Обсуждение: Strange behavior of plpgsql.

Hello list

I just noticed a strange quirk of plpgsql.  It gave me an error when trying to access a member of a complex field in NEW.  Error reported was: "ERROR:  NEW used in query that is not in a rule".

However, it seems that plsql rather has a problem with the double dot i.e. NEW.amount.direction and could not parse that (or did not have any knowledge of the complex type). 

I found a workaround by first declaring a local variable to the complex type DECLARE amnt tlx_actamount;
and then assigning the complex field to this variable.  It seems plpgsql is quite happy to accept such a consstruct.

As an example :

plpgsql will not accept
NEW.amount.direction ..and give the error listed above

It will however be happy with:

DECLARE amnt tlx_amount;

...
amnt:=NEW.amount;

Hope this sheds some light for a guru

Regards

--
Alex du Plessis

Alex du Plessis <alexdup.main@gmail.com> writes:
> However, it seems that plsql rather has a problem with the double dot i.e.
> NEW.amount.direction and could not parse that (or did not have any knowledge
> of the complex type).

You would have a problem with that with an ordinary table reference,
too, though plpgsql is probably a bit worse about providing a useful
error message :-(.  The trick is that you need to parenthesize:
    (NEW.amount).direction
With three components in the name, the parser is thinking that it
must be schema.table.column, and NEW isn't a schema name.  With
the parentheses in place, the name is correctly resolved as a column
of NEW, and then since it's a composite you can extract a field.

            regards, tom lane