Thread: Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS?

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.

"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