Dynamic session variable in PostgreSQL

Question

Is it possible to run a query with a "dynamic" Session variable in PostgreSQL?

I can define a static value for a variable and use it in a query, but how can I set a "dynamic" variable based on an iteration through every single record of a table?

Static version with a defined variable:

SET SESSION my.vars.ref = 'My value';
SELECT *
FROM sourceTable
WHERE myField LIKE current_setting('my.vars.ref')::text;

I wan't know to repeat this for each single value of my sourceTable.

I know I can execute a basic Select but in my real case I run a recursive query who returns me aggregates data. I use this result to insert values in an other table.

Edit - More complex query:

SET SESSION my.vars.ref = 'My value';
SELECT CONCAT('Value: ', current_setting('my.vars.ref')::text)
FROM sourceTable
WHERE myField LIKE current_setting('my.vars.ref')::text;

Sounds like you are not doing this the SQL way. Rather than using "variables", use a join. — Laurenz Albe
– Laurenz Albe, Commented Jul 17, 2020 at 13:23
What would this join be based on ? I just have one table here — GeoGyro
– GeoGyro, Commented Jul 17, 2020 at 13:26
Well, the contents of the "variable" have to come from somewhere in the database. — Laurenz Albe
– Laurenz Albe, Commented Jul 17, 2020 at 13:44

score 1 · Accepted Answer · 2020-07-17 15:36:34Z

1

If you want to compare multiple values with LIKE, you can do that using ANY

select *
from sourcetable
where myfield LIKE ANY (array['Value one%', 'My Value 2%', 'Three%']);

No need for loops or variables.

Another option is to JOIN to a list of values:

select st.*, v.value
from sourcetable st
  join ( 
     values ('Value one%'), 
            ('My Value 2%'), 
            ('Three%')
  ) v(value) on st.myfield LIKE v.value;

edited Jul 17, 2020 at 15:36

answered Jul 17, 2020 at 13:40

user330315

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

3 Comments

GeoGyro Over a year ago

Ok and how can I use this values in next instructions of my query ? For example if I wan't to use Value one% ? I have edited my original post to be more explicit.

user330315 Over a year ago

@GeoGyro: if you want those values as part of the output again, another option is to join against a list of values. See my edit. In the future, please do not extend your questions with more details once you get an answer. Include all your requirements in the question right from the start

GeoGyro Over a year ago

Ok, thanks for your answer. In the last line of you're edit, it's vt(value) on st.myfield LIKE vt.value; I guess.

Collectives™ on Stack Overflow

Dynamic session variable in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related