PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use an anonymous record type in an insert or update?

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?

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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:

    Solution

    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 record data type,
    then an alias or the key word AS must 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 that would make the bad solution even worse. 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);

    Of course, the record type has to match the row type of my_table.

    See:

    An UPDATE can use a similar trick to avoid spelling out a column definition list:

    UPDATE my_table
SET    my_column1 = (t.my_row).my_column1
FROM  (SELECT (pg_temp.pnh_row(123))::text::my_table) t(my_row);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search