3

In the trigger body, how can I assign a value to NEW by it's field name?
That's what I want to do:

some_key = "some_column";
NEW[some_key] = 5;
  • 1
    I know that, thats why I raised this question. There is a hacky way to access variable from NEW dynamically like this EXECUTE 'SELECT ($1).'||column_name||' ;' INTO some_var USING NEW; but I cant assign to NEW like that. @dezso – Ben Nov 6 '14 at 9:19
  • And @dezso , whenever someone calls map an array - my heart bleeds – Ben Nov 6 '14 at 9:21
  • Yes, but can you access in postgres array by key? Im just saying that some scripts don't show difference between array and map, and I know people that programm such scripts and don't know the difference between those two. – Ben Nov 6 '14 at 9:27
  • Thanks for your comments @dezso, I guess you just can't achieve this in postgres. – Ben Nov 6 '14 at 9:37
10

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also called procedure) with a function body and 0-n triggers (without body) calling this function.

The special variable NEW in plpgsql trigger functions is neither a map nor an array; it's a row:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

Assigning to field (or column) of NEW is simple. The documented assignment operator is :=. (Since Postgres 9.4 = is also documented.)

NEW.some_key := 5;

What you seem to be looking for is to parameterize the column name, which isn't quite as simple.
The additional module hstore provides the #= operator. (It's included in pretty much all standard distributions.) Install the module once per database with:

CREATE EXTENSION hstore;

Then you can:

NEW := NEW #= '"some_key"=>"5"'::hstore;

Sets the column some_key to '5' - if the column exists.

  • An explicit cast to hstore is optional. The operator #= coerces a string literal to the right data type automatically.
  • hstore only stores text strings, so a given literal for the value may have to be cast twice - a very minor drawback compared to alternative solutions.
  • The given string literal has to fit the data type of the column, or an exception is raised.
  • If no column with the given name exists, nothing is changed, no exception raised.

Related answer with details and an alternative solution:

Code example

CREATE OR REPLACE FUNCTION trg_tbl_ins_bef()
  RETURNS trigger AS
$func$
BEGIN
   NEW := NEW #= '"some_key"=>"5"';
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER ins_bef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_ins_bef();
  • I haven't thought about hstore, it's much simpler than my approach. – dezso Nov 6 '14 at 10:15
  • 1
    Check out the linked answer. Pavel and I worked hard on a solution before the #= operator was introduced - and found one, too. – Erwin Brandstetter Nov 6 '14 at 10:17
  • I didn't said NEW is a map or array, writing this NEW[some_key] = 5; I was just giving a hint what I want to achieve. The thing with hstore is genius, would never think of it by myself. – Ben Nov 6 '14 at 10:47
2

I must admit that this is no easy way of solving it, but at least it's a way. I created the below example as a standalone one, to avoid all clutter with trigger creation and such. If you use it in a trigger, you can remove the declaration and initialization of p and replace the remaining use with NEW.

DO $$
DECLARE p members_test; 
BEGIN
    p := (1,2,3);
    CREATE TEMP TABLE t ON COMMIT DROP AS SELECT p.*; -- a one row table holding 
                                                      -- the values of the variable

    EXECUTE format($e$UPDATE t SET %s = 43$e$, 'b'); -- this way you can access 
                                                     -- the columns dynamically

    SELECT * INTO p FROM t; -- assign the new values back to the variable
    RAISE INFO 'p: %', p;
END;
$$;

INFO:  p: (1,43,3)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.