Le portail francophone de la géomatique

drop view descrip_dbcol;
create view descrip_dbcol as 
select  i.table_schema, i.table_name, i.column_name, i.data_type  
from information_schema.columns as i
where  i.table_schema in  (select distinct table_schema from descrip_db )
and i.table_name != 'geometry_columns' and  i.table_name != 'spatial_ref_sys';

drop table table_hors_geometrycolumns;
create table table_hors_geometrycolumns as 
select table_schema,table_name from descrip_db
where table_schema||table_name not in (select f_table_schema||f_table_name from geometry_columns)
Except 
select table_schema,table_name from descrip_db 
where table_schema||table_name 
not in (select table_schema||table_name from descrip_dbcol where column_name = 'the_geom');
alter table table_hors_geometrycolumns add column id serial;

CREATE OR REPLACE FUNCTION "public"."mafonction" () RETURNS boolean AS
$body$
DECLARE 
ma_table character varying;
i=1
nb_table_a_modif = select count * from table_hors_geometrycolumns
while i < nb_table_a_modif
ma_table = 'select table_name from table_hors_geometrycolumns where id=i';


execute 'select  distinct '''' as f_table_catalog, t.table_schema,t.table_name,''the_geom'' as f_geometry_column,
2 as coord_dimension,srid(a.the_geom), geometrytype(a.the_geom)
from table_hors_geometrycolumns as t , 'ma_table' as a ' into geometry_columns;

set i= i+1

return true;

END;
$body$
LANGUAGE 'plpgsql';