Independent data-modifying CTEs in Postgres

[SF-300]

Hello!

First let me thank the community for such an amazing piece of software! It's really super cool to have such open-source things floating around)

I have a question regarding CTEs handling in Postgres dialect. According to Postgres' documentation, data-modifying CTEs are guaranteed to be executed even if they are not referenced in the final statement at all:

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

However I'm having hard time implementing this using query DSL from sqlalchemy (and it seems that I'm not alone in this). Basically I'm looking for something which would translate into query like this:

WITH inserted as (
    INSERT INTO some_table (some_id, some_col) VALUES (1, "test")
)
SELECT some_id FROM some_table;

Am I missing something obvious? Or is it really impossible to express this via sqlalchemy? As for now I have to resort to final dummy SELECT statement which somehow references all data-modifying CTEs and it's... not pretty.

Any help would be greatly appreciated! Thanks in advance!

[zzzeek]

this use case has never been presented here and unfortunately the quality of Stack Overflow is quite low these days and we no longer recommend it for support.

CTE objects are basically included in WITH whenever they are referred towards by the select() in any way, and we would almost think here that select_from(cte) would handle it but you don't want the CTE referred towards in the FROM clause either. Adding the CTE to select_from() while also setting flags on the cte() to "hide" it from the FROM list has the effect of it not being considered for the WITH clause.

so right now the CTE can get pulled in pretty much by setting a hint for it, but I would not consider this to be first class API:

from sqlalchemy import table, select, column

t = table('t', column('c1'), column('c2'))

ins = t.insert().returning(t.c.c1, t.c.c2).cte()

stmt = select(t).with_hint(ins, "text")

print(stmt)

[zzzeek]

we will include this feature in 1.4.21, see #6752