Re: Dynamic constraint names in ALTER TABLE

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: patrick keshishian <pkeshish(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Dynamic constraint names in ALTER TABLE
Date: 2011-09-20 14:36:21
Message-ID: 201109200736.22166.adrian.klaver@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message MirrorX 2011-09-20 14:49:45 Re: upgrade postgres to 8.4.8, centos 5.3
Previous Message Scott Marlowe 2011-09-20 14:32:58 Re: upgrade postgres to 8.4.8, centos 5.3