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

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(column name) with a 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.idmeans:
" new.idequal to the value of the column whose name is equal to the name of the parent table."

Answer 1

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

1. CASEexpression

For several well-known alternatives (and 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;

ELSE , NULL, . TRUE WHERE.

2. SQL

, .

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

• , . .

• PL/pgSQL . 1 , Postgres , , . 2 . // , ...

  • sql plpgsql PostgreSQL

• , SQL SQL- ( ). format() %I.

:

• PostgreSQL
• INSERT
• plpgsql
• sql plpgsql PostgreSQL