PostgreSQL using variable name as string literal

Question

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');

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 31, 2017 at 20:58

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DWilches Over a year ago

Ha! Thanks, I looked at a different documentation page that didn't make it that clear.

Collectives™ on Stack Overflow

PostgreSQL using variable name as string literal

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related