Обсуждение: 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
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