[postgis-users] Mulitple Geometry Columns in One Table

[strk at refractions.net]

On Wed, Aug 18, 2004 at 11:36:37AM +0100, Mark Cave-Ayland wrote:
[...]
> Hi strk,
> 
> Could you not do something like the following:
> 
> test=# select t2.oid from geometry_columns t1, pg_class t2 where
> t1.f_table_name = t2.relname;
>   oid
> -------
>  19297
>  19306
> (2 rows)
> 
> Then for each of theses oids (these are the oids for each table that
> appears in geometry_columns):
> 
> test=# select t1.conname, t2.attname from pg_constraint t1, pg_attribute
> t2 wher
> e t1.conrelid = 19297 AND t1.conkey[1] = t2.attnum AND t2.attrelid =
> 19297;
>      conname     | attname
> -----------------+---------
>  enforce_srid    | geom
>  enforce_geotype | geom
> (2 rows)
> 
> ....where 'geom' is the name of the column the constraint is applied to,
> and conname is the name of the constraint.

This is derived from your suggestion, but uses SQL only and its
2 queries (one for constraint type).
It avoids querying geometry_column so to avoid messing with schema,
pgsql switches and the like. 

What do you think ?

UPDATE pg_constraint
        SET conname = textcat(''enforce_geotype_'', a.attname)
        FROM pg_attribute a
        WHERE
                a.attrelid = conrelid
                AND a.attnum = conkey[1]
                AND c.consrc LIKE ''((geometrytype(%) = %'';

UPDATE pg_conname
        SET conrelid = textcat(''enforce_srid_'', a.attname)
        FROM pg_attribute a
        WHERE
                a.attrelid = conrelid
                AND a.attnum = conkey[1]
                AND c.consrc LIKE ''(srid(% = %)'';

--strk;