CODEWITHSUNDEEP
postgresqlplpgsql
Drewmate
Drewmate

Reputation: 2159

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

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) ?

Upvotes: 2

Views: 2237

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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.

Upvotes: 2

Related Questions