Re: Dynamic constraint names in ALTER TABLE

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.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@gmail.com
>