Postgresql – how to get back the modified INOUT parameter in postgres

castfunctionspostgresqltrigger

I have two functions with a similar signature, lets say:

CREATE FUNCTION func1(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;
CREATE FUNCTION func2(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;

I call both of these functions from a BEFORE UPDATE TRIGGER where the parameter is the row being updated. I want to give the row to the function as an input but subsequently modify it inside the function (therefore the INOUT).

I call the first function as:

SELECT * FROM func1(NEW) INTO NEW;

Which works just fine.

However, I can't make it work with the second function because the type of NEW is no longer of "My_Table" but it is "record" and I am unable to cast it.

SELECT * FROM func1(NEW) INTO NEW;

SELECT * FROM func1(NEW::"My_Table") INTO NEW;

SELECT * FROM func1(CAST(NEW AS "My_Table")) INTO NEW;

gives

ERROR:  42846: cannot cast type record to "My_Table"

How can I do this please?

Best Answer

These are the two working solutions:

NEW := func1(NEW);

or first cast the record to text:

SELECT * FROM func1(NEW::text::"My_Table") INTO NEW;

Related Solutions

Postgresql – How to insert a record returned by a function into a table

Assuming current Postgres version 9.5, since it has not been defined.
Best solved with a LATERAL join (Postgres 9.3+).

The minimal working form:

INSERT INTO table_output
SELECT f.*
FROM   table_input t, my_func(t.id) f;

The same, more explicit:

INSERT INTO table_output (foo, bar)
SELECT f.foo, f.bar
FROM   table_input t
CROSS  JOIN LATERAL my_func(t.id) AS f;

Or, since exactly one result row is guaranteed in this case, a plain function call in a subquery does the same, probably cheaper:

INSERT INTO table_output
SELECT f.*
FROM  (SELECT my_func(id) AS f FROM table_input) t;

It's wise to declare target columns in a persisted INSERT, even if the row types of function and table currently match. This is more robust against later changes.

CROSS JOIN LATERAL is just the verbose equivalent of a simple comma before a set-returning function in the FROM list. (Binding more closely, but that's not relevant in this query.)

Detailed explanation for LATERAL and how to avoid repeated function evaluation in this recent, related answer on SO - with links to more:

Split function-returned record into multiple columns

Postgresql – How to pass a row as a parameter to a Postgres function

It's much simpler that that. While one would expect it to be ...

SELECT build_details(my_table.*, 1000, TRUE) FROM my_table LIMIT 1;

... the actual syntax is:

SELECT build_details(my_table, 1000, TRUE) FROM my_table LIMIT 1;

Best Answer

Related Solutions

Postgresql – How to insert a record returned by a function into a table

Postgresql – How to pass a row as a parameter to a Postgres function

Related Question