PL/pgSQL INSERT INTO table

Question

I have a plpgsql function that creates an execute statement and I want it to execute it into a table. The standard EXECUTE ... INTO table_name didn't work, so I was looking for an alternative. Basically, the select statement returns three columns which I want to save to a table.

Here's some sample code of the execute:

query = 'SELECT name, work, phone FROM info WHERE name = '
        || quote_literal(inName) || ' ORDER BY phone';

Ideally, if I was just running the query myself I would just put a SELECT INTO tablename, but that didn't work with the execute.

Any ideas?

"*to execute it into a table*" makes no sense at all – Apr 03 '14 at 14:31 — , Apr 03 '14 at 14:31
You can select into tables, why not execute? :O – KiaShakiba Apr 03 '14 at 14:33 — KiaShakiba, Apr 03 '14 at 14:33

score 1 · Accepted Answer · edited May 23 '17 at 10:26

Use CREATE TABLE AS for that:

EXECUTE 'CREATE TABLE foo AS
   SELECT name, work, phone
   FROM info WHERE name = ' || quote_literal(in_name) || ' ORDER BY phone';

SELECT INTO is discouraged for that purpose:
Combine two tables into a new one so that select rows from the other one are ignored

SELECT / EXECUTE .. INTO .. is meant for single rows, not for whole tables in plpgsql.

And the assignment operator in plpgsql is :=, not =.
And a single quote was missing.
And don't use unquoted mixed case identifiers in Postgres.

PL/pgSQL INSERT INTO table

1 Answers1