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?
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:
search_path? I added links with details.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.
information_schema.columns) to build the string and then use it in EXECUTE.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:
