Class rick_db.sql.With

A wrapper for ordinary or recursive Common Table Expressions (CTE).

With.__init__(dialect: SqlDialect = None)

Initialize the CTE wrapper object, using a database dialect. If no dialect is provided, a default dialect will be used. Check SqlDialect for more details.

With.recursive(status=True)

Creates a recursive CTE if status is True, or an ordinary CTE if status is False

Example:

union = Select().union([
    Literal("VALUES(1)"),
    Select().from_("t", cols=[Literal("n+1")]).where("n", "<", 100)
], Sql.SQL_UNION_ALL)

# assemble a recursive CTE
with_qry = (
    With()
    .clause("t", union)
    .query(Select().from_("t", cols={Literal("SUM(n)"): "total"}))
    .recursive()
)

sql, values = with_qry.assemble()
# sql: WITH RECURSIVE "t"("n") AS (VALUES(1) UNION SELECT n+1 FROM "t" WHERE ("n" < %s)) SELECT SUM(n) AS "total" FROM "t"
# values: [100]

With.**clause(name: str, with_query: Union[SqlStatement, Literal], columns: list = None, materialized: bool =

True)**

Adds a CTE expression with the format WITH name(columns) AS (with_query); The columns parameter is optional. If materialized is False, NOT MATERIALIZED is added: WITH name AS NOT MATERIALIZED (...). This hints the query planner to re-evaluate the CTE each time it is referenced, rather than computing it once and caching the result. This is a PostgreSQL 12+ feature and may not be supported by all databases.

With.query(query: SqlStatement)

Specifies the final CTE query to be applied on the expressions.

With.assemble()

Generates a tuple with the generated SQL string and list of values.