2

In this script I expected the output to be:

NOTICE:  00000: Outside value is: hello
NOTICE:  00000: Inside value is: hello

but the output was:

NOTICE:  00000: Outside value is: hello
NOTICE:  00000: Inside value is: my_variable

Why is PostgreSQL using the name of the variable as a string literal when passing it to the function? Notice I didn't put single quotes around the variable name, so I was not expecting it to be interpreted as a string literal.

Here is the script:

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table(my_field text);

CREATE OR REPLACE FUNCTION my_function() RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'Inside value is: %', TG_ARGV[0];
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    my_variable text := 'hello';
BEGIN
    RAISE NOTICE 'Outside value is: %', my_variable;
    CREATE TRIGGER my_trigger
        AFTER INSERT ON my_table 
        FOR EACH ROW 
        EXECUTE PROCEDURE my_function(my_variable);
END
$$;

INSERT INTO my_table VALUES('some value');
Daniel
  • 21,933
  • 14
  • 72
  • 101

1 Answers1

3

From the documentation (emphasis added):

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.

CREATE TRIGGER is a DDL command in which variables are not known. You need dynamic SQL to use a variable in this case. See also this post.

Community
  • 1
  • 1
klin
  • 112,967
  • 15
  • 204
  • 232