Le portail francophone de la géomatique

CREATE OR REPLACE FUNCTION plm.test_update_geom() RETURNS varchar AS $$
DECLARE
    tgeom RECORD;
BEGIN
    RAISE NOTICE 'Ajout d''une colonne geom plus OGC....';
    --- la clause where du select est utilisée pour gérer les exceptions des tables à deux colonnes geom. A vérifier si en prod il y aura ce problème
    FOR tgeom IN 
        SELECT * FROM geometry_columns inner join information_schema.tables 
        on table_schema = f_table_schema and table_name = f_table_name and table_type = 'BASE TABLE'
        where f_table_schema = 'plm' and f_table_name not in ('patrimoine_avap','test_gm_vers_qgis')  LOOP
        
        -- ici peut-être modifié le searchpath pour simplifier les choses
        EXECUTE 'SET search_path TO '||tgeom.f_table_schema||', public';
        
        RAISE NOTICE 'Mise à jour de la table % ...', quote_ident(tgeom.f_table_name);
        
        -- IF
        -- si la colonne est de type GEOMETRY
        -- si il y a plusieurs colonnes géométrie
                
        RAISE NOTICE 'suppression de la colonne the_geomtest des tables';
        EXECUTE 'SELECT DropGeometryColumn ('|| quote_literal(tgeom.f_table_schema)||','|| quote_literal(tgeom.f_table_name)||',''the_geom_test'' '||')';
        RAISE NOTICE 'colonne supprimée';
        
        RAISE NOTICE 'SELECT AddGeometryColumn (%,%,''the_geom_test'',%,%,%,true);'
        , quote_literal(tgeom.f_table_schema), quote_literal(tgeom.f_table_name), quote_literal(tgeom.srid)
        , quote_literal(tgeom.type), quote_literal(tgeom.coord_dimension);

        EXECUTE 'SELECT AddGeometryColumn ('
                || quote_literal(tgeom.f_table_schema)||','
                || quote_literal(tgeom.f_table_name)||',''the_geom_test'','
                || quote_literal(tgeom.srid)||','
                || quote_literal(tgeom.type)||','
                || quote_literal(tgeom.coord_dimension)||',true)';
        RAISE NOTICE 'colonne ajoutée';
        RAISE NOTICE 'on s''occupe de l''index spatial';
        EXECUTE 'DROP INDEX IF EXISTS sidx_'||tgeom.f_table_name||'_the_geom_test_gist';
        RAISE NOTICE 'CREATE INDEX %_the_geom_test_gist ON %.% USING gist (the_geom_test) TABLESPACE pg_default;'
        , quote_literal(tgeom.f_table_name), quote_literal(tgeom.f_table_schema), quote_literal(tgeom.f_table_name);
        
        EXECUTE 'CREATE INDEX sidx_'||tgeom.f_table_name||'_the_geom_test_gist ON '
            ||quote_ident(tgeom.f_table_schema)||'.'||quote_ident(tgeom.f_table_name)|| 
            ' USING gist (the_geom_test) TABLESPACE pg_default';
        RAISE NOTICE 'index créé';
        RAISE NOTICE 'copie des géométries dans la nouvelle colonne';
        EXECUTE 'UPDATE '
                ||quote_ident(tgeom.f_table_schema)
                ||'.'||quote_ident(tgeom.f_table_name)
                ||' SET the_geom_test = '
                ||quote_ident(tgeom.f_table_name)
                ||'.'||quote_ident(tgeom.f_geometry_column);
        RAISE NOTICE 'données copiées';    
        --END
        
    END LOOP;

    RAISE NOTICE 'Mise à jour terminée';
    RETURN 'Mise à jour terminée';
END;
$$ LANGUAGE plpgsql;

...||'.'||quote_ident(tgeom.f_geometry_column)||'::geometry';