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? -- 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
