[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
> Sent: 18 August 2004 09:33
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Mulitple Geometry Columns in One Table
> 
> 
> Mark, I've just committed a change similar to what you're 
> proposing. Constraints will be named:
> 	enforce_srid_<fieldname>
> 	enforce_geomtype_<fieldname>
> 
> If you want to help you might try modifying 
> rename_geometry_table_constraints().
> I've spent some time on it and finally I've decided to obsolete it.
> 
> No functions currently rely on constraints name so having 
> constraints with unconsistent names should not be a problem.
> 
> The problem in finding out column name from
> rename_geometry_table_constraints() is that consrc might be 
> in the following forms:
> 	((geometrytype(the_geom) == ....
> 	((geometrytype("MyGeom") == ....
> 	((geometrytype(public."MyGeom") == ....
> 	((geometrytype("MySchema"."MyGeom") == ....
> 	((geometrytype("MySchema".the_geom) == ....
> 
> AFAICS there's no reference to the field being checked in 
> pg_constraint so I tried using substr (7.1 compatible). The 
> only viable way I see is using CASE statements, but I'm not 
> sure we can rely on future values in connsrc for this to work.
> 
> Sorry for the inconveniences.
> 
> --strk;


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.


Kind regards,

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.