create a temp table with the output of a stored procedure in postgres

Question

I want a query to create a table with the output of a stored procedure function in Postgres.

SQL:

CREATE TEMP TABLE new_project AS select project_insert('1','test2343','tew3234','ccc',1);

Error:

ERROR:  42P16: column "projects_insert" has pseudo-type record
LOCATION:  CheckAttributeType, heap.c:513

Note: project_insert is a function which insert the values and returns inserted values

Laurenz Albe · Accepted Answer · 2017-09-27 10:20:09Z

1

The problem is that project_insert has been declared with RETURNS record, and that type is illegal for a column definition.

You have to specify name and type of the result columns in the query, like this:

CREATE TEMP TABLE new_project AS
   SELECT x, y, z
   FROM project_insert('1','test2343','tew3234','ccc',1)
        AS p(x integer, y text, z bytea);

Replace the names and types with the appropriate names and types.

answered Sep 27, 2017 at 10:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Answer 1