Bug in plpgsql, when using NEW with composite field value.
SQL:
CREATE OR REPLACE FUNCTION "bug_with_triggers" () RETURNS trigger AS
$body$
BEGIN
PERFORM COALESCE(NEW.some_composite_field.field, TRUE);
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Error:
ERROR: NEW used in query that is not in a rule
QUERY: SELECT COALESCE(NEW.somecompositefield.field, TRUE)
CONTEXT: SQL statement in PL/PgSQL function "bug_with_triggers" near line 2
********** ERROR **********
ERROR: NEW used in query that is not in a rule
SQL state: 42601
Контекст:SQL statement in PL/PgSQL function "bug_with_triggers" near line 2
Hello
you have to use parenthesis, because parser don't distinguish between
variants schema.table.column and variable.field.attrib
example:
postgres=# create or replace function trgbody() returns trigger as $$
begin
raise notice '%', (new.a).a;
return new;
end $$ language plpgsql;
CREATE FUNCTION
postgres=# create type xx as (a integer, b integer);
CREATE TYPE
postgres=# create table g(a xx);
CREATE TABLE
postgres=# create trigger ggg before insert on g for each row execute
procedure trgbody();
CREATE TRIGGER
postgres=# insert into g values('(10,20)');
NOTICE: 10
INSERT 0 1
regards
Pavel Stehule
2008/12/10 Oleg Serov <serovov@gmail.com>:
Show quoted text
SQL:
CREATE OR REPLACE FUNCTION "bug_with_triggers" () RETURNS trigger AS
$body$
BEGIN
PERFORM COALESCE(NEW.some_composite_field.field, TRUE);
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;Error:
ERROR: NEW used in query that is not in a rule
QUERY: SELECT COALESCE(NEW.somecompositefield.field, TRUE)
CONTEXT: SQL statement in PL/PgSQL function "bug_with_triggers" near line 2********** ERROR **********
ERROR: NEW used in query that is not in a rule
SQL state: 42601
Контекст:SQL statement in PL/PgSQL function "bug_with_triggers" near line 2--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
"Oleg Serov" <serovov@gmail.com> writes:
CREATE OR REPLACE FUNCTION "bug_with_triggers" () RETURNS trigger AS
$body$
BEGIN
PERFORM COALESCE(NEW.some_composite_field.field, TRUE);
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Error:
ERROR: NEW used in query that is not in a rule
I agree that this error message is pretty unhelpful, but the code is
wrong anyway. The correct way to do this is
PERFORM COALESCE((NEW.some_composite_field).field, TRUE);
regards, tom lane