Using EXECUTE in WITH query in postgresql

Question

I am extracting data from a table using execute query. now I want to store this data in some variable to use it for other operation, and I cannot use temp table so I have to use WITH query.

--Something like this:

with ttable (col1, col2) as (execute 'select tcol1, tcol2 from tab_sample')
insert into tab_sam2 select col1,col2 from ttable;

Now this gives me error at execute saying syntax error at or near execute.

How can I do that. Also, Is there any alternate to store multiple data coming from a table in a procedure/function without using array or temp table?

EXECUTE 'SELECT blah FROM ...' INTO your_var; You don't need a CTE for this. In fact, unless you're using a dynamic statement, you don't need execute - just do SELECT blah INTO your_var FROM ... — 404
– 404, Commented May 3, 2018 at 16:22

Laurenz Albe · Accepted Answer · 2018-05-03 18:59:19Z

1

EXECUTE is a PL/pgSQL statement and cannot be mixed into SQL.

Why don't you do it like this:

EXECUTE E'WITH ttable (col1, col2) as (\n'
         '      SELECT tcol1, tcol2 FROM tab_sample\n'
         '   )\n'
         'INSERT INTO tab_sam2\n'
         '   SELECT col1,col2 FROM ttable';

answered May 3, 2018 at 18:59

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

2 Comments

Vishal D Over a year ago

That would work, Thanks. But If am storing dynamic query in a variable say var_dyn, how can I use it instead of last line itself?

Laurenz Albe Over a year ago

Just append it with ||.

Collectives™ on Stack Overflow

Using EXECUTE in WITH query 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