On Mon, Sep 19, 2011 at 6:08 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > On Monday, September 19, 2011 5:10:45 pm patrick keshishian wrote: >> Hi, >> >> 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)? > > Use the information schema? As example: > http://www.postgresql.org/docs/7.4/static/infoschema-table-constraints.html > http://www.postgresql.org/docs/9.0/interactive/infoschema-table-constraints.html I think you you missed the intent of my question; unless I've missed depth of your answer. The question wasn't where does one find the name of the constraint. My example demonstrated that I knew how to get that value. The question, however, is how do you get that in an ALTER TABLE statement? A sub-select doesn't seem to work. e.g., ALTER TABLE sales DROP CONSTRAINT (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') ; That does not work. I can generate the SQL statements using SELECTs, output (\o) them to a /tmp/really-hacky-way-to-do-this.sql files, then read (\i) them into psql, but as the file name says, this is getting perverse. --patrick >> 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 > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxx > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
