Using execute in subquery in postgresql

Question

I am creating a dynamic query in my function from which I need to extract data. now I want to execute that query.

something like this:

declare
df_id varchar;
BEGIN
/*creating dynamic query in run time and saving it in df_id in string format
say df_id='select col from schema.table_name**'*/
CREATE TEMP TABLE temp_table ON COMMIT DROP AS
    execute df_id;

How can I do this? Also, Is there any other way that I can execute the query in string variable and store it to temp table?

user330315 · Accepted Answer · 2018-05-04 10:56:56Z

2

You need to include the CREATE TABLE in your dynamic SQL.

Assuming that the variable df_id already contains the SELECT statement, you can do something like this:

df_id := 'CREATE TEMP TABLE temp_table ON COMMIT DROP AS '||df_id;
execute df_id;

answered May 4, 2018 at 10:56

user330315

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

2 Comments

Vishal D Over a year ago

Thanks that worked. Can I store this data in array also? if yes How? because in some cases I am not allowed to use temp table.

user330315 Over a year ago

@VishalD: yes that's possible, but you should ask a new question for that. Do not extend the scope of your question once you have an answer

Collectives™ on Stack Overflow

Using execute in subquery in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related