Postgresql: How to pass an array to trigger function in TG_ARGV

arrayplpgsqlpostgresqltrigger

I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be available in the TG_ARGV array, as described in the docs.

This works fine as long as all of my arguments are scalars, but there are a couple of them that really should be represented as arrays.

Here's a simplified example of what I'm trying to do:

CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS
$$
DECLARE
    special_names text[];
    special_users text[];
BEGIN
  special_names := TG_ARGV[0];
  special_users := TG_ARGV[1];
  IF NEW.name = ANY(special_names) THEN
    NEW.name = 'special';
  END IF;
  IF NEW.user = ANY(special_users) THEN
    NEW.user = 'flag';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_function(
    ARRAY['magicname1', 'magicname2']::text[],
    ARRAY['user1', 'user2']::text[]
);

Postgres complains with

ERROR: syntax error at or near "["

LINE 5: ARRAY['magicname1', 'magicname2']::text[],

In other contexts, that's valid syntax for an array literal (e.g. SELECT ARRAY['magicname1', 'magicname2']::text[]; works fine). I've also tried using other syntax varieties, like '{"magicname1", "magicname2"}'::text[] but without any success.

Can anyone suggest a good approach for passing an array parameter to a trigger function? Is it just impossible and I should give up and encode/decode via a JSON string or something?

Best Answer

string_to_array() in your solution is only useful if you want to allow non-standard array syntax. Else it's simpler and cheaper to just cast. And you can do the assignment at declaration time. Like:

CREATE OR REPLACE FUNCTION example_function()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
DECLARE
    special_names text[] := TG_ARGV[0]::text[];
    special_users text[] := TG_ARGV[1]::text[];
BEGIN
   IF NEW.name = ANY(special_names) THEN
      NEW.name = 'special';
   END IF;
   IF NEW.user = ANY(special_users) THEN
      NEW.user = 'flag';
   END IF;
   RETURN NEW;
END
$func$;

Use proper array syntax in the trigger as suggested by Jeff:

CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW                             
EXECUTE PROCEDURE example_function(
    '{magicname1,magicname2}',        
    '{user1,user2}'
);

{magicname1,magicname2} is standard text representation of arrays in Postgres for input and output.

Related Question