pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? 3+ messages / 2 participants [nested] [flat]
* Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? @ 2007-01-21 23:52 Simon Kinsella <simon@bluefiresystems.co.uk> 0 siblings, 1 reply; 3+ messages in thread From: Simon Kinsella @ 2007-01-21 23:52 UTC (permalink / raw) To: pgsql-sql Hi My system currently runs on PostgreSQL 8.1 and makes use of the old behaviour of SET CONSTRAINTS, namely that the command is applied to all constraints that match the specified name. This makes it very easy to write a general-case function that can change the DEFERRED mode on a given constraint that is present in several similar schemas (sounds odd maybe but it works very well in my case!). I understand that SET CONSTRAINTS in 8.2 no longer behaves like this. It's looking like my general function will need to do something like: SET CONSTRAINTS schema1.foo IMMEDIATE; SET CONSTRAINTS schema2.foo IMMEDIATE; SET CONSTRAINTS schema3.foo IMMEDIATE; ... SET CONSTRAINTS schemaX.foo IMMEDIATE; instead of SET CONSTRAINTS foo IMMEDIATE; and hope that I remember to add new clauses to this function when new schemas are added in the future. Or can I do something with pg_contraints, or some other technique? I'm relunctant to use SET CONSTRAINTS' 'ALL' clause in case it messes with other constraints in the system. Hope this makes sense, Simon --- Simon Kinsella Technical Director - Bluefire Systems Ltd This message has been scanned for viruses. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? @ 2007-01-22 03:36 Tom Lane <tgl@sss.pgh.pa.us> parent: Simon Kinsella <simon@bluefiresystems.co.uk> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2007-01-22 03:36 UTC (permalink / raw) To: Simon Kinsella <simon@bluefiresystems.co.uk>; +Cc: pgsql-sql "Simon Kinsella" <simon@bluefiresystems.co.uk> writes: > My system currently runs on PostgreSQL 8.1 and makes use of the old > behaviour of SET CONSTRAINTS, namely that the command is applied to all > constraints that match the specified name. Unfortunately that was pretty far away from what the SQL spec says :-( > This makes it very easy to write > a general-case function that can change the DEFERRED mode on a given > constraint that is present in several similar schemas (sounds odd maybe but > it works very well in my case!). I think you could do it fairly easily still, eg for rec in select nspname from pg_namespace n join pg_constraint c on n.oid = c.connamespace where conname = $1 loop execute 'set constraints ' || quote_ident(rec.nspname) || '.' || quote_ident($1) || ' immediate'; end loop; Exceedingly untested, but something close to this seems like it'd solve your problem. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* FW: Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? @ 2007-01-22 10:50 Simon Kinsella <simon@bluefiresystems.co.uk> 0 siblings, 0 replies; 3+ messages in thread From: Simon Kinsella @ 2007-01-22 10:50 UTC (permalink / raw) To: pgsql-sql That sounds like a plan - will give it a go. Thanks! simon -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, January 22, 2007 3:37 AM To: Simon Kinsella Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? "Simon Kinsella" <simon@bluefiresystems.co.uk> writes: > My system currently runs on PostgreSQL 8.1 and makes use of the old > behaviour of SET CONSTRAINTS, namely that the command is applied to > all constraints that match the specified name. Unfortunately that was pretty far away from what the SQL spec says :-( > This makes it very easy to write > a general-case function that can change the DEFERRED mode on a given > constraint that is present in several similar schemas (sounds odd > maybe but it works very well in my case!). I think you could do it fairly easily still, eg for rec in select nspname from pg_namespace n join pg_constraint c on n.oid = c.connamespace where conname = $1 loop execute 'set constraints ' || quote_ident(rec.nspname) || '.' || quote_ident($1) || ' immediate'; end loop; Exceedingly untested, but something close to this seems like it'd solve your problem. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-01-22 10:50 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-01-21 23:52 Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? Simon Kinsella <simon@bluefiresystems.co.uk> 2007-01-22 03:36 ` Tom Lane <tgl@sss.pgh.pa.us> 2007-01-22 10:50 FW: Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? Simon Kinsella <simon@bluefiresystems.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox