CODEWITHSUNDEEP
postgresqlplpgsql
jean-yves boisiaud
jean-yves boisiaud

Reputation: 49

in a trigger, how to use NEW for a dynamic query?

I am trying to write a generic function that uppercase a parametrized column. The function is called by a trigger run before insert or update.

I tried several triggers:

CREATE OR REPLACE FUNCTION uppercase()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE '$1.TG_ARGV[0] := UPPER($1.TG_ARGV[0])' USING NEW;
  RETURN NEW;
END;

or

CREATE OR REPLACE FUNCTION uppercase()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE FORMAT('NEW.%I := UPPER(NEW.%I)', TG_ARGV[0], TG_ARGV[0]);
  RETURN NEW;
END;

But all failed.

How could I declare such a trigger, with a parametrized column name ?

Thanks for you help!

Upvotes: 2

Views: 76

Answers (1)

Zegarek
Zegarek

Reputation: 26347

  1. See if you can get away just by hiding this behind a view that applies the upper() wherever it's needed on the fly, during reads, without having to persist that form anywhere.
  2. If you want the uppercase persisted in a separate field, consider a generated column.
  3. If it's to make searches case-insensitive, use a case-insensitive non-deterministic collation (or citext but the other option is faster, more flexible and powerful, better maintained).
  4. You can also index an expression on the column and use upper() in that expression, so even if you don't persist that form in the table, it's used for search just the same, as long as you reference that expression from the query.

If none of those options seem suitable, you can convert NEW which is type record, to jsonb type (to_jsonb() does that automagically). That lets you reference its fields directly using the text you're getting from the TG_ARGV array, even without the need for dynamic SQL. Set the field, then map it back.

@Stefanov.sm's idea shortens it by swapping out the jsonb_set() for a JSON || union with a fresh object holding just the target key with its new value.
demo1 at db-fiddle

CREATE OR REPLACE FUNCTION trigger_copy_data()RETURNS TRIGGER AS $BODY$
DECLARE njb    jsonb := to_jsonb(new); 
        target text  := TG_ARGV[0];
BEGIN RETURN
jsonb_populate_record(new,njb||jsonb_build_object(target,upper(njb->>target)));
END $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_order_backup
  BEFORE INSERT ON orders
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_copy_data('customer_name');

It does what it says on the tin: you insert a lowercase value, it intercepts it and based on the trigger function argument, switches the desired column to uppercase:

insert into orders values('order4','customer4',4)returning*;
order_name customer_name total_amount
order4 CUSTOMER4 4

The reason your method didn't work is that the code inside execute uses SQL syntax, not PL/pgSQL, and the assignment with := walrus operator isn't part of it. You'd also have to return the updated NEW record back outside the execute because execute..using doesn't do the type of pass-by-reference where you can use the reference to overwrite the value for everyone else.

You'd probably need to execute..using NEW into NEW;, passing it in, changing, then returning to overwrite the previous value with the updated one.

Alternatively, you could apply the change after the fact, with a statement-level trigger that only fires one single time per insert instead of as many times as you have incoming rows:
demo2 at db-fiddle
demo3 at db-fiddle, with update..from

CREATE OR REPLACE FUNCTION trigger_copy_data()RETURNS TRIGGER AS $BODY$
BEGIN 
EXECUTE FORMAT('UPDATE %1$I.%2$I SET %3$I=upper(%3$I)
                WHERE id IN(SELECT id FROM new_table);'
               ,TG_TABLE_SCHEMA
               ,TG_TABLE_NAME
               ,TG_ARGV[0]);
RETURN null;
END $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_order_backup
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_table
  FOR EACH STATEMENT
  EXECUTE PROCEDURE trigger_copy_data('customer_name');

The change won't yet be visible in insert..returning. The transition table cannot be the target of an update, and this is only allowed as an after trigger - in result, it's a single, bulk change, but it happens in addition to the insert that triggered it, whereas the before..for each row changes the values one by one, but it does that on the fly, before they get saved.
You'll have to test on your own setup to see which one performs better.

Upvotes: 2

Related Questions