I updated the function probe_geometry_columns().
Hope that help.
Fred
--------------------------------------------
-- Function: probe_geometry_columns()
-- DROP FUNCTION probe_geometry_columns();
CREATE OR REPLACE FUNCTION probe_geometry_columns()
RETURNS text AS
$BODY$
DECLARE
inserted integer;
oldcount integer;
probed integer;
stale integer;
BEGIN
SELECT count(*) INTO oldcount FROM geometry_columns;
SELECT count(*) INTO probed
FROM pg_class c, pg_attribute a, pg_type t,
pg_namespace n,
pg_constraint sridcheck, pg_constraint typecheck
WHERE t.typname = 'geometry'
AND a.atttypid = t.oid
AND a.attrelid = c.oid
AND c.relnamespace = n.oid
AND sridcheck.connamespace = n.oid
AND typecheck.connamespace = n.oid
AND sridcheck.conrelid = c.oid
AND sridcheck.consrc LIKE '(%.srid('||a.attname||') = %)'
AND typecheck.conrelid = c.oid
AND typecheck.consrc LIKE
'((%.geometrytype('||a.attname||') = ''%''::text) OR (% IS NULL))'
;
INSERT INTO geometry_columns SELECT
''::varchar as f_table_catalogue,
n.nspname::varchar as f_table_schema,
c.relname::varchar as f_table_name,
a.attname::varchar as f_geometry_column,
2 as coord_dimension,
trim(both ' =)' from
replace(replace(split_part(
sridcheck.consrc, ' = ', 2), ')', ''), '(', ''))::integer AS
srid,
trim(both ' =)''' from substr(typecheck.consrc,
strpos(typecheck.consrc, '='),
strpos(typecheck.consrc, '::')-
strpos(typecheck.consrc, '=')
))::varchar as type
FROM pg_class c, pg_attribute a, pg_type t,
pg_namespace n,
pg_constraint sridcheck, pg_constraint typecheck
WHERE t.typname = 'geometry'
AND a.atttypid = t.oid
AND a.attrelid = c.oid
AND c.relnamespace = n.oid
AND sridcheck.connamespace = n.oid
AND typecheck.connamespace = n.oid
AND sridcheck.conrelid = c.oid
AND sridcheck.consrc LIKE '(%.srid('||a.attname||') = %)'
AND typecheck.conrelid = c.oid
AND typecheck.consrc LIKE
'((%.geometrytype('||a.attname||') = ''%''::text) OR (% IS NULL))'
AND NOT EXISTS (
SELECT oid FROM geometry_columns gc
WHERE c.relname::varchar = gc.f_table_name
AND n.nspname::varchar = gc.f_table_schema
AND a.attname::varchar = gc.f_geometry_column
);
GET DIAGNOSTICS inserted = ROW_COUNT;
IF oldcount > probed THEN
stale = oldcount-probed;
ELSE
stale = 0;
END IF;
RETURN 'probed:'||probed::text||
' inserted:'||inserted::text||
' conflicts:'||(probed-inserted)::text||
' stale:'||stale::text;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION probe_geometry_columns() OWNER TO postgres;
COMMENT ON FUNCTION probe_geometry_columns() IS 'Scans all tables with
PostGIS geometry constraints and adds them to the geometry_columns table if
they are not there.';
On Tue, May 18, 2010 at 3:38 PM, Fred Lehodey <leho...@gmail.com> wrote:
> Hi,
> I have no success trying the function probe_geometry_columns() with Postgis
> 1.5.0
>
> 1) Not sure but comparing the SQL with Postgis 1.3.3:
> the clause (in the INSERT step and not the count of probed)
> "sridcheck.consrc LIKE '(*srid*('||a.attname||') = %)' in postgis 1.3.3
> is now:
> "sridcheck.consrc LIKE '(*st_srid*('||a.attname||') = %)' in postgis
> 1.5.0
> This looks like a tipo error. (this is not the function here but the
> constraint text in pg_constraint)
>
>
> 2) I have a second problem with pg_constraint table and the "consrc" field.
> Most of time I have something like :
> "(public.srid(the_geom) = 27492)"
> and not (as expected by the function probe_geometry_columns()) :
> "(srid(the_geom) = 27492)"
>
> Thanks for any feed-back.
>
> Fred.
>
>
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
