POSTGRES stored procedure and SET session variable

Question

Does anyone know why this works this way? When I tried in other stored procedures and text is normally replaced with the value that is specified as stored procedure parameter.

CREATE OR REPLACE FUNCTION setUserId(userId text)
returns void AS $$
    BEGIN 
    SET session "myapp.userId" = userId;
    END
$$ LANGUAGE plpgsql;

app=# select * from setUserId('myuserId');
 setuserid
-----------

(1 row)

app=# select current_setting('myapp.userId');
 current_setting
-----------------
 userid
(1 row)

app=#

Edouard · Accepted Answer · 2021-12-16 21:50:02Z

3

According to the manual,

SET session "myapp.userId" = userId

is equivalent to

SET session "myapp.userId" = 'userId'

so the input parameter name userId is considered as the new value for the run-time parameter instead of the input parameter value 'myuserId'.

If you want the run-time parameter to consider the value 'myuserId' of the input parameter userId, you need a dynamic statement :

CREATE OR REPLACE FUNCTION setUserId(userIdtext)
returns void AS $$
    BEGIN
    EXECUTE '
    SET session "myapp.userId" = ' || quote_nullable(userId) ;
    END
$$ LANGUAGE plpgsql;

demo in dbfiddle

answered Dec 16, 2021 at 21:50

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

1 Comment

user330315 Over a year ago

Alternatively use set_config('myapp.userId', userid)

Collectives™ on Stack Overflow

POSTGRES stored procedure and SET session variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related