1

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?

1

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.

2

You would have to use the pure string representation of the array.

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

For anyone else who comes across this question, I wound up encoding my arrays as strings and then using string_to_array() to decode inside the trigger function.

CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS
$$
DECLARE
    special_names text[];
    special_users text[];
BEGIN
  special_names := string_to_array(TG_ARGV[0], ', ');
  special_users := string_to_array(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(
    'magicname1, magicname2',
    'user1, user2'
);

This feels slightly like a hack, but it seems to work fine.

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.