Re: Dynamic constraint names in ALTER TABLE

On Monday, September 19, 2011 8:09:04 pm patrick keshishian wrote:
> On Mon, Sep 19, 2011 at 6:08 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com