From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org, px812(at)mailbox(dot)org |
Subject: | Re: BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT |
Date: | 2019-07-13 11:07:44 |
Message-ID: | 87y312nqrf.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Rashid Abzalov | 2019-07-13 13:12:07 | Re: The statement is re-executed (performed twice) on commit if it is declared as "cursor with hold" and the cursor is not closed yet |
Previous Message | PG Bug reporting form | 2019-07-13 08:37:47 | BUG #15906: Insert ops within a branch of "RETURN QUERY WITH" are not seen by a subsequent SELECT |