Set a variable in Postgres

Question

I want to set search path in a function for which the schema name to set the search path has to be selected from another table. I want to do something like :

set search_path to (select db_schema_name from Schenma_Name_Table  where lookup_id = 'xxxx')

this syntax is not working. It says - syntax error at or near "(".

Answer 1

set can't use dynamic expressions, but you can use set_config() which accepts any expression:

select set_config('search_path', (select string_agg(db_schema_name, ',')
                                  from some_table 
                                  where lookup_id = 'xxxx'), false);

Note that I used string_agg() in case the query returns more than one value.