[postgis-users] Mulitple Geometry Columns in One Table

strk at refractions.net strk at refractions.net
Thu Aug 19 02:28:16 PDT 2004


On Thu, Aug 19, 2004 at 10:20:41AM +0100, Mark Cave-Ayland wrote:
> 
> > -----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.

Very good, I found those typos too.
I've committed the change.
Thank you.

--strk;



More information about the postgis-users mailing list