inserted row in CTE not available to join on in postgreSQL?

Question

i have a table foo

                            Table "public.foo"
      Column       |            Type             |              Modifiers              
-------------------+-----------------------------+-------------------------------------
 foo_id            | uuid                        | not null default uuid_generate_v1()
 col1              | uuid                        | 
 col2              | uuid                        | 
 col3              | uuid                        | not null

I have a view goo_view, which selects primarily from foo, but also joins another table

SELECT * from foo LEFT JOIN foo_helper USING (foo_id);

I have the following CTE query in which i am attempting to insert into table foo whilst returning the appropriate goo_view information

WITH ins AS (
    INSERT INTO foo (col1,col2, col3) VALUES (111,222,333) RETURNING foo_id
)                              
SELECT v.foo_id, v.col1, v.col2, v.fk1
FROM goo_view v 
JOIN ins 
USING (foo_id)   

however the result is empty.

If i run the insert and select separately it works, is there something about the timing that causes this to fail?

what is the work around?

Answer 1

Why are you using an insert in a CTE?

WITH ins AS (
    select 111 as col1, 222 as col2, 333 as col3
) 
. . .

Does your syntax really work? I haven't seen it before. Wow, I just learned something. This appears to be Postgres syntax.

My best guess is that the returning clause you are using is returning one foo_id . . . the one you just inserted. This foo_id is probably not present in goo_view, so there is no match and an empty set.

And, what you are trying to do is explicity not allowed in the documentation:

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 alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query . . .

So, you are not going to see the new row in the view. To fix this, you would have to explicitly reference the CTE in the from clause, using the results returned by returning.