Обсуждение: Re: Combining scalar and row types in RETURNING
"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. > 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. 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