Re: a trigger question

Zhou, Lixin wrote:
> Thanks Jan!
>
> >> 1) Fire the trigger AFTER INSERT. BEFORE INSERT won't work.
> >> 2) Deferrable does not seem to be important.
>
> >Jan 1) Fire the trigger BEFORE INSERT does work
> >Jan 2) If you make the constraint deferred
> >Jan 3) And let the trigger return NEW instead of NULL;
>
> This is a really interesting trick!

To return NEW? The documented behaviour of a trigger fired
BEFORE INSERT and returning NULL is, that the INSERT doesn't
take place. That pretty much guarantees that the key will not
be there, wouldn't it?

>
> >> I've tested above, it does work as expected for PK/FKs that are integers.
>
> >Jan It also works for all other types of keys.
>
> Yes, it should as I pointed out in another email.
>
> I had troubles because I used quot_literal on a new.varchar value within the
> PLPGSQL function. This should cause SQL syntax error (eg: insert into
> tbl(a_string) values (''this is a string to be inserted but it will fail to
> insert.'') but I got the error message as "referential integrity error"
> instead of SQL syntax error.

It should not, because those statements don't get parsed that
way. PL/pgSQL is not a string substitution/reevaluation
system like some other scripting languages. Strings in
variables are handled as datum, no matter if there are
special characters in them or not.

The statement

INSERT INTO second_tbl (v) VALUES (new.v);

Get's internally modified by the PL/pgSQL parser into

INSERT INTO second_tbl (v) VALUES ( $1 );

This $1 notation is only available via the internal server
programming interface (SPI) and PL/pgSQL specifies the
datatype of that "parameter" explicitly in an array that has
to be passed to SPI_prepare().

The datatype (varchar(20) in our case) is known, because all
this happens on the first trigger invocation and the trigger
system passes not only the NEW row for first_tbl in, but a
row descriptor as well.

Surrounding new.v now with the function call quote_literal()
just add's the quoting to the string and inserts that result.
Which is of course different from the key originally
inserted, and if you started off with empty tables it's
nearly impossible that this quoted string exists as a key
(it's insertion should've failed because of the double qouted
key missing ... and so forth ... maybe we could start off
with a key consisting of single quotes only and work down
from there, but that's academic and not exactly what your
business modell requires).

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #