[postgis-users] Mulitple Geometry Columns in One Table

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of strk at refractions.net
> Sent: 19 August 2004 09:24
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Mulitple Geometry Columns in One Table
> 

(lots cut)

> 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(% = %)'';
> 

Hi strk,

Nice one! I had to tweak these a little: I had to remove the 'c' alias
(as otherwise it was matching everything in pg_constraint if I added
it?) but the following queries worked for me in psql (tested on
PG8.0b1):


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

test=# update pg_constraint set conname = textcat('enforce_srid_',
a.attname) FROM pg_attribute a WHERE a.attrelid = conrelid AND a.attnum
= conkey[1] AND consrc LIKE '(srid(% = %';


How does that work for you?

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.