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 "(".
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.