On 03/06/2025 17:53, Tom Lane wrote:
"Ray O'Donnell" <r...@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
r...@rodonnell.ie
