Re: BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT - Mailing list pgsql-bugs

From Andrew Gierth
Subject Re: BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT
Date
Msg-id 87y312nqrf.fsf@news-spur.riddles.org.uk
Whole thread Raw
In response to BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT  (PG Bug reporting form <noreply@postgresql.org>)
List pgsql-bugs
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> CREATE OR REPLACE FUNCTION itest1( aName Text) RETURNS SETOF xtmp AS $$
 PG> BEGIN
 PG>    RETURN QUERY
 PG>      WITH ix AS ( 
 PG>           INSERT INTO xtmp(name) VALUES(aName) RETURNING *
 PG>      ) SELECT * FROM xtmp WHERE name = (SELECT name from ix);
 PG> --    Same result as with 
 PG> --     ) SELECT * FROM xtmp;  
 PG> --     ) SELECT * FROM xtmp WHERE name = aName;
 PG> END
 PG> $$ LANGUAGE plpgsql;

I think the point that you're missing here is that a SELECT (or indeed
any other statement) sees only the effects of _previously started_
modification statements, while the INSERT in the WITH is part of the
_same_ statement as the final SELECT and therefore its effects are not
visible.

This is explicitly documented at:
https://www.postgresql.org/docs/11/queries-with.html#QUERIES-WITH-MODIFYING

If you want to return the inserted value then you must do so by querying
the CTE ("ix" in this example), using UNION ALL if necessary.

-- 
Andrew (irc:RhodiumToad)



pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT
Next
From: Rashid Abzalov
Date:
Subject: Re: The statement is re-executed (performed twice) on commit if it isdeclared as "cursor with hold" and the cursor is not closed yet