2

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?

4 Answers 4

3

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 FUNCTION example_function(
    '{magicname1,magicname2}',        
    '{user1,user2}'
);

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

A trigger takes string arguments. We have to pass an array literal, not an array constructor. The manual:

arguments

An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants. Simple names and numeric constants can be written here, too, but they will all be converted to strings. Please check the description of the implementation language of the trigger function to find out how these arguments can be accessed within the function; it might be different from normal function arguments.

An array constructor needs to be executed to build an array from the input list, and we cannot execute anything in a DDL SQL call. We need to pass a literal string. See:

These trigger arguments are completely separate from normal function arguments. In fact, trigger functions are created without arguments. The manual:

Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER — such arguments are passed via TG_ARGV, as described below.

The array literal is then cast to an actual array in the function: TG_ARGV[0]::text[].

About EXECUTE FUNCTION vs. EXECUTE PROCEDURE:

6
  • 2
    Can you explain why {a,b,c} is the syntax, and not ARRAY['a', 'b', 'c'] which is what seems to be postgres array syntax in other scenarios. Commented Mar 29 at 3:31
  • 1
    @xpusostomos The trigger takes a string argument, so you have to pass an array literal, not an array constructor. See: dba.stackexchange.com/a/114562/3684 Commented Mar 29 at 9:42
  • 1
    Hmm... the trigger function is declared without explicit arguments, and you have to extract them with TG_ARGV. Surely you should be able to pass anything you want and have it pop out in TG_ARGV.... and it's not like '{a,b}' pops out in TG_ARGV as a string, it seems to pop out as an actually array. Perhaps it's too much to ask why the behavior is so esoteric, but I don't really get how supposedly ARRAY['a','b'] is creates a thing in Postgres, but it isn't accepted in some cases. Commented Mar 30 at 3:55
  • 2
    It's not even as if it gives an error "wrong type" or some such, it's syntax error, as if the parser never heard of ARRAY[] before. Commented Mar 30 at 3:55
  • 1
    @xpusostomos: "and it's not like '{a,b}' pops out in TG_ARGV as a string". But that's exactly how it is. I added more explanation, yet. Please follow the links to the manual. Commented Mar 30 at 14:31
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}'
);
1

@bjmc this is what you should have done:

CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS
$$
DECLARE
    special_names text[];
    special_users text[];
BEGIN
  special_names := TG_ARGV[0]::text[];
  special_users := TG_ARGV[1]::text[];
  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}'
);
1
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.

1
  • that's not the way to do it. I'll show you below what you should have done. Commented Mar 31 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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