Le portail francophone de la géomatique

CREATE OR REPLACE FUNCTION bc_per_building ( ) RETURNS VOID AS 
$$

DECLARE 
    current_zone_bc record; 
    i integer ; -- nombre de d'itération
BEGIN
     
    i := 1 ; 
    
    FOR current_zone in 
        SELECT 
            z.id_zone, 
            z.id_usage, 
            z.id_categorie, 
            z.repart_categorie,
            count(b.gid) as nbbati_zone,
            count(b.gid) * z.repart_bc/100 AS nbbati_repart,
            z.geom 
        FROM monschema.table_zone AS z
        INNER JOIN monschema.table_bati as b 
        ON st_contains (z.geom, st_centroid(b.geom))
        WHERE zgeom.code = b.id_zone AND z.id_usage = b.code_usage
        GROUP BY z.id_zone, z.id_usage, z.id_categorie, z.repart_categorie, z.geom 
    
    LOOP 
        WHILE i <= current_zone_bc.nbbati_repart LOOP
            UPDATE monschema.table_bati bg SET id_categorie = current_zone_bc.id_categorie
            WHERE bg.id_bc IS NULL 
            AND bg.id_usage = current_zone_bc.id_usage 
            AND bg.id_zone = current_zone_bc.id_zone
            AND bg.random_num <= current_zone_bc.nbbati_repart; 
            i := i + 1 ; 
        END LOOP ;     
    END LOOP ;     
END    ;
$$

language plpgsql volatile;

SELECT bc_per_building ( ) ;