Subject: Re: trigger/for key help
From: Bret Hughes <bhughes@elevating.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
In-Reply-To: <28212.1081736841@sss.pgh.pa.us>
References: <1081662204.6609.72.camel@bretsony>
	<20040411080932.Q83278@megazone.bigpanda.com>
	<1081711155.6399.165.camel@bretsony> <17189.1081720813@sss.pgh.pa.us>
	<1081724318.2628.20.camel@bretsony>  <28212.1081736841@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 12 Apr 2004 02:18:35 -0500
Message-Id: <1081754316.3024.74.camel@bretsony>
Mime-Version: 1.0

On Sun, 2004-04-11 at 21:27, Tom Lane wrote:
> Bret Hughes <bhughes@elevating.com> writes:
> > FWIW I tried to use alter table but could never get the parser to accept
> > $1 as a constraint name.  I used single and double quotes as well as a
> > lame attempt \$1.
> 
> Hm, "$1" works for me ...
> 
> regression=# create table foo (f1 int primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> CREATE TABLE
> regression=# create table bar (f1 int references foo);
> CREATE TABLE
> regression=# \d bar
>       Table "public.bar"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f1     | integer |
> Foreign-key constraints:
>     "$1" FOREIGN KEY (f1) REFERENCES foo(f1)
>  
> regression=# alter table bar drop constraint "$1";
> ALTER TABLE
> regression=#
> 
> 			regards, tom lane

Here is a recreation of what I did:

elevating=# create table foo (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
elevating=# create table bar (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
elevating=# ALTER TABLE ONLY foo   ADD CONSTRAINT "$1" FOREIGN KEY (f1)
REFERENCES bar;
ALTER TABLE

elevating=# \d foo
         Table "foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | not null
Primary key: foo_pkey
Triggers: RI_ConstraintTrigger_2042118

elevating=# \d bar
         Table "bar"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | not null
Primary key: bar_pkey
Triggers: RI_ConstraintTrelevating=# create table foo (f1 int primary
key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
elevating=# create table bar (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
elevating=# ALTER TABLE ONLY foo   ADD CONSTRAINT "$1" FOREIGN KEY (f1)
REFERENCES bar;
ALTER TABLE
igger_2042119,
          RI_ConstraintTrigger_2042120

elevating=# 

Not much help to me.  It was at this point I started reading old emails
and trying to get rid of the foreign key that I guessed was buried in
the trigger names somewhere.

As you say, I must not have had the syntax right since:

elevating=# alter table foo drop constraint "$1";
ALTER TABLE
elevating=# drop table foo;
DROP TABLE
elevating=# drop table bar;
DROP TABLE


Well I guess I am brain dead. this is from the psql history:
alter table only diag_logs drop contraint "$2";
alter table only diag_logs drop contraint '$2';
alter table only diag_logs drop contraint ;
alter table only diag_logs drop contraint '\$2';
alter table only diag_logs drop contraint $2;
alter table only diag_logs drop contraint $2;
alter table only diag_logs drop contraint \$2;
alter table only diag_logs drop contraint "\$2";
alter table only diag_logs drop contraint '*';
alter table only diag_logs drop contraint "$2";
alter table diag_logs drop contraint "$2";


I told you I tried all combos I could think of.  All, that is except for
spelling constraint correctly :(

Anyway, I appreciate the help.


Bret