Trigger to update old records in the same table using PL/pgsql

Question

I'm trying to use a trigger function to update old records in the same table and mark them as redundant if a new record supersedes them.

I'm trying to use TG_TABLE_NAME as a generic way to update whichever table caused this trigger to fire. My code looks like this:

BEGIN
  UPDATE TG_TABLE_NAME 
  SET "Redundant"=true
  WHERE "DocumentID"=NEW."DocumentID"
  AND "RecordID" = NEW."RecordID"
  AND "TransactionID" < NEW."TransactionID"
  AND "Redundant" = false ;
  RETURN NEW;
END

But when the trigger fires, postgres complains that it can't find a table called "tg_table_name"

I'm guessing I'm doing something obviously wrong, but I'm new to pl/PGSQL. Does anyone have any advice for how to update old records (with matching RecordID and smaller TransactionID) ?

score 2 · Accepted Answer · edited May 23 '17 at 12:19

2

You cannot use variables for identifiers in plain SQL. You need to build SQL statements and use EXECUTE. Dynamic SQL. Could look something like this:

CREATE FUNCTION foo() RETURNS trigger AS
$BODY$
BEGIN
EXECUTE '
  UPDATE ' || quote_ident(TG_RELNAME) || '
  SET    "Redundant" = true
  WHERE  "DocumentID" = $1
  AND    "RecordID" = $2
  AND    "TransactionID" < $3
  AND    "Redundant" = FALSE'
USING
   NEW."DocumentID"
  ,NEW."RecordID"
  ,NEW."TransactionID";

  RETURN NEW;
END;
$BODY$ language plpgsql;

Note how I pass in variables with the USING clause. Simplifies the syntax.
You can find more information and links to the manual in this related answer.

edited May 23 '17 at 12:19

Community

1
1

answered Feb 14 '12 at 21:03

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks, this was definitely the right way to go. I had to do a couple of tweaks to get it to work though. My final code was: `BEGIN EXECUTE ' UPDATE "' || TG_RELNAME || '" SET "Redundant"=true WHERE "DocumentID"=$1 AND "RecordID" = $2 AND "TransactionID" < $3 AND "Redundant" = false' USING NEW."DocumentID", NEW."RecordID", NEW."TransactionID"; RETURN NEW; END` I had to add double quotes to preserve the case of my table name (probably specific to my case) and took off the first semicolon before the USING statement. Thanks much for your help! – Drewmate Feb 14 '12 at 21:19
@Drewmate: use [`quote_ident(TG_RELNAME)`](http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER) instead. I did not think of mixed case identifiers, because I **never** use them. Lower case identifiers make your life easier in PostgreSQL. You must have missed my update where I fixed the semicolon. – Erwin Brandstetter Feb 14 '12 at 21:35
Good advice on the identifiers. I think I'll switch everything to lower case. – Drewmate Feb 14 '12 at 21:43

Trigger to update old records in the same table using PL/pgsql

1 Answers1