PostgreSQL plpgsql - variable column names

8

votes

I am creating a trigger, which uses dynamic names for columns

NEW.name:=2222; -- works fine !

but

dynamic_column:='name';
EXECUTE '$1.'||dynamic_column||':=2222 ' USING NEW; -- raises error

gives an error:

ERROR: syntax error at or near "$1" LINE 1: $1.name:=2222

postgresqlplpgsql

8

votes

I found info here: Assign to NEW by key in a Postgres trigger

If we enable the module hstore by:

CREATE EXTENSION hstore;

We can do this:

dynamic_column:='name';    
temp_sql_string:='"'||dynamic_column||'"=>"2222"';
NEW := NEW #= temp_sql_string::hstore;

And the RECORD NEW.name now is set to the value 2222.

Thank you tough for making an effort to find a solution @Laurenz Albe

5

votes

The problem is that this is not a valid SQL statement.

You can access the columns in new with dynamic SQL like this:

EXECUTE 'SELECT $1.id' INTO v_id USING NEW;

There is no comfortable way like that for changing individual columns in NEW.

You could use TG_RELID to get the OID of the table, query pg_attribute for the columns, compose a row literal string composed of the values in NEW and your new value, cast this to the table type and assign the result to NEW. Quite cumbersome.

Here is sample code that does that (I tested it, but there may be bugs left):

CREATE OR REPLACE FUNCTION dyntrig() RETURNS trigger
   LANGUAGE plpgsql AS
$$DECLARE
   colname text;
   colval text;
   newrow text := '';
   fieldsep text := 'ROW(';
BEGIN
   /* loop through the columns of the table */
   FOR colname IN
      SELECT attname
      FROM pg_catalog.pg_attribute
      WHERE attrelid = TG_RELID
        AND attnum > 0
        AND NOT attisdropped
      ORDER BY attnum
   LOOP
      IF colname = 'name' THEN
         colval = '2222';
      ELSE
         /* all other columns than 'name' retain their value */
         EXECUTE 'SELECT CAST($1.' || quote_ident(colname) || ' AS text)'
            INTO colval USING NEW;
      END IF;

      /* compose a string that represents the new table row */
      IF colval IS NULL THEN
         newrow := newrow || fieldsep || 'NULL';
      ELSE
         newrow := newrow || fieldsep || '''' || colval || '''';
      END IF;
      fieldsep := ',';
   END LOOP;
   newrow := newrow || ')';

   /* assign the new table row to NEW */
   EXECUTE 'SELECT (CAST(' || newrow || ' AS '
           || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME)
           || ')).*'
      INTO NEW;

   RETURN NEW;
END;$$;

5

votes

You already found my answer recommending the hstore operator #= on dba.SE. You may also be interested in the corresponding reference answer here on SO:

How to set value of composite variable field using dynamic SQL

Since you construct the auxiliary hstore value from variables I suggest the simple function hstore():

CREATE OR REPLACE FUNCTION dyn_trigger_func()
  RETURNS TRIGGER AS
$func$
DECLARE
   dyn_col_name text := 'name';
   dyn_col_val  text := '2222';
BEGIN
   NEW := NEW #= hstore(dyn_col_name, dyn_col_val);
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Faster / simpler / clearer / more secure this way.

Or, since it's obviously a trigger function, you may want to pass column name and value in CREATE TRIGGER statements:

CREATE OR REPLACE FUNCTION dyn_trigger_func()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW := NEW #= hstore(TG_ARGV[0], TG_ARGV[1]);
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

And:

CREATE TRIGGER ins_bef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE dyn_trigger_func('name', '2222');

Provide column name unquoted and case-sensitive.

PostgreSQL plpgsql - variable column names

3 Answers