Message-ID: <01020197370480a4-f9b53911-e5a8-4bdb-81e5-de41d5db4d8c-000000@eu-west-1.amazonses.com>
Date: Tue, 3 Jun 2025 18:18:51 +0000
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Combining scalar and row types in RETURNING
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 'PostgreSQL' <pgsql-general@postgresql.org>
References: <01020197369aac89-01a7b7b4-b775-471e-ac8e-de8b28d87008-000000@eu-west-1.amazonses.com>
 <858949.1748969638@sss.pgh.pa.us>
Content-Language: en-GB, de-DE
From: Ray O'Donnell <ray@rodonnell.ie>
In-Reply-To: <858949.1748969638@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Feedback-ID: ::1.eu-west-1.Bw1eu/7Uepg3xcsiFBL71JJRCh2A2yTFrgBhhiulXmU=:AmazonSES
Archived-At: <https://www.postgresql.org/message-id/01020197370480a4-f9b53911-e5a8-4bdb-81e5-de41d5db4d8c-000000%40eu-west-1.amazonses.com>
Precedence: bulk

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