Setting application_name using a variable post connection?

Question

I'm trying to set the application_name using a variable inside a pl/pgsql function. I need to set a few vars so I'd like to wrap in a single function rather than have the clients run the command several times.

However when I run

my_variable := 'foo';
SET application_name = my_variable;

It sets the application_name to my_variable instead of foo.

This is on postgresql11. I've tried quoting the variable in various ways to make it evaluate but so far no luck.

When I run a simple

SET application_name = 'foo';

It works as expected. I've tried some of the string quoting from another answer: How do you use script variables in psql? but none of these seemed to work for me.

The code I have so far is:

CREATE OR REPLACE FUNCTION app_name_test(
                                                p_application_name VARCHAR(64)
                                        ) RETURNS BOOLEAN
    LANGUAGE plpgsql
AS $$
DECLARE
BEGIN

    RAISE INFO 'Setting Application Name To: %', p_application_name;

    SET application_name = p_application_name;

    RETURN TRUE;
END;
$$;


SELECT app_name_test('This_is_a_test');

I'm fairly sure it's possible but I must have to quote the strings in a certain way. Many thanks in advance.

Edit: The answer in comments from a_horse_with_no_name below solved the issue.

EXECUTE FORMAT('SET application_name = %L', p_application_name);

Would be interesting though to know why this works for tables (variables expanded) but not for this particular SQL. E.g.

SELECT * from table where column = my_var;

Works but the above didn't.

Did you try dynamic SQL? `execute format('set application_name = %L', p_application_name);` — , Aug 26 '19 at 21:08
This worked. Many thanks. I'll edit the question to show solved. — BlueIris, Aug 26 '19 at 21:43

score 2 · Accepted Answer · answered Aug 27 '19 at 04:37

A statement like

SELECT x INTO dest FROM atable WHERE column = my_var;

where my_var is a PL/pgSQL variable will actually be turned into a parameterized query:

SELECT x FROM atable WHERE column = $1;

and the value of my_var will be supplied as parameter value.

Using variables in PL/pgSQL statements works if and only if the statement supports parameters.

Now SELECT supports parameters (as does INSERT, UPDATE and DELETE), but SET (like all “utility statements”) doesn't.

This is why you have to use dynamic SQL if you want to use a variable in a utility statement.

Setting application_name using a variable post connection?

1 Answers1