The postgres docs say:
The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This … means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.
I'm looking for a way to work around this effect in a situation like this:
create table t1(id serial); create table t2(id integer); create function f1() returns integer language sql as $$ insert into t1 default values returning id; -- $$; create function f2() returns integer language sql as $$ with w as (insert into t1 default values returning id) insert into t2(id) select id from w join t1 using(id) returning id; -- $$;
select f2() /* | F2 | ---------- | (null) | */ select * from t1 /* | ID | ------ | 1 | */ select * from t2 /* | ID | ------ */
I'd like the
f2 to see the data in
t1 and so end up with 1 row in