Le portail francophone de la géomatique

DROP FUNCTION fn_assiette_sup_s();
CREATE OR REPLACE FUNCTION fn_assiette_sup_s() RETURNS integer AS $$
DECLARE
    v_count integer;
    v_table record;
BEGIN
    v_count := 0;

    FOR v_table IN
        SELECT DISTINCT suptype as tablename
        FROM ref_gpu_sup_flux."wfs_sup:assiette_sup_s", ref_bd_admin_express_3_0_2021.ade_emprise_ptdl
        WHERE ST_INTERSECTS("wfs_sup:assiette_sup_s".geom, ade_emprise_ptdl.geom)
    LOOP
        EXECUTE 'CREATE TABLE IF NOT EXISTS ref_gpu_sup_flux.' || quote_ident(v_table.tablename) || '(geom geometry(MultiPolygon,2154), gid integer, suptype character varying, partition character varying, fichier character varying, idass character varying, idgen character varying, nomass character varying, typeass character varying, modegeoass character varying, paramcalc bigint, srcgeoass character varying, datesrcass character varying, angle1 bigint,  angle2 bigint, rayon bigint, h double precision, href character varying, xdebut bigint, xfinal bigint, ydebut bigint, yfinal bigint, largeur bigint, nomsuplitt character varying)';
        v_count := v_count + 1;
    END LOOP;
    RETURN v_count;
END
$$ LANGUAGE 'plpgsql';

SELECT fn_assiette_sup_s();

CREATE OR REPLACE FUNCTION ref_gpu_sup_flux_tdlu.fn_assiette_sup_s() RETURNS integer AS $$
DECLARE
    v_count integer;
    v_table record;
BEGIN
    v_count := 0;

    FOR v_table IN
        SELECT DISTINCT wfs_sup_assiette_sup_s.suptype as tablename
        FROM ref_gpu_sup_flux.wfs_sup_assiette_sup_s, ref_bd_admin_express_3_0_2021.ade_emprise_ptdl
        WHERE ST_INTERSECTS(wfs_sup_assiette_sup_s.geom, ade_emprise_ptdl.geom)
    LOOP
        EXECUTE 'CREATE TABLE IF NOT EXISTS ref_gpu_sup_fn.sup_' || quote_ident(v_table.tablename) || '_ass_s_gpu' || '(geom geometry(MultiPolygon,2154), gid integer, suptype character varying, partition character varying, fichier character varying, idass character varying, idgen character varying, nomass character varying, typeass character varying, modegeoass character varying, paramcalc bigint, srcgeoass character varying, datesrcass character varying, angle1 bigint,  angle2 bigint, rayon bigint, h double precision, href character varying, xdebut bigint, xfinal bigint, ydebut bigint, yfinal bigint, largeur bigint, nomsuplitt character varying)';
        EXECUTE 'INSERT INTO ref_gpu_sup_fn.sup_' || quote_ident(v_table.tablename) || '_ass_s_gpu' || ' SELECT wfs_sup_assiette_sup_s.geom, wfs_sup_assiette_sup_s.gid, wfs_sup_assiette_sup_s.suptype, wfs_sup_assiette_sup_s.partition, wfs_sup_assiette_sup_s.fichier, wfs_sup_assiette_sup_s.idass, wfs_sup_assiette_sup_s.idgen, wfs_sup_assiette_sup_s.nomass, wfs_sup_assiette_sup_s.typeass, wfs_sup_assiette_sup_s.modegeoass, wfs_sup_assiette_sup_s.paramcalc, wfs_sup_assiette_sup_s.srcgeoass, wfs_sup_assiette_sup_s.datesrcass, wfs_sup_assiette_sup_s.angle1, wfs_sup_assiette_sup_s.angle2, wfs_sup_assiette_sup_s.rayon, wfs_sup_assiette_sup_s.h, wfs_sup_assiette_sup_s.href, wfs_sup_assiette_sup_s.xdebut, wfs_sup_assiette_sup_s.xfinal, wfs_sup_assiette_sup_s.ydebut, wfs_sup_assiette_sup_s.yfinal, wfs_sup_assiette_sup_s.largeur, wfs_sup_assiette_sup_s.nomsuplitt FROM ref_gpu_sup_flux.wfs_sup_assiette_sup_s, ref_bd_admin_express_3_0_2021.ade_emprise_ptdl WHERE wfs_sup_assiette_sup_s.suptype = ''' || quote_ident(v_table.tablename) || ''' AND ST_INTERSECTS(wfs_sup_assiette_sup_s.geom, ade_emprise_ptdl.geom)';
        v_count := v_count + 1; 
    END LOOP;
    RETURN v_count;
END
$$ LANGUAGE 'plpgsql';

SELECT ref_gpu_sup_flux_tdlu.fn_assiette_sup_s();