Trigger with dynamic field name

Question

I have a problem on creating PostgreSQL (9.3) trigger on update table. I want set new values in the loop as

EXECUTE 'NEW.'|| fieldName || ':=''some prepend data'' || NEW.' || fieldName || ';';

where fieldName is set dynamically. But this string raise error

ERROR:  syntax error at or near "NEW"

How do I go about achieving that?

It would be much more useful to provide a *complete* trigger function and the trigger definition to go with it. — Erwin Brandstetter, Aug 02 '16 at 13:14

Erwin Brandstetter · Answer 1 · 2017-07-22T02:06:31.030

You can implement that rather conveniently with the hstore operator #=:

Make sure the additional module is installed properly (once per database), in a schema that's included in your search_path:

Trigger function:

CREATE OR REPLACE FUNCTION tbl_insup_bef()
  RETURNS TRIGGER AS
$func$
DECLARE
   _prefix CONSTANT text := 'some prepend data'; -- your prefix here
   _prelen CONSTANT int  := 17;  -- length of above string (optional optimization)
   _col text := quote_ident(TG_ARGV[0]);
   _val text;
BEGIN
   EXECUTE 'SELECT $1.' || _col
   USING NEW
   INTO _val;

   IF left(_val, _prelen) = _prefix THEN 
      -- do nothing: prefix already there!
   ELSE
      NEW := NEW #= hstore(_col, _prefix || _val);  
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger (reuse the same func for multiple tables):

CREATE TRIGGER insup_bef
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW
EXECUTE PROCEDURE tbl_insup_bef('fieldName');  -- unquoted, case-sensitive column name

Closely related with more explanation and advice:

Yes, this is very useful sample. hstore extension exist but operator #= not found in the extension. — FlyBot, Aug 03 '16 at 08:37
@FlyBot: That's odd, [the operator is definitely included in the 9.3 version](https://www.postgresql.org/docs/9.3/static/hstore.html#HSTORE-OP-TABLE). Are you sure you installed it in *your database* and the schema is included in your `search_path`? I added links with details. — Erwin Brandstetter, Aug 03 '16 at 14:07

score 2 · Answer 2 · answered Aug 02 '16 at 08:13

2

Your problem is that EXECUTE can only be used to execute SQL statements and not PL/pgSQL statements like the assignment in your question.

You can maybe work around that like this:

Let's assume that table testtab is defined like this:

CREATE TABLE testtab (
   id integer primary key,
   val text
);

Then a trigger function like the following will work:

BEGIN
   EXECUTE 'SELECT $1.id, ''prefix '' || $1.val' INTO NEW USING NEW;
   RETURN NEW;
END;

I used hard-coded idand val in my example, but that is not necessary.

answered Aug 02 '16 at 08:13

Laurenz Albe

209,280
17
206
263

Thank you, very useful example. But in my case the field names are changing dynamically and I can not code them hard to the trigger. I need to substitute the name of the field as NEW.$1 for sample. Can I write EXECUTE 'SELECT $1.*, ''prefix '' || $1.$2' INTO NEW USING NEW, fieldName; ? – FlyBot Aug 02 '16 at 10:22
3

No, but you can use introspection (e.g. with `information_schema.columns`) to build the string and then use it in `EXECUTE`. – Laurenz Albe Aug 02 '16 at 11:59

score 0 · Accepted Answer · answered Aug 02 '16 at 11:45

I found a working solution: trigger should execute after insert/update, not before. Then desired row takes the form

EXECUTE 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME ||
                ' SET ' || fieldName || '= ''prefix:'' ||''' || fieldValue || ''' WHERE id = ' || NEW.id;

fieldName and fieldValue I get in the next way:

FOR fieldName,fieldValue IN select key,value from each(hstore(NEW)) LOOP
       IF .... THEN
END LOOP:

Trigger with dynamic field name

3 Answers3

Linked