Re: 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';