Combining scalar and row types in RETURNING
Can you combine scalar and row types in a RETURNING clause?
My use-case is getting the results of a MERGE - I'd like to be able to
capture both the action performed and the modified row, something like
this (this is in a plpgsql function):
declare
m_action text;
m_new_data record;
begin
merge into my_table t
using (
....
) s
on (t.id = s.id)
when matched then
update .....
when not matched then
insert .....
returning
merge_action(), t.*
into
m_action, m_new_data;
end;
In my case, m_new_data is actually a table row type rather than plain
"record". The row is passed on to another function which calculates the
altered columns and logs the changes.
I've tried it, and it doesn't seem to work; I get an error, "m_new_data
is not a scalar variable", so I'm guessing it's not possible, but it's
worth asking... I know I can list the returned columns individually in
the RETURNING and then use a row constructor to construct the row....
but it'd be handier if I could just derive the row directly from the
MERGE query.
Thanks in advance,
Ray.
--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie
"Ray O'Donnell" <ray@rodonnell.ie> writes:
Can you combine scalar and row types in a RETURNING clause?
I think so.
declare
m_action text;
m_new_data record;
begin
merge into my_table t
using (
....
) s
on (t.id = s.id)
when matched then
update .....
when not matched then
insert .....
returning
merge_action(), t.*
into
m_action, m_new_data;
I think the problem here is that "t.*" gets expanded into a list of
all of t's columns, just as would happen in a SELECT's output list.
Try
returning merge_action(), t
It might also be necessary to declare the target variable
"m_new_data" as being of type my_table rather than generic
"record"; not sure about that.
regards, tom lane
On 03/06/2025 17:53, Tom Lane wrote:
"Ray O'Donnell" <ray@rodonnell.ie> writes:
Can you combine scalar and row types in a RETURNING clause?
I think so.
declare
m_action text;
m_new_data record;
begin
merge into my_table t
using (
....
) s
on (t.id = s.id)
when matched then
update .....
when not matched then
insert .....
returning
merge_action(), t.*
into
m_action, m_new_data;I think the problem here is that "t.*" gets expanded into a list of
all of t's columns, just as would happen in a SELECT's output list.
Tryreturning merge_action(), t
It might also be necessary to declare the target variable
"m_new_data" as being of type my_table rather than generic
"record"; not sure about that.
Thanks a million for the explanation, Tom - that makes sense. I tried
what you suggested, with mixed results:
(i) Running the MERGE as a stand-alone query, with just RETURNING... ,
worked - I got a scalar and a row as expected.
(ii) Running it in a function (actually a DO block), with m_new
correctly declared as the table type, failed with the same error as before.
(iii) Running (ii) but with the order of the items in RETURNING reversed -
... returning t, merge_action() into m_new, m_action
- gave me a different error:
ERROR: record variable cannot be part of multiple-item INTO list
LINE 53: m, merge_action() into m_new, m_action
...which seems to answer my question definitively.
Thanks once more,
Ray.
--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie
On 6/3/25 11:18, Ray O'Donnell wrote:
On 03/06/2025 17:53, Tom Lane wrote:
Thanks a million for the explanation, Tom - that makes sense. I tried
what you suggested, with mixed results:(i) Running the MERGE as a stand-alone query, with just RETURNING... ,
worked - I got a scalar and a row as expected.(ii) Running it in a function (actually a DO block), with m_new
correctly declared as the table type, failed with the same error as before.(iii) Running (ii) but with the order of the items in RETURNING reversed -
... returning t, merge_action() into m_new, m_action
- gave me a different error:
ERROR: record variable cannot be part of multiple-item INTO list
LINE 53: m, merge_action() into m_new, m_action...which seems to answer my question definitively.
This:
... returning t, merge_action() into m_new, m_action
does not match this:
LINE 53: m, merge_action() into m_new, m_action
Is this a copy and paste error or two different invocations of the function?
Thanks once more,
Ray.
--
Adrian Klaver
adrian.klaver@aklaver.com
"Ray O'Donnell" <ray@rodonnell.ie> writes:
(iii) Running (ii) but with the order of the items in RETURNING reversed -
... returning t, merge_action() into m_new, m_action
- gave me a different error:
ERROR: record variable cannot be part of multiple-item INTO list
LINE 53: m, merge_action() into m_new, m_action
...which seems to answer my question definitively.
Ah, after looking at the source code in that area, plpgsql
allows the INTO target to be either a single composite
variable, or one or more non-composite variables; the
argument being that otherwise it's too hard to decide which
RETURNING items match which INTO items.
But I think maybe there is still a solution:
declare
m_into record;
...
returning
merge_action() m, t
into
m_into;
... then fetch m_into.m and m_into.t (the latter will be
a composite field). I didn't try this approach though.
regards, tom lane
On 03/06/2025 20:01, Adrian Klaver wrote:
On 6/3/25 11:18, Ray O'Donnell wrote:
On 03/06/2025 17:53, Tom Lane wrote:
Thanks a million for the explanation, Tom - that makes sense. I tried
what you suggested, with mixed results:(i) Running the MERGE as a stand-alone query, with just RETURNING...
, worked - I got a scalar and a row as expected.(ii) Running it in a function (actually a DO block), with m_new
correctly declared as the table type, failed with the same error as
before.(iii) Running (ii) but with the order of the items in RETURNING
reversed -... returning t, merge_action() into m_new, m_action
- gave me a different error:
ERROR: record variable cannot be part of multiple-item INTO list
LINE 53: m, merge_action() into m_new, m_action...which seems to answer my question definitively.
This:
... returning t, merge_action() into m_new, m_action
does not match this:
LINE 53: m, merge_action() into m_new, m_action
Is this a copy and paste error or two different invocations of the
function?
Whoops, sorry, yes, that's a copy-and-paste error - the target table is
aliased as "m" in my original.
Ray.
--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie
In my case, m_new_data is actually a table row type rather than plain
"record". The row is passed on to another function which calculates the
altered columns and logs the changes.
In my experience, to specify composite-typed receiver as one element
of INTO list, it need to be column of composite variable.
CREATE TABLE t (a int, b int, c int);
CREATE TYPE wrap AS (v t);
DO $DO$
DECLARE
s text;
w wrap;
BEGIN
INSERT INTO t VALUES (1, 2, 3)
RETURNING 'text', t
INTO s, w.v;
RAISE '%, %', s, w.v;
END;
$DO$;