CODEWITHSUNDEEP
postgresqltriggersdatabase-trigger
Airn5475
Airn5475

Reputation: 2492

How to set a composite type column using dynamic sql in trigger procedure

I have a trigger function that is called by several tables when COLUMN A is updated, so that COLUMN B can be updated based on value from a different function. (More complicated to explain than it really is). The trigger function takes in col_a and col_b since they are different for the different tables.

IF needs_updated THEN
    sql = format('($1).%2$s = dbo.foo(($1).%1$s); ', col_a, col_b);

    EXECUTE sql USING NEW;
END IF;

When I try to run the above, the format produces this sql: 

($1).NameText = dbo.foo(($1).Name);

When I execute the SQL with the USING I am expecting something like this to happen (which works when executed straight up without dynamic sql):

NEW.NameText = dbo.foo(NEW.Name);

Instead I get:

[42601] ERROR: syntax error at or near "$1"

How can I dynamically update the column on the record/composite type NEW?

Upvotes: 3

Views: 293

Answers (2)

Jim Nasby
Jim Nasby

Reputation: 1268

First: This is a giant pain in plpgsql. So my best recommendation is to do this in some other PL, such as plpythonu or plperl. Doing this in either of those would be trivial. Even if you don't want to do the whole trigger in another PL, you could still do something like:

v_new RECORD;
BEGIN
v_new := plperl_function(NEW, column_a...)

The key to doing this in plpgsql is creating a CTE that has what you need in it:

c_new_old CONSTANT text := format(
  'WITH
    NEW AS (SELECT (r).* FROM (SELECT ($1)::%1$s r) s)
    , OLD AS (SELECT (r).* FROM (SELECT ($2)::%1$s r) s
    '
  , TG_RELID::regclass
);

You will also need to define a v_new that is a plain record. You could then do something like:

-- Replace 2nd field in NEW with a new value
sql := c_new_old || $$SELECT row(NEW.a, $3, NEW.c) FROM NEW$$
EXECUTE sql INTO v_new USING NEW, OLD, new_value;

Upvotes: 0

Ildar Musin
Ildar Musin

Reputation: 1468

This isn't going to work because NEW.NameText = dbo.foo(NEW.Name); isn't a correct sql query. And I cannot think of the way you could dynamically update variable attribute of NEW. My suggestion is to explicitly define behaviour for each of your tables:

IF TG_TABLE_SCHEMA = 'my_schema' THEN
    IF TG_TABLE_NAME = 'my_table_1' THEN
        NEW.a1 = foo(NEW.b1);
    ELSE IF TG_TABLE_NAME = 'my_table_2' THEN
        NEW.a2 = foo(NEW.b2);
    ... etc ...
    END IF;
END IF;

Upvotes: 0

Related Questions