Dynamic constraint names in ALTER TABLE

Hi,

Where I work, we have a large deployment of software using PostgreSQL
database. We have been stuck on version 7.4.16 for a while now. I am
about to switch us to a 9.0.x.

One problem I'm running into, and I am hoping you can help me with,
given the constraints I have to work with, is our conversion
"scripts".

As our DB schema changes, we have a series of convert SQL scripts that
are executed through psql to alter tables, constraints, etc. This is
done at upgrade time. This has worked pretty well over the years for
us. However, what I notice is that, while in PG 7.4.x the constraints
(foreign keys) seem to take of the form "$1", "$2", etc., PG 9.x seems
to give them a more descriptive names. This behavior change breaks our
SQL convert scripts.

Is there anyway I can change our .sql files to make the the ALTER
TABLE <ADD|DROP> CONSTRAINT statements determine the constraint name
based on system catalog?

Here a simple example demonstrating my issue:

foo=# CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"employee_pkey" for table "employee"
CREATE TABLE

foo=# CREATE TABLE sales (seller INTEGER PRIMARY KEY, amount INTEGER,
FOREIGN KEY (seller) REFERENCES employee (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"sales_pkey" for table "sales"
CREATE TABLE

In PostgreSQL 7.4.17:
foo=# \d sales
     Table "public.sales"
 Column |  Type   | Modifiers
--------+---------+-----------
 seller | integer | not null
 amount | integer |
Indexes:
    "sales_pkey" PRIMARY KEY, btree (seller)
Foreign-key constraints:
    "$1" FOREIGN KEY (seller) REFERENCES employee(id)


In PostgreSQL 9.0.3:
foo=# \d sales
     Table "public.sales"
 Column |  Type   | Modifiers
--------+---------+-----------
 seller | integer | not null
 amount | integer |
Indexes:
    "sales_pkey" PRIMARY KEY, btree (seller)
Foreign-key constraints:
    "sales_seller_fkey" FOREIGN KEY (seller) REFERENCES employee(id)


# If the conversion wanted to change the constraint to
# add ON DELETE CASCADE (simple example).

In PostgreSQL 7.4.17 our .sql convert script would say:
foo=# ALTER TABLE sales DROP CONSTRAINT "$1" ;
ALTER TABLE
foo=# ALTER TABLE sales ADD CONSTRAINT "$1" FOREIGN KEY (seller)
REFERENCES employee (id) ON DELETE CASCADE ;
ALTER TABLE


In PostgreSQL 9.0.3 our .sql convert script would need to say:
foo=# ALTER TABLE sales DROP CONSTRAINT "sales_seller_fkey" ;
ALTER TABLE
foo=# ALTER TABLE sales ADD CONSTRAINT "sales_seller_fkey" FOREIGN KEY
(seller) REFERENCES employee (id) ON DELETE CASCADE ;
ALTER TABLE


Is there any way the .sql scripts could make use of this query to get
the foreign key name from pg_constraint table, regardless of PG
version (7.4.x or 9.x)?

foo=# SELECT conname FROM pg_constraint JOIN pg_class ON
(conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] =
1 AND contype='f';

In PostgreSQL 7.4.17:
 conname
---------
 $1
(1 row)


In PostgreSQL 9.0.3:
      conname
-------------------
 sales_seller_fkey
(1 row)


Thanks for reading,
--patrick