Squeal.PostgreSQL.Query.With

with statements

With

class With statement where

with provides a way to write auxiliary statements for use in a larger query. These statements, referred to as CommonTableExpressions, can be thought of as defining temporary tables that exist just for one query.

with can be used for a Query. Multiple CommonTableExpressions can be chained together with the Path constructor :>>, and each CommonTableExpression is constructed via overloaded as.

>>> type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
let
  qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
  qry = with (
    select Star (from (table #tab)) `as` #cte1 :>>
    select Star (from (common #cte1)) `as` #cte2
    ) (select Star (from (common #cte2)))
in printSQL qry
:}
WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"

You can use data-modifying statements in with. This allows you to perform several different operations in the same query. An example is:

>>> type ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]
>>> type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]
>>> :{
let
  manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[]
  manp = with
    (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del)
    (insertInto_ #products_deleted (Subquery (select Star (from (common #del)))))
in printSQL manp
:}
WITH "del" AS (DELETE FROM "products" AS "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" AS "products_deleted" SELECT * FROM "del" AS "del"

Methods

with

Arguments

:: Path (CommonTableExpression statement db params) with0 with1

common table expressions

-> statement with1 db params row

larger query

-> statement with0 db params row

Instances

With Manipulation

Defined in Squeal.PostgreSQL.Manipulation

Methods

with :: forall (db :: SchemasType) (params :: [NullType]) (with0 :: FromType) (with1 :: FromType) (row :: RowType). Path (CommonTableExpression Manipulation db params) with0 with1 -> Manipulation with1 db params row -> Manipulation with0 db params row

With (Query lat)

Defined in Squeal.PostgreSQL.Query.With

Methods

with :: forall (db :: SchemasType) (params :: [NullType]) (with0 :: FromType) (with1 :: FromType) (row :: RowType). Path (CommonTableExpression (Query lat) db params) with0 with1 -> Query lat with1 db params row -> Query lat with0 db params row

data CommonTableExpression statement (db :: SchemasType) (params :: [NullType]) (with0 :: FromType) (with1 :: FromType) where

A CommonTableExpression is an auxiliary statement in a with clause.

Constructors

CommonTableExpression

Fields

• :: Aliased (statement with db params) (cte ::: common)

  aliased statement

• -> Materialization

  materialization of the CTE output

• -> CommonTableExpression statement db params with ((cte ::: common) ': with)

Instances

(KnownSymbol cte, with1 ~ ((cte ::: common) ': with)) => Aliasable cte (statement with db params common) (Path (CommonTableExpression statement db params) with with1)

Defined in Squeal.PostgreSQL.Query.With

Methods

as :: statement with db params common -> Alias cte -> Path (CommonTableExpression statement db params) with with1

(KnownSymbol cte, with1 ~ ((cte ::: common) ': with)) => Aliasable cte (statement with db params common) (CommonTableExpression statement db params with with1)

Defined in Squeal.PostgreSQL.Query.With

Methods

as :: statement with db params common -> Alias cte -> CommonTableExpression statement db params with with1

(forall (c :: FromType) (s :: SchemasType) (p :: [NullType]) (r :: RowType). RenderSQL (statement c s p r)) => RenderSQL (CommonTableExpression statement db params with0 with1)

Defined in Squeal.PostgreSQL.Query.With

Methods

renderSQL :: CommonTableExpression statement db params with0 with1 -> ByteString

withRecursive

Arguments

:: Aliased (Query lat (recursive ': with) db params) recursive

recursive query

-> Query lat (recursive ': with) db params row

larger query

-> Query lat with db params row

A withRecursive Query can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:

>>> import Data.Monoid (Sum (..))
>>> import Data.Int (Int64)
>>> :{
  let
    sum100 :: Statement db () (Sum Int64)
    sum100 = query $
      withRecursive
        ( values_ ((1 & astype int) `as` #n)
          `unionAll`
          select_ ((#n + 1) `as` #n)
            (from (common #t) & where_ (#n .< 100)) `as` #t )
        ( select_
            (fromNull 0 (sum_ (All #n)) `as` #getSum)
            (from (common #t) & groupBy Nil) )
  in printSQL sum100
:}
WITH RECURSIVE "t" AS ((SELECT * FROM (VALUES (((1 :: int4) :: int))) AS t ("n")) UNION ALL (SELECT ("n" + (1 :: int4)) AS "n" FROM "t" AS "t" WHERE ("n" < (100 :: int4)))) SELECT COALESCE(sum(ALL "n"), (0 :: int8)) AS "getSum" FROM "t" AS "t"

The general form of a recursive WITH query is always a non-recursive term, then union (or unionAll), then a recursive term, where only the recursive term can contain a reference to the query's own output.

data Materialization

Whether the contents of the WITH clause are materialized. If a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels.

Note: Use of Materialized or NotMaterialized requires PostgreSQL version 12 or higher. For earlier versions, use DefaultMaterialization which in those earlier versions of PostgreSQL behaves as Materialized. PostgreSQL 12 both changes the default behavior as well as adds options for customizing the materialization behavior.

Constructors

DefaultMaterialization

By default, folding happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. Note: this is the behavior in PostgreSQL 12+. In PostgreSQL 11 and earlier, all CTEs are materialized.

Materialized

You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query. Requires PostgreSQL 12+.

NotMaterialized

or by specifying NOT MATERIALIZED to force it to be merged into the parent query. Requires PostgreSQL 12+.

Instances

Enum Materialization

Defined in Squeal.PostgreSQL.Query.With

Methods

succ :: Materialization -> Materialization

pred :: Materialization -> Materialization

toEnum :: Int -> Materialization

fromEnum :: Materialization -> Int

enumFrom :: Materialization -> [Materialization]

enumFromThen :: Materialization -> Materialization -> [Materialization]

enumFromTo :: Materialization -> Materialization -> [Materialization]

enumFromThenTo :: Materialization -> Materialization -> Materialization -> [Materialization]

Eq Materialization

Defined in Squeal.PostgreSQL.Query.With

Methods

(==) :: Materialization -> Materialization -> Bool

(/=) :: Materialization -> Materialization -> Bool

Ord Materialization

Defined in Squeal.PostgreSQL.Query.With

Methods

compare :: Materialization -> Materialization -> Ordering

(<) :: Materialization -> Materialization -> Bool

(<=) :: Materialization -> Materialization -> Bool

(>) :: Materialization -> Materialization -> Bool

(>=) :: Materialization -> Materialization -> Bool

max :: Materialization -> Materialization -> Materialization

min :: Materialization -> Materialization -> Materialization

Read Materialization

Defined in Squeal.PostgreSQL.Query.With

Methods

readsPrec :: Int -> ReadS Materialization

readList :: ReadS [Materialization]

readPrec :: ReadPrec Materialization

readListPrec :: ReadPrec [Materialization]

Show Materialization

Defined in Squeal.PostgreSQL.Query.With

Methods

showsPrec :: Int -> Materialization -> ShowS

show :: Materialization -> String

showList :: [Materialization] -> ShowS

Generic Materialization

Defined in Squeal.PostgreSQL.Query.With

Associated Types

type Rep Materialization :: Type -> Type

Methods

from :: Materialization -> Rep Materialization x

to :: Rep Materialization x -> Materialization

Generic Materialization

Defined in Squeal.PostgreSQL.Query.With

Associated Types

type Code Materialization :: [[Type]]

Methods

from :: Materialization -> Rep Materialization

to :: Rep Materialization -> Materialization

HasDatatypeInfo Materialization

Defined in Squeal.PostgreSQL.Query.With

Associated Types

type DatatypeInfoOf Materialization :: DatatypeInfo

Methods

datatypeInfo :: proxy Materialization -> DatatypeInfo (Code Materialization)

RenderSQL Materialization

Defined in Squeal.PostgreSQL.Query.With

Methods

renderSQL :: Materialization -> ByteString

type Rep Materialization

Defined in Squeal.PostgreSQL.Query.With

type Rep Materialization = D1 ('MetaData "Materialization" "Squeal.PostgreSQL.Query.With" "squeal-postgresql-0.8.0.0-HHFIvalMWy63oPSAK2xG8g" 'False) (C1 ('MetaCons "DefaultMaterialization" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "Materialized" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "NotMaterialized" 'PrefixI 'False) (U1 :: Type -> Type)))

type Code Materialization

Defined in Squeal.PostgreSQL.Query.With

type Code Materialization = GCode Materialization

type DatatypeInfoOf Materialization

Defined in Squeal.PostgreSQL.Query.With

type DatatypeInfoOf Materialization = GDatatypeInfoOf Materialization

materialized

Arguments

:: Aliased (statement with db params) (cte ::: common)

CTE

-> CommonTableExpression statement db params with ((cte ::: common) ': with)

Force separate calculation of the WITH query.

>>> type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
let
  qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
  qry = with (
    materialized (select Star (from (table #tab)) `as` #cte1) :>>
    select Star (from (common #cte1)) `as` #cte2
    ) (select Star (from (common #cte2)))
in printSQL qry
:}
WITH "cte1" AS MATERIALIZED (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"

Note: if the last CTE has materialized or notMaterialized you must add `:>> Done`.

Requires PostgreSQL 12 or higher.

notMaterialized

Arguments

:: Aliased (statement with db params) (cte ::: common)

CTE

-> CommonTableExpression statement db params with ((cte ::: common) ': with)

Force the WITH query to be merged into the parent query.

>>> type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
let
  qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
  qry = with (
    select Star (from (table #tab)) `as` #cte1 :>>
    notMaterialized (select Star (from (common #cte1)) `as` #cte2) :>>
    Done
    ) (select Star (from (common #cte2)))
in printSQL qry
:}
WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS NOT MATERIALIZED (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"

Note: if the last CTE has materialized or notMaterialized you must add `:>> Done` to finish the Path.

Requires PostgreSQL 12 or higher.