Squeal.PostgreSQL.Query

Squeal queries.

Queries

type family Query_ (schemas :: SchemasType) (parameters :: Type) (row :: Type) where ...

The top level Query_ type is parameterized by a schemas SchemasType, against which the query is type-checked, an input parameters Haskell Type, and an ouput row Haskell Type.

A top-level query can be run using runQueryParams, or if parameters = () using runQuery.

Generally, parameters will be a Haskell tuple or record whose entries may be referenced using positional parameters and row will be a Haskell record, whose entries will be targeted using overloaded labels.

Let's see some examples of queries.

>>> :set -XDeriveAnyClass -XDerivingStrategies
>>> :{
data Row a b = Row { col1 :: a, col2 :: b }
  deriving stock (GHC.Generic)
  deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)
:}

simple query:

>>> type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = select Star (from (table #tab))
in printSQL query
:}
SELECT * FROM "tab" AS "tab"

restricted query:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query =
    select_ ((#col1 + #col2) `as` #col1 :* #col1 `as` #col2)
      ( from (table #tab)
        & where_ (#col1 .> #col2)
        & where_ (#col2 .> 0) )
in printSQL query
:}
SELECT ("col1" + "col2") AS "col1", "col1" AS "col2" FROM "tab" AS "tab" WHERE (("col1" > "col2") AND ("col2" > 0))

subquery:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = select Star (from (subquery (select Star (from (table #tab)) `as` #sub)))
in printSQL query
:}
SELECT * FROM (SELECT * FROM "tab" AS "tab") AS "sub"

limits and offsets:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = select Star (from (table #tab) & limit 100 & offset 2 & limit 50 & offset 2)
in printSQL query
:}
SELECT * FROM "tab" AS "tab" LIMIT 50 OFFSET 4

parameterized query:

>>> :{
let
  query :: Query_ (Public Schema) (Only Int32) (Row Int32 Int32)
  query = select Star (from (table #tab) & where_ (#col1 .> param @1))
in printSQL query
:}
SELECT * FROM "tab" AS "tab" WHERE ("col1" > ($1 :: int4))

aggregation query:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int64 Int32)
  query =
    select_ ((fromNull 0 (sum_ (All #col2))) `as` #col1 :* #col1 `as` #col2)
    ( from (table (#tab `as` #table1))
      & groupBy #col1
      & having (sum_ (Distinct #col2) .> 1) )
in printSQL query
:}
SELECT COALESCE(sum(ALL "col2"), 0) AS "col1", "col1" AS "col2" FROM "tab" AS "table1" GROUP BY "col1" HAVING (sum(DISTINCT "col2") > 1)

sorted query:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = select Star (from (table #tab) & orderBy [#col1 & Asc])
in printSQL query
:}
SELECT * FROM "tab" AS "tab" ORDER BY "col1" ASC

joins:

>>> :{
type OrdersColumns =
  '[ "id"         ::: 'NoDef :=> 'NotNull 'PGint4
   , "price"       ::: 'NoDef :=> 'NotNull 'PGfloat4
   , "customer_id" ::: 'NoDef :=> 'NotNull 'PGint4
   , "shipper_id"  ::: 'NoDef :=> 'NotNull 'PGint4  ]
:}

>>> :{
type OrdersConstraints =
  '["pk_orders" ::: PrimaryKey '["id"]
  ,"fk_customers" ::: ForeignKey '["customer_id"] "customers" '["id"]
  ,"fk_shippers" ::: ForeignKey '["shipper_id"] "shippers" '["id"] ]
:}

>>> type NamesColumns = '["id" ::: 'NoDef :=> 'NotNull 'PGint4, "name" ::: 'NoDef :=> 'NotNull 'PGtext]
>>> type CustomersConstraints = '["pk_customers" ::: PrimaryKey '["id"]]
>>> type ShippersConstraints = '["pk_shippers" ::: PrimaryKey '["id"]]
>>> :{
type OrdersSchema =
  '[ "orders"   ::: 'Table (OrdersConstraints :=> OrdersColumns)
   , "customers" ::: 'Table (CustomersConstraints :=> NamesColumns)
   , "shippers" ::: 'Table (ShippersConstraints :=> NamesColumns) ]
:}

>>> :{
data Order = Order
  { price :: Float
  , customerName :: Text
  , shipperName :: Text
  } deriving GHC.Generic
instance SOP.Generic Order
instance SOP.HasDatatypeInfo Order
:}

>>> :{
let
  query :: Query_ (Public OrdersSchema) () Order
  query = select_
    ( #o ! #price `as` #price :*
      #c ! #name `as` #customerName :*
      #s ! #name `as` #shipperName )
    ( from (table (#orders `as` #o)
      & innerJoin (table (#customers `as` #c))
        (#o ! #customer_id .== #c ! #id)
      & innerJoin (table (#shippers `as` #s))
        (#o ! #shipper_id .== #s ! #id)) )
in printSQL query
:}
SELECT "o"."price" AS "price", "c"."name" AS "customerName", "s"."name" AS "shipperName" FROM "orders" AS "o" INNER JOIN "customers" AS "c" ON ("o"."customer_id" = "c"."id") INNER JOIN "shippers" AS "s" ON ("o"."shipper_id" = "s"."id")

self-join:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = select
    (#t1 & DotStar)
    (from (table (#tab `as` #t1) & crossJoin (table (#tab `as` #t2))))
in printSQL query
:}
SELECT "t1".* FROM "tab" AS "t1" CROSS JOIN "tab" AS "t2"

value queries:

>>> :{
let
  query :: Query_ schemas () (Row String Bool)
  query = values
    ("true" `as` #col1 :* true `as` #col2)
    ["false" `as` #col1 :* false `as` #col2]
in printSQL query
:}
SELECT * FROM (VALUES (E'true', TRUE), (E'false', FALSE)) AS t ("col1", "col2")

set operations:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = select Star (from (table #tab)) `unionAll` select Star (from (table #tab))
in printSQL query
:}
(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab")

with queries:

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int32)
  query = with (
    select Star (from (table #tab)) `as` #cte1 :>>
    select Star (from (common #cte1)) `as` #cte2
    ) (select Star (from (common #cte2)))
in printSQL query
:}
WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"

window function queries

>>> :{
let
  query :: Query_ (Public Schema) () (Row Int32 Int64)
  query = select
    (#col1 & Also (rank `as` #col2 `Over` (partitionBy #col1 & orderBy [#col2 & Asc])))
    (from (table #tab))
in printSQL query
:}
SELECT "col1" AS "col1", rank() OVER (PARTITION BY "col1" ORDER BY "col2" ASC) AS "col2" FROM "tab" AS "tab"

correlated subqueries

>>> :{
let
  query :: Query_ (Public Schema) () (Only Int32)
  query =
    select (#col1 `as` #fromOnly) (from (table (#tab `as` #t1))
    & where_ (exists (
      select Star (from (table (#tab `as` #t2))
      & where_ (#t2 ! #col2 .== #t1 ! #col1)))))
in printSQL query
:}
SELECT "col1" AS "fromOnly" FROM "tab" AS "t1" WHERE EXISTS (SELECT * FROM "tab" AS "t2" WHERE ("t2"."col2" = "t1"."col1"))

Equations

Query_ schemas params row = Query '[] '[] schemas (TuplePG params) (RowPG row)

newtype Query (outer :: FromType) (commons :: FromType) (schemas :: SchemasType) (params :: [NullityType]) (row :: RowType)

The process of retrieving or the command to retrieve data from a database is called a Query.

The general Query type is parameterized by

• outer :: FromType - outer scope for a correlated subquery,
• commons :: FromType - scope for all common table expressions,
• schemas :: SchemasType - scope for all tables and views,
• params :: [NullityType] - scope for all parameters,
• row :: RowType - return type of the Query.

Constructors

UnsafeQuery

Fields

• renderQuery :: ByteString

Instances

With (Query outer)

Defined in Squeal.PostgreSQL.Query

Methods

with :: AlignedList (CommonTableExpression (Query outer) schemas params) commons0 commons1 -> Query outer commons1 schemas params row -> Query outer commons0 schemas params row

Eq (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

Methods

(==) :: Query outer commons schemas params row -> Query outer commons schemas params row -> Bool

(/=) :: Query outer commons schemas params row -> Query outer commons schemas params row -> Bool

Ord (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

Methods

compare :: Query outer commons schemas params row -> Query outer commons schemas params row -> Ordering

(<) :: Query outer commons schemas params row -> Query outer commons schemas params row -> Bool

(<=) :: Query outer commons schemas params row -> Query outer commons schemas params row -> Bool

(>) :: Query outer commons schemas params row -> Query outer commons schemas params row -> Bool

(>=) :: Query outer commons schemas params row -> Query outer commons schemas params row -> Bool

max :: Query outer commons schemas params row -> Query outer commons schemas params row -> Query outer commons schemas params row

min :: Query outer commons schemas params row -> Query outer commons schemas params row -> Query outer commons schemas params row

Show (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

Methods

showsPrec :: Int -> Query outer commons schemas params row -> ShowS

show :: Query outer commons schemas params row -> String

showList :: [Query outer commons schemas params row] -> ShowS

Generic (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

Associated Types

type Rep (Query outer commons schemas params row) :: Type -> Type

Methods

from :: Query outer commons schemas params row -> Rep (Query outer commons schemas params row) x

to :: Rep (Query outer commons schemas params row) x -> Query outer commons schemas params row

NFData (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

Methods

rnf :: Query outer commons schemas params row -> ()

RenderSQL (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: Query outer commons schemas params row -> ByteString

type Rep (Query outer commons schemas params row)

Defined in Squeal.PostgreSQL.Query

type Rep (Query outer commons schemas params row) = D1 (MetaData "Query" "Squeal.PostgreSQL.Query" "squeal-postgresql-0.5.1.0-1zXd1HFs4I2ArqqDdrCP04" True) (C1 (MetaCons "UnsafeQuery" PrefixI True) (S1 (MetaSel (Just "renderQuery") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 ByteString)))

Select

select

Arguments

:: (SListI row, row ~ (x ': xs))

=> Selection outer commons grp schemas params from row

selection

-> TableExpression outer commons grp schemas params from

intermediate virtual table

-> Query outer commons schemas params row

the TableExpression in the select command constructs an intermediate virtual table by possibly combining tables, views, eliminating rows, grouping, etc. This table is finally passed on to processing by the select list. The Selection determines which columns of the intermediate table are actually output.

select_

Arguments

:: (SListI row, row ~ (x ': xs))

=> NP (Aliased (Expression outer commons grp schemas params from)) row

select list

-> TableExpression outer commons grp schemas params from

intermediate virtual table

-> Query outer commons schemas params row

Like select but takes an NP list of Expressions instead of a general Selection.

selectDistinct

Arguments

:: (SListI columns, columns ~ (col ': cols))

=> Selection outer commons Ungrouped schemas params from columns

selection

-> TableExpression outer commons Ungrouped schemas params from

intermediate virtual table

-> Query outer commons schemas params columns

After the select list has been processed, the result table can be subject to the elimination of duplicate rows using selectDistinct.

selectDistinct_

Arguments

:: (SListI columns, columns ~ (col ': cols))

=> NP (Aliased (Expression outer commons Ungrouped schemas params from)) columns

select list

-> TableExpression outer commons Ungrouped schemas params from

intermediate virtual table

-> Query outer commons schemas params columns

Like selectDistinct but takes an NP list of Expressions instead of a general Selection.

data Selection outer commons grp schemas params from row where

The simplest kinds of Selection are Star and DotStar which emits all columns that a TableExpression produces. A select List is a list of Expressions. A Selection could be a list of WindowFunctions Over WindowDefinition. Additional Selections can be selected with Also.

Constructors

Star

Fields

• :: HasUnique tab from row
• => Selection outer commons Ungrouped schemas params from row

  HasUnique table in the FromClause

DotStar

Fields

• :: Has tab from row
• => Alias tab

  Has table with Alias

• -> Selection outer commons Ungrouped schemas params from row

List

Fields

• :: SListI row
• => NP (Aliased (Expression outer commons grp schemas params from)) row

  NP list of Aliased Expressions

• -> Selection outer commons grp schemas params from row

Over

Fields

• :: SListI row
• => NP (Aliased (WindowFunction outer commons grp schemas params from)) row

  NP list of Aliased WindowFunctions

• -> WindowDefinition outer commons grp schemas params from
• -> Selection outer commons grp schemas params from row

Also

Fields

• :: Selection outer commons grp schemas params from right

  Additional Selection

• -> Selection outer commons grp schemas params from left
• -> Selection outer commons grp schemas params from (Join left right)

Instances

(Has tab (Join outer from) row0, Has col row0 ty, row1 ~ ((col ::: ty) ': ([] :: [(Symbol, NullityType)])), GroupedBy tab col bys) => IsQualified tab col (Selection outer commons (Grouped bys) schemas params from row1)

Defined in Squeal.PostgreSQL.Query

Methods

(!) :: Alias tab -> Alias col -> Selection outer commons (Grouped bys) schemas params from row1

(Has tab (Join outer from) row0, Has col row0 ty, row1 ~ ((col ::: ty) ': ([] :: [(Symbol, NullityType)]))) => IsQualified tab col (Selection outer commons Ungrouped schemas params from row1)

Defined in Squeal.PostgreSQL.Query

Methods

(!) :: Alias tab -> Alias col -> Selection outer commons Ungrouped schemas params from row1

(HasUnique tab (Join outer from) row0, Has col row0 ty, row1 ~ ((col ::: ty) ': ([] :: [(Symbol, NullityType)])), GroupedBy tab col bys) => IsLabel col (Selection outer commons (Grouped bys) schemas params from row1)

Defined in Squeal.PostgreSQL.Query

Methods

fromLabel :: Selection outer commons (Grouped bys) schemas params from row1

(HasUnique tab (Join outer from) row0, Has col row0 ty, row1 ~ ((col ::: ty) ': ([] :: [(Symbol, NullityType)]))) => IsLabel col (Selection outer commons Ungrouped schemas params from row1)

Defined in Squeal.PostgreSQL.Query

Methods

fromLabel :: Selection outer commons Ungrouped schemas params from row1

(KnownSymbol col, row ~ ((col ::: ty) ': ([] :: [(Symbol, NullityType)]))) => Aliasable col (Expression outer commons grp schemas params from ty) (Selection outer commons grp schemas params from row)

Defined in Squeal.PostgreSQL.Query

Methods

as :: Expression outer commons grp schemas params from ty -> Alias col -> Selection outer commons grp schemas params from row

Additional (Selection outer commons grp schemas params from :: RowType -> Type)

Defined in Squeal.PostgreSQL.Query

Methods

also :: Selection outer commons grp schemas params from ys -> Selection outer commons grp schemas params from xs -> Selection outer commons grp schemas params from (Join xs ys)

IsString (Selection outer commons grp schemas params from (("fromOnly" ::: NotNull PGtext) ': ([] :: [(Symbol, NullityType)])))

Defined in Squeal.PostgreSQL.Query

Methods

fromString :: String -> Selection outer commons grp schemas params from (("fromOnly" ::: NotNull PGtext) ': [])

RenderSQL (Selection outer commons grp schemas params from row)

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: Selection outer commons grp schemas params from row -> ByteString

Values

values

Arguments

:: SListI cols

=> NP (Aliased (Expression outer commons Ungrouped schemas params '[])) cols

-> [NP (Aliased (Expression outer commons Ungrouped schemas params '[])) cols]

When more than one row is specified, all the rows must must have the same number of elements

-> Query outer commons schemas params cols

values computes a row value or set of row values specified by value expressions. It is most commonly used to generate a “constant table” within a larger command, but it can be used on its own.

>>> type Row = '["a" ::: 'NotNull 'PGint4, "b" ::: 'NotNull 'PGtext]
>>> let query = values (1 `as` #a :* "one" `as` #b) [] :: Query outer commons schemas '[] Row
>>> printSQL query
SELECT * FROM (VALUES (1, E'one')) AS t ("a", "b")

values_

Arguments

:: SListI cols

=> NP (Aliased (Expression outer commons Ungrouped schemas params '[])) cols

one row of values

-> Query outer commons schemas params cols

values_ computes a row value or set of row values specified by value expressions.

Set Operations

union :: Query outer commons schemas params columns -> Query outer commons schemas params columns -> Query outer commons schemas params columns

The results of two queries can be combined using the set operation union. Duplicate rows are eliminated.

unionAll :: Query outer commons schemas params columns -> Query outer commons schemas params columns -> Query outer commons schemas params columns

The results of two queries can be combined using the set operation unionAll, the disjoint union. Duplicate rows are retained.

intersect :: Query outer commons schemas params columns -> Query outer commons schemas params columns -> Query outer commons schemas params columns

The results of two queries can be combined using the set operation intersect, the intersection. Duplicate rows are eliminated.

intersectAll :: Query outer commons schemas params columns -> Query outer commons schemas params columns -> Query outer commons schemas params columns

The results of two queries can be combined using the set operation intersectAll, the intersection. Duplicate rows are retained.

except :: Query outer commons schemas params columns -> Query outer commons schemas params columns -> Query outer commons schemas params columns

The results of two queries can be combined using the set operation except, the set difference. Duplicate rows are eliminated.

exceptAll :: Query outer commons schemas params columns -> Query outer commons schemas params columns -> Query outer commons schemas params columns

The results of two queries can be combined using the set operation exceptAll, the set difference. Duplicate rows are retained.

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.

Methods

with

Arguments

:: AlignedList (CommonTableExpression statement schemas params) commons0 commons1

common table expressions

-> statement commons1 schemas params row

larger query

-> statement commons0 schemas params row

Instances

With Manipulation

Defined in Squeal.PostgreSQL.Manipulation

Methods

with :: AlignedList (CommonTableExpression Manipulation schemas params) commons0 commons1 -> Manipulation commons1 schemas params row -> Manipulation commons0 schemas params row

With (Query outer)

Defined in Squeal.PostgreSQL.Query

Methods

with :: AlignedList (CommonTableExpression (Query outer) schemas params) commons0 commons1 -> Query outer commons1 schemas params row -> Query outer commons0 schemas params row

data CommonTableExpression statement (schemas :: SchemasType) (params :: [NullityType]) (commons0 :: FromType) (commons1 :: FromType) where

A CommonTableExpression is an auxiliary statement in a with clause.

Constructors

CommonTableExpression :: Aliased (statement commons schemas params) (cte ::: common) -> CommonTableExpression statement schemas params commons ((cte ::: common) ': commons)

Instances

(KnownSymbol cte, commons1 ~ ((cte ::: common) ': commons)) => Aliasable cte (statement commons schemas params common) (AlignedList (CommonTableExpression statement schemas params) commons commons1)

Defined in Squeal.PostgreSQL.Query

Methods

as :: statement commons schemas params common -> Alias cte -> AlignedList (CommonTableExpression statement schemas params) commons commons1

(KnownSymbol cte, commons1 ~ ((cte ::: common) ': commons)) => Aliasable cte (statement commons schemas params common) (CommonTableExpression statement schemas params commons commons1)

Defined in Squeal.PostgreSQL.Query

Methods

as :: statement commons schemas params common -> Alias cte -> CommonTableExpression statement schemas params commons commons1

(forall (c :: FromType) (s :: SchemasType) (p :: [NullityType]) (r :: RowType). RenderSQL (statement c s p r)) => RenderSQL (CommonTableExpression statement schemas params commons0 commons1)

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: CommonTableExpression statement schemas params commons0 commons1 -> ByteString

withRecursive :: Aliased (Query outer (recursive ': commons) schemas params) recursive -> Query outer (recursive ': commons) schemas params row -> Query outer commons schemas params row

>>> import Data.Monoid (Sum (..))
>>> import Data.Int (Int64)
>>> :{
  let
    query :: Query_ schema () (Sum Int64)
    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 query
:}
WITH RECURSIVE "t" AS ((SELECT * FROM (VALUES ((1 :: int))) AS t ("n")) UNION ALL (SELECT ("n" + 1) AS "n" FROM "t" AS "t" WHERE ("n" < 100))) SELECT COALESCE(sum(ALL "n"), 0) AS "getSum" FROM "t" AS "t"

Table Expressions

data TableExpression (outer :: FromType) (commons :: FromType) (grp :: Grouping) (schemas :: SchemasType) (params :: [NullityType]) (from :: FromType)

A TableExpression computes a table. The table expression contains a fromClause that is optionally followed by a whereClause, groupByClause, havingClause, orderByClause, limitClause and offsetClauses. Trivial table expressions simply refer to a table on disk, a so-called base table, but more complex expressions can be used to modify or combine base tables in various ways.

Constructors

TableExpression

Fields

• fromClause :: FromClause outer commons schemas params from

  A table reference that can be a table name, or a derived table such as a subquery, a JOIN construct, or complex combinations of these.

• whereClause :: [Condition outer commons Ungrouped schemas params from]

  optional search coditions, combined with .&&. After the processing of the fromClause is done, each row of the derived virtual table is checked against the search condition. If the result of the condition is true, the row is kept in the output table, otherwise it is discarded. The search condition typically references at least one column of the table generated in the fromClause; this is not required, but otherwise the WHERE clause will be fairly useless.

• groupByClause :: GroupByClause grp from

  The groupByClause is used to group together those rows in a table that have the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups.

• havingClause :: HavingClause outer commons grp schemas params from

  If a table has been grouped using groupBy, but only certain groups are of interest, the havingClause can be used, much like a whereClause, to eliminate groups from the result. Expressions in the havingClause can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function).

• orderByClause :: [SortExpression outer commons grp schemas params from]

  The orderByClause is for optional sorting. When more than one SortExpression is specified, the later (right) values are used to sort rows that are equal according to the earlier (left) values.

• limitClause :: [Word64]

  The limitClause is combined with min to give a limit count if nonempty. If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows).

• offsetClause :: [Word64]

  The offsetClause is combined with + to give an offset count if nonempty. The offset count says to skip that many rows before beginning to return rows. The rows are skipped before the limit count is applied.

Instances

OrderBy TableExpression

Defined in Squeal.PostgreSQL.Query

Methods

orderBy :: [SortExpression outer commons grp schemas params from] -> TableExpression outer commons grp schemas params from -> TableExpression outer commons grp schemas params from

Generic (TableExpression outer commons grp schemas params from)

Defined in Squeal.PostgreSQL.Query

Associated Types

type Rep (TableExpression outer commons grp schemas params from) :: Type -> Type

Methods

from :: TableExpression outer commons grp schemas params from -> Rep (TableExpression outer commons grp schemas params from) x

to :: Rep (TableExpression outer commons grp schemas params from) x -> TableExpression outer commons grp schemas params from

RenderSQL (TableExpression outer commons grp schemas params from)

Render a TableExpression

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: TableExpression outer commons grp schemas params from -> ByteString

type Rep (TableExpression outer commons grp schemas params from)

Defined in Squeal.PostgreSQL.Query

type Rep (TableExpression outer commons grp schemas params from) = D1 (MetaData "TableExpression" "Squeal.PostgreSQL.Query" "squeal-postgresql-0.5.1.0-1zXd1HFs4I2ArqqDdrCP04" False) (C1 (MetaCons "TableExpression" PrefixI True) ((S1 (MetaSel (Just "fromClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 (FromClause outer commons schemas params from)) :*: (S1 (MetaSel (Just "whereClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 [Condition outer commons Ungrouped schemas params from]) :*: S1 (MetaSel (Just "groupByClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 (GroupByClause grp from)))) :*: ((S1 (MetaSel (Just "havingClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 (HavingClause outer commons grp schemas params from)) :*: S1 (MetaSel (Just "orderByClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 [SortExpression outer commons grp schemas params from])) :*: (S1 (MetaSel (Just "limitClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 [Word64]) :*: S1 (MetaSel (Just "offsetClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 [Word64])))))

from

Arguments

:: FromClause outer commons schemas params from

table reference

-> TableExpression outer commons Ungrouped schemas params from

A from generates a TableExpression from a table reference that can be a table name, or a derived table such as a subquery, a JOIN construct, or complex combinations of these. A from may be transformed by where_, groupBy, having, orderBy, limit and offset, using the & operator to match the left-to-right sequencing of their placement in SQL.

where_

Arguments

:: Condition outer commons Ungrouped schemas params from

filtering condition

-> TableExpression outer commons grp schemas params from

-> TableExpression outer commons grp schemas params from

A where_ is an endomorphism of TableExpressions which adds a search condition to the whereClause.

groupBy

Arguments

:: SListI bys

=> NP (By from) bys

grouped columns

-> TableExpression outer commons Ungrouped schemas params from

-> TableExpression outer commons (Grouped bys) schemas params from

A groupBy is a transformation of TableExpressions which switches its Grouping from Ungrouped to Grouped. Use groupBy Nil to perform a "grand total" aggregation query.

having

Arguments

:: Condition outer commons (Grouped bys) schemas params from

having condition

-> TableExpression outer commons (Grouped bys) schemas params from

-> TableExpression outer commons (Grouped bys) schemas params from

A having is an endomorphism of TableExpressions which adds a search condition to the havingClause.

limit

Arguments

:: Word64

limit parameter

-> TableExpression outer commons grp schemas params from

-> TableExpression outer commons grp schemas params from

A limit is an endomorphism of TableExpressions which adds to the limitClause.

offset

Arguments

:: Word64

offset parameter

-> TableExpression outer commons grp schemas params from

-> TableExpression outer commons grp schemas params from

An offset is an endomorphism of TableExpressions which adds to the offsetClause.

From Clauses

newtype FromClause outer commons schemas params from

A FromClause can be a table name, or a derived table such as a subquery, a JOIN construct, or complex combinations of these.

Constructors

UnsafeFromClause

Fields

• renderFromClause :: ByteString

Instances

Additional (FromClause outer commons schemas params :: [a] -> Type)

Defined in Squeal.PostgreSQL.Query

Methods

also :: FromClause outer commons schemas params ys -> FromClause outer commons schemas params xs -> FromClause outer commons schemas params (Join xs ys)

Eq (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

(==) :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Bool

(/=) :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Bool

Ord (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

compare :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Ordering

(<) :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Bool

(<=) :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Bool

(>) :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Bool

(>=) :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> Bool

max :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> FromClause outer commons schemas params from

min :: FromClause outer commons schemas params from -> FromClause outer commons schemas params from -> FromClause outer commons schemas params from

Show (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

showsPrec :: Int -> FromClause outer commons schemas params from -> ShowS

show :: FromClause outer commons schemas params from -> String

showList :: [FromClause outer commons schemas params from] -> ShowS

Generic (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

Associated Types

type Rep (FromClause outer commons schemas params from) :: Type -> Type

Methods

from :: FromClause outer commons schemas params from -> Rep (FromClause outer commons schemas params from) x

to :: Rep (FromClause outer commons schemas params from) x -> FromClause outer commons schemas params from

NFData (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

rnf :: FromClause outer commons schemas params from -> ()

RenderSQL (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: FromClause outer commons schemas params from -> ByteString

type Rep (FromClause outer commons schemas params from)

Defined in Squeal.PostgreSQL.Query

type Rep (FromClause outer commons schemas params from) = D1 (MetaData "FromClause" "Squeal.PostgreSQL.Query" "squeal-postgresql-0.5.1.0-1zXd1HFs4I2ArqqDdrCP04" True) (C1 (MetaCons "UnsafeFromClause" PrefixI True) (S1 (MetaSel (Just "renderFromClause") NoSourceUnpackedness NoSourceStrictness DecidedLazy) (Rec0 ByteString)))

table :: (Has sch schemas schema, Has tab schema (Table table)) => Aliased (QualifiedAlias sch) (alias ::: tab) -> FromClause outer commons schemas params '[alias ::: TableToRow table]

A real table is a table from the database.

subquery :: Aliased (Query outer commons schemas params) query -> FromClause outer commons schemas params '[query]

subquery derives a table from a Query.

view :: (Has sch schemas schema, Has vw schema (View view)) => Aliased (QualifiedAlias sch) (alias ::: vw) -> FromClause outer commons schemas params '[alias ::: view]

view derives a table from a View.

common :: Has cte commons common => Aliased Alias (alias ::: cte) -> FromClause outer commons schemas params '[alias ::: common]

common derives a table from a common table expression.

crossJoin

Arguments

:: FromClause outer commons schemas params right

right

-> FromClause outer commons schemas params left

left

-> FromClause outer commons schemas params (Join left right)

left & crossJoin right. For every possible combination of rows from left and right (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in left followed by all columns in right. If the tables have n and m rows respectively, the joined table will have n * m rows.

innerJoin

Arguments

:: FromClause outer commons schemas params right

right

-> Condition outer commons Ungrouped schemas params (Join left right)

on condition

-> FromClause outer commons schemas params left

left

-> FromClause outer commons schemas params (Join left right)

left & innerJoin right on. The joined table is filtered by the on condition.

leftOuterJoin

Arguments

:: FromClause outer commons schemas params right

right

-> Condition outer commons Ungrouped schemas params (Join left right)

on condition

-> FromClause outer commons schemas params left

left

-> FromClause outer commons schemas params (Join left (NullifyFrom right))

left & leftOuterJoin right on. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right, a joined row is added with null values in columns of right. Thus, the joined table always has at least one row for each row in left.

rightOuterJoin

Arguments

:: FromClause outer commons schemas params right

right

-> Condition outer commons Ungrouped schemas params (Join left right)

on condition

-> FromClause outer commons schemas params left

left

-> FromClause outer commons schemas params (Join (NullifyFrom left) right)

left & rightOuterJoin right on. First, an inner join is performed. Then, for each row in right that does not satisfy the on condition with any row in left, a joined row is added with null values in columns of left. This is the converse of a left join: the result table will always have a row for each row in right.

fullOuterJoin

Arguments

:: FromClause outer commons schemas params right

right

-> Condition outer commons Ungrouped schemas params (Join left right)

on condition

-> FromClause outer commons schemas params left

left

-> FromClause outer commons schemas params (Join (NullifyFrom left) (NullifyFrom right))

left & fullOuterJoin right on. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right, a joined row is added with null values in columns of right. Also, for each row of right that does not satisfy the join condition with any row in left, a joined row with null values in the columns of left is added.

Grouping

data By (from :: FromType) (by :: (Symbol, Symbol)) where

Bys are used in groupBy to reference a list of columns which are then used to group together those rows in a table that have the same values in all the columns listed. By #col will reference an unambiguous column col; otherwise By2 (#tab ! #col) will reference a table qualified column tab.col.

Constructors

By1 :: (HasUnique table from columns, Has column columns ty) => Alias column -> By from '(table, column)

By2 :: (Has table from columns, Has column columns ty) => Alias table -> Alias column -> By from '(table, column)

Instances

(Has rel rels cols, Has col cols ty, by ~ (,) rel col) => IsQualified rel col (By rels by)

Defined in Squeal.PostgreSQL.Query

Methods

(!) :: Alias rel -> Alias col -> By rels by

(Has rel rels cols, Has col cols ty, bys ~ ((,) rel col ': ([] :: [(Symbol, Symbol)]))) => IsQualified rel col (NP (By rels) bys)

Defined in Squeal.PostgreSQL.Query

Methods

(!) :: Alias rel -> Alias col -> NP (By rels) bys

(HasUnique rel rels cols, Has col cols ty, by ~ (,) rel col) => IsLabel col (By rels by)

Defined in Squeal.PostgreSQL.Query

Methods

fromLabel :: By rels by

(HasUnique rel rels cols, Has col cols ty, bys ~ ((,) rel col ': ([] :: [(Symbol, Symbol)]))) => IsLabel col (NP (By rels) bys)

Defined in Squeal.PostgreSQL.Query

Methods

fromLabel :: NP (By rels) bys

Eq (By from by)

Defined in Squeal.PostgreSQL.Query

Methods

(==) :: By from by -> By from by -> Bool

(/=) :: By from by -> By from by -> Bool

Ord (By from by)

Defined in Squeal.PostgreSQL.Query

Methods

compare :: By from by -> By from by -> Ordering

(<) :: By from by -> By from by -> Bool

(<=) :: By from by -> By from by -> Bool

(>) :: By from by -> By from by -> Bool

(>=) :: By from by -> By from by -> Bool

max :: By from by -> By from by -> By from by

min :: By from by -> By from by -> By from by

Show (By from by)

Defined in Squeal.PostgreSQL.Query

Methods

showsPrec :: Int -> By from by -> ShowS

show :: By from by -> String

showList :: [By from by] -> ShowS

RenderSQL (By from by)

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: By from by -> ByteString

data GroupByClause grp from where

A GroupByClause indicates the Grouping of a TableExpression. A NoGroups indicates Ungrouped while a Group indicates Grouped. NoGroups is distinguised from Group Nil since no aggregation can be done on NoGroups while all output Expressions must be aggregated in Group Nil. In general, all output Expressions in the complement of bys must be aggregated in Group bys.

Constructors

NoGroups :: GroupByClause Ungrouped from

Group :: SListI bys => NP (By from) bys -> GroupByClause (Grouped bys) from

Instances

RenderSQL (GroupByClause grp from)

Renders a GroupByClause.

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: GroupByClause grp from -> ByteString

data HavingClause outer commons grp schemas params from where

A HavingClause is used to eliminate groups that are not of interest. An Ungrouped TableExpression may only use NoHaving while a Grouped TableExpression must use Having whose conditions are combined with .&&.

Constructors

NoHaving :: HavingClause outer commons Ungrouped schemas params from

Having :: [Condition outer commons (Grouped bys) schemas params from] -> HavingClause outer commons (Grouped bys) schemas params from

Instances

Eq (HavingClause outer commons grp schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

(==) :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Bool

(/=) :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Bool

Ord (HavingClause outer commons grp schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

compare :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Ordering

(<) :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Bool

(<=) :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Bool

(>) :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Bool

(>=) :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> Bool

max :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from

min :: HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from -> HavingClause outer commons grp schemas params from

Show (HavingClause outer commons grp schemas params from)

Defined in Squeal.PostgreSQL.Query

Methods

showsPrec :: Int -> HavingClause outer commons grp schemas params from -> ShowS

show :: HavingClause outer commons grp schemas params from -> String

showList :: [HavingClause outer commons grp schemas params from] -> ShowS

RenderSQL (HavingClause outer commons grp schemas params from)

Render a HavingClause.

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: HavingClause outer commons grp schemas params from -> ByteString