Thread: 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