CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sqlcreate-table
KiaShakiba
KiaShakiba

Reputation: 128

PL/pgSQL INSERT INTO table

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?

Upvotes: 0

Views: 1322

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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.

Upvotes: 1

Related Questions