Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

postgresql - Trigger with dynamic field name

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...