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

Search Discussions

  • Pavel Stehule at Dec 10, 2008 at 10:47 am
    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>:
    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
  • Tom Lane at Dec 10, 2008 at 4:44 pm

    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedDec 10, '08 at 9:54a
activeDec 10, '08 at 4:44p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase