is it possible tu use variable field name for read new record in a trigger function?-CodePudding

in a trigger function i want to use a variable (fieldName) to read value from new record.

DECLARE

_fieldName VARCHAR:='';
_fieldValue VARCHAR;


BEGIN 

_fieldName = 'field1';


_fieldValue =  new[_fieldName];

or like this

execute 'select NEW.$1', into _fieldValue using _fieldName;

this is the solution:

 execute 'select $1.' || _fieldName using NEW into _fieldValue;

CodePudding user response：

If you want to use dynamic field names on triggers, then try this sample:

CREATE TABLE books (
    id serial NOT NULL, 
    bookcode int4 NULL,
    bookname text NULL
);

CREATE TABLE books_log (
    id serial4 NOT NULL,
    bookcode text NULL
);


CREATE OR REPLACE FUNCTION books_before_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare 
    v_field_name text;
    v_field_values text;
begin

    v_field_name = 'bookname';

    EXECUTE 'SELECT $1.' || v_field_name
    USING NEW
    INTO v_field_values;

    insert into books_log (bookcode) values (v_field_values);
  
    RETURN new;
end;
$function$
;


create trigger trigger_book_before_insert before
insert
    on
    public.books for each row execute function books_before_insert();