How to derive column name in UPDATE from variable in a PL/pgSQL trigger function?

Question

I have a simple trigger function in PostgreSQL 9.4:

 BEGIN 
 IF (TG_OP = 'UPDATE') THEN 
 UPDATE relation 
 SET child_name = new.name 
 WHERE table_reference_1 = new.id; 
 END IF; 
 RETURN NULL; 
 END;

Is it possible to replace table_reference_1 (which is column name) with variable? I want to do something like:

 BEGIN 
 IF (TG_OP = 'UPDATE') THEN 
 UPDATE relation 
 SET child_name = new.name 
 WHERE TG_TABLE_NAME = new.id; 
 END IF; 
 RETURN NULL; 
 END;

WHERE TG_TABLE_NAME = new.id is supposed to mean:
"new.id is equal to the value of the column, whose name is equal to the parent table's name".

How can a table name equal an id? But in general you need to use dynamic SQL if you want to use identifiers that are stored in a variable: stackoverflow.com/search?q=[postgresql]+dynamic+sql — user330315
– user330315, Commented Dec 24, 2015 at 10:26
> How can a table name equal an id?< It's not what i'm trying to do. Check the description - i've added a bit more explanation — stkvtflw
– stkvtflw, Commented Dec 24, 2015 at 11:15

Community · Accepted Answer · 2017-05-23 11:59:05Z

Plain SQL does not accept variables for identifiers. I see two options for your trigger function:

1. `CASE` expression

For a couple of known alternatives (and an optional catch-all).

UPDATE relation r
SET    child_name = NEW.name 
WHERE  CASE TG_TABLE_NAME  -- "switched case"
        WHEN    'possible_column1'          -- value!
          THEN r.possible_column1 = NEW.id  -- identifier!
        WHEN    'possible_column2'
          THEN r.possible_column2 = NEW.id
        --  etc.
        -- ELSE r.default_column = NEW.id
        -- or no ELSE ...
       END;

No ELSE means the expression evaluates to NULL if no option matches. And only TRUE qualifies in a WHERE clause.

2. Dynamic SQL

For any number of alternatives or for alternatives unknown at the time of coding.

EXECUTE format('
   UPDATE relation
   SET    child_name = $1
   WHERE  %I = $2'
 , TG_TABLE_NAME  -- being used as column name
USING NEW.name, NEW.id;

Notes

If the column name doesn't actually exist, this raises an exception. Your transaction is rolled back unless you trap it.
PL/pgSQL operates with prepared statements. The query plan for option 1 can be reused within the same session if Postgres finds that re-planning does not generate better plans than a generic plan. Option 2 is planned every time. This may be irrelevant / a disadvantage / an actual advantage, depending on your use case ...
- Difference between language sql and language plpgsql in PostgreSQL functions
Always make sure that dynamic SQL is safe against SQL injection (by maliciously crafted table names in this case). I defend against it with format() using %I.

Collectives™ on Stack Overflow

How to derive column name in UPDATE from variable in a PL/pgSQL trigger function?

1 Answer 1

1. `CASE` expression

2. Dynamic SQL

Notes

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1. CASE expression

2. Dynamic SQL

Notes

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

1. `CASE` expression