Re: Combining scalar and row types in RETURNING

On 03/06/2025 17:53, Tom Lane wrote:
> "Ray O'Donnell" <ray(at)rodonnell(dot)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(at)rodonnell(dot)ie