I want to execute two statements using the Query Editor. One to set the parameter search_path and another to run a query against PG_TABLE_DEF.
I need to submit both queries at once because the set statement seems to have no affect on subsequent queries.
set search_path to '$user', 'public', 'demo';
select * from pg_table_def where tablename = 'demotable';
However only the results for the first statement are returned.
Is there any way to return the results of the second statement?
Note: I cant change the users default search path or make any other changes to the database.
When you say query editor, you mean in the Redshift console UI?
If so, then as far as I know, it can only execute a single query, and when you have multiple queries, as you do here, only the first is executed.
The new (not so new now - in fact, I recall the original UI has now finally been permanently disabled, which sucks) UI has a lot of issues. This may or may not be one of them - you could I think argue for it on the basis of a design decision - it's certainly a restriction.