PostgreSQL & PgAdmin set session variable to query result

Question

Why am I getting this error?

set session my.vars.id = SELECT "Name" FROM "Client" WHERE "ID" = 1;
SELECT * FROM "Client" WHERE "Name" = current_setting('my.vars.id');
...
ERROR:  syntax error at or near "SELECT"
LINE 1: set session my.vars.id = SELECT "Name" FROM "Client" WHERE "...
                                 ^

Laurenz Albe · Accepted Answer · 2019-01-17 11:25:19Z

5

Because you can only use constants in a SET command.

Try this:

SELECT set_config('my.vars.id', "Name", FALSE)
FROM "Client" WHERE "ID" = 1;

answered Jan 17, 2019 at 11:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

7 Comments

Freddy Bonda Over a year ago

I am trying to set a variable equal to a query's response. I then want to use that variable in many different querys. Would you mind showing me how your answer helps with this? Sorry I just don't know what set_config does

Freddy Bonda Over a year ago

SELECT set_config('var1', (SELECT "Name" FROM "Client" WHERE "ID" = 1), TRUE); SELECT * FROM "Client" WHERE "Name" = current_setting('var1');

Is this corrent? What does the TRUE do?

Laurenz Albe Over a year ago

It does exactly what you want - set my.vars.id to the result of your query. Please read the documentation for details. TRUE changes the value only for the duration of the current transaction.

Freddy Bonda Over a year ago

When you say ..value only for the duration of the current transaction do you mean transaction = script? The value is changed for the current script?

Laurenz Albe Over a year ago

No, I mean a database transaction. Did you try my answer? Does it work as you want?

|

Collectives™ on Stack Overflow

PostgreSQL & PgAdmin set session variable to query result

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related