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, May 03 '18 at 16:22

score 1 · Accepted Answer · answered May 03 '18 at 18:59

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 03 '18 at 18:59

Laurenz Albe

209,280
17
206
263

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? – Vishal D May 04 '18 at 07:24
Just append it with `||`. – Laurenz Albe May 04 '18 at 07:26

Using EXECUTE in WITH query in postgresql

1 Answers1