Re: a trigger question - Mailing list pgsql-general

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@Yahoo.com #