public inbox for [email protected]
help / color / mirror / Atom feedRe: Combining scalar and row types in RETURNING
3+ messages / 3 participants
[nested] [flat]
* Re: Combining scalar and row types in RETURNING
@ 2025-06-03 18:18 Ray O'Donnell <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Ray O'Donnell @ 2025-06-03 18:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-general
On 03/06/2025 17:53, Tom Lane wrote:
> "Ray O'Donnell" <[email protected]> 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
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Combining scalar and row types in RETURNING
@ 2025-06-03 19:01 Adrian Klaver <[email protected]>
parent: Ray O'Donnell <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2025-06-03 19:01 UTC (permalink / raw)
To: Ray O'Donnell <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Combining scalar and row types in RETURNING
@ 2025-06-03 19:31 Tom Lane <[email protected]>
parent: Ray O'Donnell <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-06-03 19:31 UTC (permalink / raw)
To: Ray O'Donnell <[email protected]>; +Cc: pgsql-general
"Ray O'Donnell" <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-06-03 19:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-03 18:18 Re: Combining scalar and row types in RETURNING Ray O'Donnell <[email protected]>
2025-06-03 19:01 ` Adrian Klaver <[email protected]>
2025-06-03 19:31 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox