Let’s say I have a function that returns type record:
CREATE OR REPLACE FUNCTION pnh_row(myId integer)
RETURNS RECORD AS $$
DECLARE
myRow RECORD;
BEGIN
// blah blah
return myRow;
END;
$$ language plpgsql;
How can I insert and update this record into a table (preferably without naming each individual field).
I tried:
insert into mytable select pnh_row(1234);
but I get:
ERROR: column "id" is of type integer but expression is of type record
LINE 1: insert into mytable select pnh_row(1234);
^
HINT: You will need to rewrite or cast the expression.
Ideally, I could also update the whole record from this tuple also, but I can’t think what syntax to even try. I guess I could do either one by tediously listing out every column, but is there a way to avoid that?
10
If your function returns a record, you must write a column definition list at the call site, there is no way to avoid that. So instead give the function the proper return type, the row type of the table in which you want to insert: RETURNS mytable. Then you can run
INSERT INTO mytable
SELECT *
FROM pnh_row(1234);
3
Disclaimer
Like I commented, the best course of action is not to make that function return an anonymous record to begin with. There are typically better options.
If you indeed need a function, here are some related answers:
-
Function returning a table for given table and column names
-
How to return multiple rows from PL/pgSQL function?
-
Refactor a PL/pgSQL function to return the output of various SELECT queries
Solution with cast
INSERT
While stuck with your unfortunate situation, there is a way to make it work without naming each individual field:
Your attempt:
insert into mytable select pnh_row(1234);… does not work, because the INSERT tries to assign the returned value (the whole anonymous record) to the first column id of mytable (apparently an integer), which raises a type mismatch exception.
You cannot decompose the returned record since, being anonymous, its structure is unknown:
INSERT INTO mytable SELECT (pnh_row(1234)).*;The manual:
If the function has been defined as returning the
recorddata type,
then an alias or the key wordASmust be present, followed by a column
definition list in the form( column_name data_type [, ... ]). The
column definition list must match the actual number and types of
columns returned by the function.
You would have to tediously list out every column – each followed by its data type – the thing you want to avoid.
You cannot cast it to the row type mytable because there is no registered cast.
INSERT INTO mytable SELECT (pnh_row(1234)::mytable).*;However, everything can be cast to and from text. So you can use text as stepping stone:
INSERT INTO mytable SELECT (pnh_row(1234)::text::mytable).*;
But decomposing this way results in a separate function call for every nested column.
Instead, move the function call to a subquery to execute it once, and only decompose in the SELECT list:
INSERT INTO my_table
SELECT (my_row).*
FROM (SELECT pnh_row(123)::text::my_table) t(my_row);
Note the parentheses required to access fields of the composite type in (my_row).*.
Still, casting to text and back adds cost.
Of course, the record type has to match the row type of my_table.
See:
- How to avoid multiple function evals with the (func()).* syntax in a query?
UPDATE
You can use similar syntax to avoid a column definition list. While you can omit target column names in an INSERT (only advisable in certain cases like the one at hand), you must spell out target column names in an UPDATE:
UPDATE my_table
SET col1 = (t.my_row).col1
-- , ... more?
FROM (SELECT pnh_row(123)::text::my_table) t(my_row) -- !
WHERE my_table_id = 123; -- or whatever?
But you can omit source column names with multiple-column UPDATE syntax:
UPDATE my_table
SET (my_table_id, col1, col2, ...) -- ①
= (SELECT (my_row).* FROM (SELECT pnh_row(123)::text::my_table) t(my_row)) -- !
WHERE my_table_id = 123; -- or whatever?
① List target columns for all source columns in matching order!
You could even fetch column names from the system catalog pg_attribute and build the statement dynamically. See:
- SQL update fields of one table from fields of another one
But this is getting ridiculous …
Solution with (temporary) wrapper function
Create once (per session, if temporary):
CREATE OR REPLACE pg_temp.my_wrapper(my_id int, OUT rec my_table)
LANGUAGE plpgsql AS
$func$
BEGIN
rec := pnh_row($1); -- assignment succeeds (!)
END
$func$;
Queries are simple again:
INSERT INTO my_table
SELECT * FROM pg_temp.my_wrapper(123);
UPDATE my_table
SET (my_table_id, col1, col2, ...)
= (SELECT * FROM pg_temp.my_wrapper(123))
WHERE my_table_id = 123; -- or whatever?
See:
- Automatic conversion of anonymous records returned from an UDF to well known table-type
- How to create a temporary function in PostgreSQL?