PostgreSQL: SET statement from a variable

Question

In general I found the treatment of variables in Postgres much confusing compared to typical programming languages, and then I came to this one:

DO $$
DECLARE
    x INTEGER;
BEGIN
    x := 5; -- imagine doing some involved query to get to this value
    RAISE NOTICE 'x is %', x;
    SET app.foo = x;
    RAISE NOTICE 'app.foo is %', (SELECT current_setting('app.foo'));
END;
$$;

Much to my surprise, app.foo is x gets printed, where I expected app.foo is 5. From there I arrive to the conclusion that the right hand side of the SET statement is not evaluated, and it has to be constant. Is this how it's supposed to be?

Is there any way to assign a result of a query into a configuration parameter?

Context: I'm using current_setting for row level security ala https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql

question from:https://stackoverflow.com/questions/65907332/postgresql-set-statement-from-a-variable

Answer 1

You need to use EXECUTE:

DO $$
DECLARE
    x INTEGER;
BEGIN
    x := 5; -- imagine doing some involved query to get to this value
    RAISE NOTICE 'x is %', x;
    EXECUTE 'SET app.foo =' || x::text;
    RAISE NOTICE 'app.foo is %', (SELECT current_setting('app.foo'));
END;
$$;

NOTICE:  x is 5
NOTICE:  app.foo is 5
DO

For more information see Execute. In particular:

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.