CODEWITHSUNDEEP
sqlpostgresqlfunctionstored-procedures
Thavaprakash Swaminathan
Thavaprakash Swaminathan

Reputation: 6976

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

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

Upvotes: 2

Views: 1532

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

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.

See the documentation for details.

Upvotes: 1

Related Questions