Search code examples
postgresqlreturn

PostgreSQL: Do I need to return anything in a CTE?

I once heard that a CTE (WITH statement) needs to have returning something. Otherwise it might happen that it will not be executed.

I have following CTE simplified structure:

WITH
selectA AS (
    SELECT ...
),
insertA AS (
    INSERT INTO ...
    -- selecting something from selectA
    RETURNING ...
),
insertB AS (
    INSERT INTO ... 
    -- selecting something from selectA and insertA
    -- HERE no RETURNING!
),
selectB AS (
    SELECT ...
    -- selecting something from selectA and insertA
),
insertForms AS (
    INSERT INTO ...
    -- selecting something from selectB
    -- HERE no RETURNING!
),
insertFormData AS (
    INSERT INTO ...
    -- selecting something from selectB
    -- HERE no RETURNING!
)
SELECT ...
    -- selecting something from insertA and selectB

It works but I am wondering if I hit any corner case causing that all CTE parts get executed. In the PostgreSQL documentation I did not find a clear hint if a returning part is necessary within a CTE.

Does anyone have a good thought for that? Thanks a lot!

Solution

  • "but I am wondering if I hit any corner case" - that behaviour is fully documented in the manual

    If RETURNING is omitted, the statement is still executed, but it produces no output so it cannot be referenced as a table by the primary query