Re: Dynamic constraint names in ALTER TABLE

On Tue, Sep 20, 2011 at 7:36 AM, Adrian Klaver <adrian.klaver@gmail.com> 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@gmail.com>
> 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