Tag: returning

Waiting for PostgreSQL 17 – Add RETURNING support to MERGE.

On 17th of March 2024, Dean Rasheed committed patch:

Add RETURNING support to MERGE.
 
This allows a RETURNING clause to be appended to a MERGE query, to
return values based on each row inserted, updated, or deleted. As with
plain INSERT, UPDATE, and DELETE commands, the returned values are
based on the new contents of the target table for INSERT and UPDATE
actions, and on its old contents for DELETE actions. Values from the
source relation may also be returned.
 
As with INSERT/UPDATE/DELETE, the output of MERGE ... RETURNING may be
used as the source relation for other operations such as WITH queries
and COPY commands.
 
Additionally, a special function merge_action() is provided, which
returns 'INSERT', 'UPDATE', or 'DELETE', depending on the action
executed for each row. The merge_action() function can be used
anywhere in the RETURNING list, including in arbitrary expressions and
subqueries, but it is an error to use it anywhere outside of a MERGE
query's RETURNING list.
 
Dean Rasheed, reviewed by Isaac Morland, Vik Fearing, Alvaro Herrera,
Gurjeet Singh, Jian He, Jeff Davis, Merlin Moncure, Peter Eisentraut,
and Wolfgang Walther.
 
Discussion: http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add RETURNING support to MERGE.

Waiting for PostgreSQL 14 – Support for OUT parameters in procedures

On 5th of October 2020, Peter Eisentraut committed patch:

Support for OUT parameters in procedures 
 
Unlike for functions, OUT parameters for procedures are part of the
signature.  Therefore, they have to be listed in pg_proc.proargtypes
as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.
 
Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com

Continue reading Waiting for PostgreSQL 14 – Support for OUT parameters in procedures

Waiting for 9.1 – Writable CTE

On 25th of February, Tom Lane committed patch:

Support data-modifying commands (INSERT/UPDATE/DELETE) IN WITH.   
 
This patch implements data-modifying WITH queries according TO the           
semantics that the updates ALL happen WITH the same command counter VALUE,
AND IN an unspecified ORDER.  Therefore one WITH clause can't see the 
effects of another, nor can the outer query see the effects other than
through the RETURNING values.  And attempts to do conflicting updates will
have unpredictable results.  We'll need TO document ALL that.           
 
This commit just fixes the code; documentation updates are waiting ON
author.                                                                 
 
Marko Tiikkaja AND Hitoshi Harada

Continue reading Waiting for 9.1 – Writable CTE

Waiting for 8.4 – sql-wrappable RETURNING

In PostgreSQL 8.2, we got “RETURNING" clause in INSERT/UPDATE/DELETE queries.

Unfortunately it could not be used as source of rows for anything in sql.

INSERT INTO table_backup DELETE FROM TABLE WHERE ... returning *;

Well, it's still not possible, but it is a one step closer, thanks to patch written and committed by Tom Lane on 31st of October:

Allow SQL-LANGUAGE functions TO RETURN the output OF an INSERT/UPDATE/DELETE
RETURNING clause, NOT just a SELECT AS formerly.
 
A side effect OF this patch IS that WHEN a set-returning SQL FUNCTION IS used
IN a FROM clause, performance IS improved because the output IS collected INTO
a tuplestore WITHIN the FUNCTION, rather than USING the less efficient
value-per-CALL mechanism.

Continue reading Waiting for 8.4 – sql-wrappable RETURNING