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?

Report

It would be much more useful to provide a complete trigger function and the trigger definition to go with it. – Froghopper

Answer 1

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:

Report

Answer 2

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:

• How to use % operator from the extension pg_trgm?
• Best way to install hstore on multiple schemas in a Postgres database?

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:

• Assignment of a column with dynamic column name
• How to access NEW or OLD field given only the field's name?
• Get values from varying columns in a generic trigger

Froghopper

Yes, this is very useful sample. hstore extension exist but operator #= not found in the extension. – Report

@FlyBot: That's odd, the operator is definitely included in the 9.3 version. Are you sure you installed it in your database and the schema is included in your search_path? I added links with details. – Froghopper

Answer 3

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.

Lovins

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; ? – Report

No, but you can use introspection (e.g. with information_schema.columns) to build the string and then use it in EXECUTE. – Lovins