Hmmm...
The following does work actually:
insert into second_tbl(v) values(new.v);
The trick is as you pointed, new.v does have single quotes associated with
already. I tested many combinations -- with and without DEFERRABLE and with
BEFORE INSERT and AFTER INSERT. I probably ran the above statement with
BEFORE INSERT in the trigger so I wrongly stated the above did not work.
Never quote_literal any new.varchar!
Problem solved! Thank you all for the help! I really appreciate it.
Lixin Zhou
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Wednesday, June 05, 2002 3:23 PM
To: Zhou, Lixin
Cc: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] a trigger question
On Wed, 5 Jun 2002, Zhou, Lixin wrote:
> Thank all who answered and helped!
>
> Here is what I learnt so far:
>
> 1) Fire the trigger AFTER INSERT. BEFORE INSERT won't work.
> 2) Deferrable does not seem to be important.
>
> I've tested above, it does work as expected for PK/FKs that are integers.
>
> (As Stephan pointed out, it should work. And he's right, it works.)
>
> However, my specific problem is still not solved. My original SQL
> actually has varchar(25) as PK/FK (in the original post, I used int as
> example because I did not realize that would make a difference). I made
> a simple test and it does exactly reproduce my problem. So, I post it
> below.
>
> The problem is actually, I guess, the SQL statement somehow does not
> insert the correct value to the second table. For example, it may not
> quote the varchar string correctly.
In the below, you seem to be getting an extra set of quote marks (what
it's inserting is the string 'v' (where the single quotes are in the
string). I removed the foreign key constraint to see what was being
inserted.
It worked for me on 7.2 when I just did the (new.v) insert with
the constraint. You might want to turn on query logging and such to
see what's going on.
> create function init_second_tbl() returns opaque as '
> declare
> begin
> insert into second_tbl(v) values(quote_literal(new.v));
> -- 1. this does not work
> -- insert into second_tbl(v) values(new.v);
> -- 2. this does not work
> -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v)
||
> '')'';
> -- execute s;
> -- 3. this does not work
> -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v)
||
> '')'';
> -- perform s;
> return null;
> end;
> ' language 'plpgsql';
