Dynamic query in trigger function in PostgreSQL

Question

I am trying to execute a dynamic query in a trigger function but I keep getting the Missing FROM-clause entry for table "new"

Why does the error occur and how can I fix it?

CREATE OR REPLACE FUNCTION "Site"."UpdateAncestorModified"()
  RETURNS trigger AS
$BODY$BEGIN
    EXECUTE
        format
        ('
            UPDATE 
                "' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '" 
            SET
                modified = NEW.modified
            WHERE
                id = NEW."ancestorId"
            AND
                modified <> NEW.modified
        ')
    USING
        NEW;
    RETURN NEW;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Kolyunya · Accepted Answer · 2014-04-16 17:29:48Z

4

I had to change the NEW to the ($1) since it is the reference to the first bound parameter.

The correct query is:

CREATE OR REPLACE FUNCTION "Site"."UpdateAncestorModified"()
  RETURNS trigger AS
$BODY$BEGIN
    EXECUTE
        format
        ('
            UPDATE 
                "' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '" 
            SET
                modified = ($1).modified
            WHERE
                id = ($1)."ancestorId"
            AND
                modified <> ($1).modified
        ')
    USING
        NEW;
    RETURN NEW;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

answered Apr 16, 2014 at 17:29

Kolyunya

6,2508 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic query in trigger function in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related