On Tue, Sep 20, 2011 at 7:36 AM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > On Monday, September 19, 2011 8:09:04 pm patrick keshishian wrote: >> 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.ht >> > ml >> > http://www.postgresql.org/docs/9.0/interactive/infoschema-table-constrai >> > nts.html >> >> I think you you missed the intent of my question; unless I've missed >> depth of your answer. > > My mistake. I misread the question and I thought you where looking for a way to > get the information without using the system catalogs. > >> >> 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. >> > > > Just out of curiosity, what do you do if there is more than one constraint on a > table and you want to apply different changes? You mean in a situation like this: foo=# CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT); foo=# CREATE TABLE product (id INTEGER PRIMARY KEY, description TEXT) ; foo=# CREATE TABLE sales (seller INTEGER, amount INTEGER, item INTEGER, FOREIGN KEY (seller) REFERENCES employee (id), FOREIGN KEY (item) REFERENCES product (id)); foo=# \d sales Table "public.sales" Column | Type | Modifiers --------+---------+----------- seller | integer | amount | integer | item | integer | Foreign-key constraints: "sales_item_fkey" FOREIGN KEY (item) REFERENCES product(id) "sales_seller_fkey" FOREIGN KEY (seller) REFERENCES employee(id) The conkey field in the pg_constraint table helps you choose the specific constraint you are referring to. i.e., when I am looking for the FOREIGN KEY referenced by seller (column 1) conkey[1] = 1: 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'; conname ------------------- sales_seller_fkey (1 row) FOREIGN KEY referenced by item (column 3) conkey[1] = 3: foo=# SELECT conname FROM pg_constraint JOIN pg_class ON (conrelid=pg_class.oid) WHERE pg_class.relname='sales' AND conkey[1] = 3 AND contype='f'; conname ----------------- sales_item_fkey (1 row) Best, --patrick -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
