git.iko.soy/squeal
on b044fd2 help sign in
squeal/squeal-postgresql/src/Squeal/PostgreSQL/Query.hs
1409 lines 57 kB history raw
1{-|
2Module: Squeal.PostgreSQL.Query
3Description: Squeal queries
4Copyright: (c) Eitan Chatav, 2017
5Maintainer: eitan@morphism.tech
6Stability: experimental
8Squeal queries.
9-}
11{-# LANGUAGE
12 ConstraintKinds
13 , DeriveGeneric
14 , FlexibleContexts
15 , FlexibleInstances
16 , GADTs
17 , GeneralizedNewtypeDeriving
18 , LambdaCase
19 , MultiParamTypeClasses
20 , OverloadedStrings
21 , ScopedTypeVariables
22 , StandaloneDeriving
23 , TypeApplications
24 , TypeFamilies
25 , TypeInType
26 , TypeOperators
27 , RankNTypes
28 , UndecidableInstances
29 #-}
31module Squeal.PostgreSQL.Query
32 ( -- * Queries
33 Query (..)
34 , Query_
35 -- ** Select
36 , select
37 , select_
38 , selectDistinct
39 , selectDistinct_
40 , Selection (..)
41 -- ** Values
42 , values
43 , values_
44 -- ** Set Operations
45 , union
46 , unionAll
47 , intersect
48 , intersectAll
49 , except
50 , exceptAll
51 -- ** With
52 , With (..)
53 , CommonTableExpression (..)
54 , renderCommonTableExpression
55 , renderCommonTableExpressions
56 -- ** Json
57 , jsonEach
58 , jsonbEach
59 , jsonEachAsText
60 , jsonbEachAsText
61 , jsonObjectKeys
62 , jsonbObjectKeys
63 , jsonPopulateRecord
64 , jsonbPopulateRecord
65 , jsonPopulateRecordSet
66 , jsonbPopulateRecordSet
67 , jsonToRecord
68 , jsonbToRecord
69 , jsonToRecordSet
70 , jsonbToRecordSet
71 -- * Table Expressions
72 , TableExpression (..)
73 , from
74 , where_
75 , groupBy
76 , having
77 , orderBy
78 , limit
79 , offset
80 -- * From Clauses
81 , FromClause (..)
82 , table
83 , subquery
84 , view
85 , common
86 , crossJoin
87 , innerJoin
88 , leftOuterJoin
89 , rightOuterJoin
90 , fullOuterJoin
91 -- * Grouping
92 , By (..)
93 , GroupByClause (..)
94 , HavingClause (..)
95 -- * Sorting
96 , SortExpression (..)
97 -- * Subquery Expressions
98 , in_
99 , rowIn
100 , eqAll
101 , rowEqAll
102 , eqAny
103 , rowEqAny
104 , neqAll
105 , rowNeqAll
106 , neqAny
107 , rowNeqAny
108 , allLt
109 , rowLtAll
110 , ltAny
111 , rowLtAny
112 , lteAll
113 , rowLteAll
114 , lteAny
115 , rowLteAny
116 , gtAll
117 , rowGtAll
118 , gtAny
119 , rowGtAny
120 , gteAll
121 , rowGteAll
122 , gteAny
123 , rowGteAny
124 ) where
126import Control.DeepSeq
127import Data.ByteString (ByteString)
128import Data.Kind (Type)
129import Data.String
130import Data.Word
131import Generics.SOP hiding (from)
132import GHC.TypeLits
134import qualified GHC.Generics as GHC
136import Squeal.PostgreSQL.Expression
137import Squeal.PostgreSQL.Render
138import Squeal.PostgreSQL.Schema
140{- |
141The process of retrieving or the command to retrieve data from a database
142is called a `Query`. Let's see some examples of queries.
144simple query:
146>>> type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
147>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
148>>> :{
149let
150 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
151 query = select Star (from (table #tab))
152in printSQL query
153:}
154SELECT * FROM "tab" AS "tab"
156restricted query:
158>>> :{
159let
160 query :: Query '[] (Public Schema) '[] '["sum" ::: 'NotNull 'PGint4, "col1" ::: 'NotNull 'PGint4]
161 query =
162 select_ ((#col1 + #col2) `as` #sum :* #col1)
163 ( from (table #tab)
164 & where_ (#col1 .> #col2)
165 & where_ (#col2 .> 0) )
166in printSQL query
167:}
168SELECT ("col1" + "col2") AS "sum", "col1" AS "col1" FROM "tab" AS "tab" WHERE (("col1" > "col2") AND ("col2" > 0))
170subquery:
172>>> :{
173let
174 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
175 query = select Star (from (subquery (select Star (from (table #tab)) `as` #sub)))
176in printSQL query
177:}
178SELECT * FROM (SELECT * FROM "tab" AS "tab") AS "sub"
180limits and offsets:
182>>> :{
183let
184 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
185 query = select Star (from (table #tab) & limit 100 & offset 2 & limit 50 & offset 2)
186in printSQL query
187:}
188SELECT * FROM "tab" AS "tab" LIMIT 50 OFFSET 4
190parameterized query:
192>>> :{
193let
194 query :: Query '[] (Public Schema) '[ 'NotNull 'PGint4] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
195 query = select Star (from (table #tab) & where_ (#col1 .> param @1))
196in printSQL query
197:}
198SELECT * FROM "tab" AS "tab" WHERE ("col1" > ($1 :: int4))
200aggregation query:
202>>> :{
203let
204 query :: Query '[] (Public Schema) '[] '["sum" ::: 'NotNull 'PGint4, "col1" ::: 'NotNull 'PGint4 ]
205 query =
206 select_ (sum_ #col2 `as` #sum :* #col1)
207 ( from (table (#tab `as` #table1))
208 & groupBy #col1
209 & having (#col1 + sum_ #col2 .> 1) )
210in printSQL query
211:}
212SELECT sum("col2") AS "sum", "col1" AS "col1" FROM "tab" AS "table1" GROUP BY "col1" HAVING (("col1" + sum("col2")) > 1)
214sorted query:
216>>> :{
217let
218 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
219 query = select Star (from (table #tab) & orderBy [#col1 & Asc])
220in printSQL query
221:}
222SELECT * FROM "tab" AS "tab" ORDER BY "col1" ASC
224joins:
226>>> :set -XFlexibleContexts
227>>> :{
228type OrdersColumns =
229 '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4
230 , "price" ::: 'NoDef :=> 'NotNull 'PGfloat4
231 , "customer_id" ::: 'NoDef :=> 'NotNull 'PGint4
232 , "shipper_id" ::: 'NoDef :=> 'NotNull 'PGint4
233 ]
234:}
236>>> :{
237type OrdersConstraints =
238 '["pk_orders" ::: PrimaryKey '["id"]
239 ,"fk_customers" ::: ForeignKey '["customer_id"] "customers" '["id"]
240 ,"fk_shippers" ::: ForeignKey '["shipper_id"] "shippers" '["id"] ]
241:}
243>>> type NamesColumns = '["id" ::: 'NoDef :=> 'NotNull 'PGint4, "name" ::: 'NoDef :=> 'NotNull 'PGtext]
244>>> type CustomersConstraints = '["pk_customers" ::: PrimaryKey '["id"]]
245>>> type ShippersConstraints = '["pk_shippers" ::: PrimaryKey '["id"]]
246>>> :{
247type OrdersSchema =
248 '[ "orders" ::: 'Table (OrdersConstraints :=> OrdersColumns)
249 , "customers" ::: 'Table (CustomersConstraints :=> NamesColumns)
250 , "shippers" ::: 'Table (ShippersConstraints :=> NamesColumns) ]
251:}
253>>> :{
254let
255 query :: Query '[] (Public OrdersSchema)
256 '[]
257 '[ "order_price" ::: 'NotNull 'PGfloat4
258 , "customer_name" ::: 'NotNull 'PGtext
259 , "shipper_name" ::: 'NotNull 'PGtext
260 ]
261 query = select_
262 ( #o ! #price `as` #order_price :*
263 #c ! #name `as` #customer_name :*
264 #s ! #name `as` #shipper_name )
265 ( from (table (#orders `as` #o)
266 & innerJoin (table (#customers `as` #c))
267 (#o ! #customer_id .== #c ! #id)
268 & innerJoin (table (#shippers `as` #s))
269 (#o ! #shipper_id .== #s ! #id)) )
270in printSQL query
271:}
272SELECT "o"."price" AS "order_price", "c"."name" AS "customer_name", "s"."name" AS "shipper_name" 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")
274self-join:
276>>> :{
277let
278 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
279 query = select (#t1 & DotStar) (from (table (#tab `as` #t1) & crossJoin (table (#tab `as` #t2))))
280in printSQL query
281:}
282SELECT "t1".* FROM "tab" AS "t1" CROSS JOIN "tab" AS "t2"
284value queries:
286>>> :{
287let
288 query :: Query commons schemas '[] '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
289 query = values (1 `as` #foo :* true `as` #bar) [2 `as` #foo :* false `as` #bar]
290in printSQL query
291:}
292SELECT * FROM (VALUES (1, TRUE), (2, FALSE)) AS t ("foo", "bar")
294set operations:
296>>> :{
297let
298 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
299 query = select Star (from (table #tab)) `unionAll` select Star (from (table #tab))
300in printSQL query
301:}
302(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab")
304with queries:
306>>> :{
307let
308 query :: Query '[] (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
309 query = with (
310 select Star (from (table #tab)) `as` #cte1 :>>
311 select Star (from (common #cte1)) `as` #cte2
312 ) (select Star (from (common #cte2)))
313in printSQL query
314:}
315WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"
316-}
317newtype Query
318 (commons :: FromType)
319 (schemas :: SchemasType)
320 (params :: [NullityType])
321 (row :: RowType)
322 = UnsafeQuery { renderQuery :: ByteString }
323 deriving (GHC.Generic,Show,Eq,Ord,NFData)
324instance RenderSQL (Query commons schemas params row) where renderSQL = renderQuery
326type family Query_ (schemas :: SchemasType) (params :: Type) (row :: Type) where
327 Query_ schemas params row = Query '[] schemas (TuplePG params) (RowPG row)
329-- | The results of two queries can be combined using the set operation
330-- `union`. Duplicate rows are eliminated.
331union
332 :: Query commons schemas params columns
333 -> Query commons schemas params columns
334 -> Query commons schemas params columns
335q1 `union` q2 = UnsafeQuery $
336 parenthesized (renderSQL q1)
337 <+> "UNION"
338 <+> parenthesized (renderSQL q2)
340-- | The results of two queries can be combined using the set operation
341-- `unionAll`, the disjoint union. Duplicate rows are retained.
342unionAll
343 :: Query commons schemas params columns
344 -> Query commons schemas params columns
345 -> Query commons schemas params columns
346q1 `unionAll` q2 = UnsafeQuery $
347 parenthesized (renderSQL q1)
348 <+> "UNION" <+> "ALL"
349 <+> parenthesized (renderSQL q2)
351-- | The results of two queries can be combined using the set operation
352-- `intersect`, the intersection. Duplicate rows are eliminated.
353intersect
354 :: Query commons schemas params columns
355 -> Query commons schemas params columns
356 -> Query commons schemas params columns
357q1 `intersect` q2 = UnsafeQuery $
358 parenthesized (renderSQL q1)
359 <+> "INTERSECT"
360 <+> parenthesized (renderSQL q2)
362-- | The results of two queries can be combined using the set operation
363-- `intersectAll`, the intersection. Duplicate rows are retained.
364intersectAll
365 :: Query commons schemas params columns
366 -> Query commons schemas params columns
367 -> Query commons schemas params columns
368q1 `intersectAll` q2 = UnsafeQuery $
369 parenthesized (renderSQL q1)
370 <+> "INTERSECT" <+> "ALL"
371 <+> parenthesized (renderSQL q2)
373-- | The results of two queries can be combined using the set operation
374-- `except`, the set difference. Duplicate rows are eliminated.
375except
376 :: Query commons schemas params columns
377 -> Query commons schemas params columns
378 -> Query commons schemas params columns
379q1 `except` q2 = UnsafeQuery $
380 parenthesized (renderSQL q1)
381 <+> "EXCEPT"
382 <+> parenthesized (renderSQL q2)
384-- | The results of two queries can be combined using the set operation
385-- `exceptAll`, the set difference. Duplicate rows are retained.
386exceptAll
387 :: Query commons schemas params columns
388 -> Query commons schemas params columns
389 -> Query commons schemas params columns
390q1 `exceptAll` q2 = UnsafeQuery $
391 parenthesized (renderSQL q1)
392 <+> "EXCEPT" <+> "ALL"
393 <+> parenthesized (renderSQL q2)
395{-----------------------------------------
396SELECT queries
397-----------------------------------------}
399data Selection commons schemas params grp from row where
400 List
401 :: SListI row
402 => NP (Aliased (Expression commons schemas params grp from)) row
403 -> Selection commons schemas params grp from row
404 Star
405 :: HasUnique tab from row
406 => Selection commons schemas params 'Ungrouped from row
407 DotStar
408 :: Has tab from row
409 => Alias tab
410 -> Selection commons schemas params 'Ungrouped from row
411 Also
412 :: Selection commons schemas params grp from right
413 -> Selection commons schemas params grp from left
414 -> Selection commons schemas params grp from (Join left right)
415instance (KnownSymbol col, row ~ '[col ::: ty])
416 => Aliasable col
417 (Expression commons schemas params grp from ty)
418 (Selection commons schemas params grp from row) where
419 expr `as` col = List (expr `as` col)
420instance (Has tab from row0, Has col row0 ty, row1 ~ '[col ::: ty])
421 => IsQualified tab col (Selection commons schemas params 'Ungrouped from row1) where
422 tab ! col = tab ! col `as` col
423instance
424 ( Has tab from row0
425 , Has col row0 ty
426 , row1 ~ '[col ::: ty]
427 , GroupedBy tab col bys )
428 => IsQualified tab col (Selection commons schemas params ('Grouped bys) from row1) where
429 tab ! col = tab ! col `as` col
430instance (HasUnique tab from row0, Has col row0 ty, row1 ~ '[col ::: ty])
431 => IsLabel col (Selection commons schemas params 'Ungrouped from row1) where
432 fromLabel = fromLabel @col `as` Alias
433instance
434 ( HasUnique tab from row0
435 , Has col row0 ty
436 , row1 ~ '[col ::: ty]
437 , GroupedBy tab col bys )
438 => IsLabel col (Selection commons schemas params ('Grouped bys) from row1) where
439 fromLabel = fromLabel @col `as` Alias
441instance RenderSQL (Selection commons schemas params grp from row) where
442 renderSQL = \case
443 List list -> renderCommaSeparated (renderAliased renderSQL) list
444 Star -> "*"
445 DotStar tab -> renderSQL tab <> ".*"
446 Also right left -> renderSQL left <> ", " <> renderSQL right
448-- | the `TableExpression` in the `select` command constructs an intermediate
449-- virtual table by possibly combining tables, views, eliminating rows,
450-- grouping, etc. This table is finally passed on to processing by
451-- the select list. The `Selection` determines which columns of
452-- the intermediate table are actually output.
453select
454 :: (SListI row, row ~ (x ': xs))
455 => Selection commons schemas params grp from row
456 -- ^ select list
457 -> TableExpression commons schemas params grp from
458 -- ^ intermediate virtual table
459 -> Query commons schemas params row
460select selection tabexpr = UnsafeQuery $
461 "SELECT"
462 <+> renderSQL selection
463 <+> renderSQL tabexpr
465select_
466 :: (SListI row, row ~ (x ': xs))
467 => NP (Aliased (Expression commons schemas params grp from)) row
468 -> TableExpression commons schemas params grp from
469 -> Query commons schemas params row
470select_ list = select (List list)
472-- | After the select list has been processed, the result table can
473-- be subject to the elimination of duplicate rows using `selectDistinct`.
474selectDistinct
475 :: (SListI columns, columns ~ (col ': cols))
476 => Selection commons schemas params 'Ungrouped from columns
477 -- ^ select list
478 -> TableExpression commons schemas params 'Ungrouped from
479 -- ^ intermediate virtual table
480 -> Query commons schemas params columns
481selectDistinct selection tabexpr = UnsafeQuery $
482 "SELECT DISTINCT"
483 <+> renderSQL selection
484 <+> renderSQL tabexpr
486selectDistinct_
487 :: (SListI columns, columns ~ (col ': cols))
488 => NP (Aliased (Expression commons schemas params 'Ungrouped from)) columns
489 -- ^ select list
490 -> TableExpression commons schemas params 'Ungrouped from
491 -- ^ intermediate virtual table
492 -> Query commons schemas params columns
493selectDistinct_ list = select (List list)
495-- | `values` computes a row value or set of row values
496-- specified by value expressions. It is most commonly used
497-- to generate a “constant table” within a larger command,
498-- but it can be used on its own.
499--
500-- >>> type Row = '["a" ::: 'NotNull 'PGint4, "b" ::: 'NotNull 'PGtext]
501-- >>> let query = values (1 `as` #a :* "one" `as` #b) [] :: Query commons schemas '[] Row
502-- >>> printSQL query
503-- SELECT * FROM (VALUES (1, E'one')) AS t ("a", "b")
504values
505 :: SListI cols
506 => NP (Aliased (Expression commons schemas params 'Ungrouped '[] )) cols
507 -> [NP (Aliased (Expression commons schemas params 'Ungrouped '[] )) cols]
508 -- ^ When more than one row is specified, all the rows must
509 -- must have the same number of elements
510 -> Query commons schemas params cols
511values rw rws = UnsafeQuery $ "SELECT * FROM"
512 <+> parenthesized (
513 "VALUES"
514 <+> commaSeparated
515 ( parenthesized
516 . renderCommaSeparated renderValuePart <$> rw:rws )
517 ) <+> "AS t"
518 <+> parenthesized (renderCommaSeparated renderAliasPart rw)
519 where
520 renderAliasPart, renderValuePart
521 :: Aliased (Expression commons schemas params 'Ungrouped '[] ) ty -> ByteString
522 renderAliasPart (_ `As` name) = renderSQL name
523 renderValuePart (value `As` _) = renderSQL value
525-- | `values_` computes a row value or set of row values
526-- specified by value expressions.
527values_
528 :: SListI cols
529 => NP (Aliased (Expression commons schemas params 'Ungrouped '[] )) cols
530 -- ^ one row of values
531 -> Query commons schemas params cols
532values_ rw = values rw []
534{-----------------------------------------
535Table Expressions
536-----------------------------------------}
538-- | A `TableExpression` computes a table. The table expression contains
539-- a `fromClause` that is optionally followed by a `whereClause`,
540-- `groupByClause`, `havingClause`, `orderByClause`, `limitClause`
541-- and `offsetClause`s. Trivial table expressions simply refer
542-- to a table on disk, a so-called base table, but more complex expressions
543-- can be used to modify or combine base tables in various ways.
544data TableExpression
545 (commons :: FromType)
546 (schemas :: SchemasType)
547 (params :: [NullityType])
548 (grp :: Grouping)
549 (from :: FromType)
550 = TableExpression
551 { fromClause :: FromClause commons schemas params from
552 -- ^ A table reference that can be a table name, or a derived table such
553 -- as a subquery, a @JOIN@ construct, or complex combinations of these.
554 , whereClause :: [Condition commons schemas params 'Ungrouped from]
555 -- ^ optional search coditions, combined with `.&&`. After the processing
556 -- of the `fromClause` is done, each row of the derived virtual table
557 -- is checked against the search condition. If the result of the
558 -- condition is true, the row is kept in the output table,
559 -- otherwise it is discarded. The search condition typically references
560 -- at least one column of the table generated in the `fromClause`;
561 -- this is not required, but otherwise the WHERE clause will
562 -- be fairly useless.
563 , groupByClause :: GroupByClause grp from
564 -- ^ The `groupByClause` is used to group together those rows in a table
565 -- that have the same values in all the columns listed. The order in which
566 -- the columns are listed does not matter. The effect is to combine each
567 -- set of rows having common values into one group row that represents all
568 -- rows in the group. This is done to eliminate redundancy in the output
569 -- and/or compute aggregates that apply to these groups.
570 , havingClause :: HavingClause commons schemas params grp from
571 -- ^ If a table has been grouped using `groupBy`, but only certain groups
572 -- are of interest, the `havingClause` can be used, much like a
573 -- `whereClause`, to eliminate groups from the result. Expressions in the
574 -- `havingClause` can refer both to grouped expressions and to ungrouped
575 -- expressions (which necessarily involve an aggregate function).
576 , orderByClause :: [SortExpression commons schemas params grp from]
577 -- ^ The `orderByClause` is for optional sorting. When more than one
578 -- `SortExpression` is specified, the later (right) values are used to sort
579 -- rows that are equal according to the earlier (left) values.
580 , limitClause :: [Word64]
581 -- ^ The `limitClause` is combined with `min` to give a limit count
582 -- if nonempty. If a limit count is given, no more than that many rows
583 -- will be returned (but possibly fewer, if the query itself yields
584 -- fewer rows).
585 , offsetClause :: [Word64]
586 -- ^ The `offsetClause` is combined with `+` to give an offset count
587 -- if nonempty. The offset count says to skip that many rows before
588 -- beginning to return rows. The rows are skipped before the limit count
589 -- is applied.
590 }
592-- | Render a `TableExpression`
593instance RenderSQL (TableExpression commons schemas params grp from) where
594 renderSQL
595 (TableExpression frm' whs' grps' hvs' srts' lims' offs') = mconcat
596 [ "FROM ", renderSQL frm'
597 , renderWheres whs'
598 , renderSQL grps'
599 , renderSQL hvs'
600 , renderOrderByClause srts'
601 , renderLimits lims'
602 , renderOffsets offs'
603 ]
604 where
605 renderWheres = \case
606 [] -> ""
607 wh:[] -> " WHERE" <+> renderSQL wh
608 wh:whs -> " WHERE" <+> renderSQL (foldr (.&&) wh whs)
609 renderOrderByClause = \case
610 [] -> ""
611 srts -> " ORDER BY"
612 <+> commaSeparated (renderSQL <$> srts)
613 renderLimits = \case
614 [] -> ""
615 lims -> " LIMIT" <+> fromString (show (minimum lims))
616 renderOffsets = \case
617 [] -> ""
618 offs -> " OFFSET" <+> fromString (show (sum offs))
620-- | A `from` generates a `TableExpression` from a table reference that can be
621-- a table name, or a derived table such as a subquery, a JOIN construct,
622-- or complex combinations of these. A `from` may be transformed by `where_`,
623-- `group`, `having`, `orderBy`, `limit` and `offset`, using the `&` operator
624-- to match the left-to-right sequencing of their placement in SQL.
625from
626 :: FromClause commons schemas params from -- ^ table reference
627 -> TableExpression commons schemas params 'Ungrouped from
628from rels = TableExpression rels [] NoGroups NoHaving [] [] []
630-- | A `where_` is an endomorphism of `TableExpression`s which adds a
631-- search condition to the `whereClause`.
632where_
633 :: Condition commons schemas params 'Ungrouped from -- ^ filtering condition
634 -> TableExpression commons schemas params grp from
635 -> TableExpression commons schemas params grp from
636where_ wh rels = rels {whereClause = wh : whereClause rels}
638-- | A `groupBy` is a transformation of `TableExpression`s which switches
639-- its `Grouping` from `Ungrouped` to `Grouped`. Use @group Nil@ to perform
640-- a "grand total" aggregation query.
641groupBy
642 :: SListI bys
643 => NP (By from) bys -- ^ grouped columns
644 -> TableExpression commons schemas params 'Ungrouped from
645 -> TableExpression commons schemas params ('Grouped bys) from
646groupBy bys rels = TableExpression
647 { fromClause = fromClause rels
648 , whereClause = whereClause rels
649 , groupByClause = Group bys
650 , havingClause = Having []
651 , orderByClause = []
652 , limitClause = limitClause rels
653 , offsetClause = offsetClause rels
654 }
656-- | A `having` is an endomorphism of `TableExpression`s which adds a
657-- search condition to the `havingClause`.
658having
659 :: Condition commons schemas params ('Grouped bys) from -- ^ having condition
660 -> TableExpression commons schemas params ('Grouped bys) from
661 -> TableExpression commons schemas params ('Grouped bys) from
662having hv rels = rels
663 { havingClause = case havingClause rels of Having hvs -> Having (hv:hvs) }
665-- | An `orderBy` is an endomorphism of `TableExpression`s which appends an
666-- ordering to the right of the `orderByClause`.
667orderBy
668 :: [SortExpression commons schemas params grp from] -- ^ sort expressions
669 -> TableExpression commons schemas params grp from
670 -> TableExpression commons schemas params grp from
671orderBy srts rels = rels {orderByClause = orderByClause rels ++ srts}
673-- | A `limit` is an endomorphism of `TableExpression`s which adds to the
674-- `limitClause`.
675limit
676 :: Word64 -- ^ limit parameter
677 -> TableExpression commons schemas params grp from
678 -> TableExpression commons schemas params grp from
679limit lim rels = rels {limitClause = lim : limitClause rels}
681-- | An `offset` is an endomorphism of `TableExpression`s which adds to the
682-- `offsetClause`.
683offset
684 :: Word64 -- ^ offset parameter
685 -> TableExpression commons schemas params grp from
686 -> TableExpression commons schemas params grp from
687offset off rels = rels {offsetClause = off : offsetClause rels}
689{-----------------------------------------
690JSON stuff
691-----------------------------------------}
693unsafeSetOfFunction
694 :: ByteString
695 -> Expression commons schemas params 'Ungrouped '[] ty
696 -> Query commons schemas params row
697unsafeSetOfFunction fun expr = UnsafeQuery $
698 "SELECT * FROM " <> fun <> "(" <> renderSQL expr <> ")"
700-- | Expands the outermost JSON object into a set of key/value pairs.
701jsonEach
702 :: Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json object
703 -> Query commons schemas params
704 '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGjson]
705jsonEach = unsafeSetOfFunction "json_each"
707-- | Expands the outermost binary JSON object into a set of key/value pairs.
708jsonbEach
709 :: Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb object
710 -> Query commons schemas params
711 '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGjsonb]
712jsonbEach = unsafeSetOfFunction "jsonb_each"
714-- | Expands the outermost JSON object into a set of key/value pairs.
715jsonEachAsText
716 :: Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json object
717 -> Query commons schemas params
718 '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGtext]
719jsonEachAsText = unsafeSetOfFunction "json_each_text"
721-- | Expands the outermost binary JSON object into a set of key/value pairs.
722jsonbEachAsText
723 :: Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb object
724 -> Query commons schemas params
725 '["key" ::: 'NotNull 'PGtext, "value" ::: 'NotNull 'PGtext]
726jsonbEachAsText = unsafeSetOfFunction "jsonb_each_text"
728-- | Returns set of keys in the outermost JSON object.
729jsonObjectKeys
730 :: Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json object
731 -> Query commons schemas params '["json_object_keys" ::: 'NotNull 'PGtext]
732jsonObjectKeys = unsafeSetOfFunction "json_object_keys"
734-- | Returns set of keys in the outermost JSON object.
735jsonbObjectKeys
736 :: Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb object
737 -> Query commons schemas params '["jsonb_object_keys" ::: 'NotNull 'PGtext]
738jsonbObjectKeys = unsafeSetOfFunction "jsonb_object_keys"
740unsafePopulateFunction
741 :: ByteString
742 -> TypeExpression schemas (nullity ('PGcomposite row))
743 -> Expression commons schemas params 'Ungrouped '[] ty
744 -> Query commons schemas params row
745unsafePopulateFunction fun ty expr = UnsafeQuery $
746 "SELECT * FROM " <> fun <> "("
747 <> "null::" <> renderSQL ty <> ", "
748 <> renderSQL expr <> ")"
750-- | Expands the JSON expression to a row whose columns match the record
751-- type defined by the given table.
752jsonPopulateRecord
753 :: TypeExpression schemas (nullity ('PGcomposite row)) -- ^ row type
754 -> Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json object
755 -> Query commons schemas params row
756jsonPopulateRecord = unsafePopulateFunction "json_populate_record"
758-- | Expands the binary JSON expression to a row whose columns match the record
759-- type defined by the given table.
760jsonbPopulateRecord
761 :: TypeExpression schemas (nullity ('PGcomposite row)) -- ^ row type
762 -> Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb object
763 -> Query commons schemas params row
764jsonbPopulateRecord = unsafePopulateFunction "jsonb_populate_record"
766-- | Expands the outermost array of objects in the given JSON expression to a
767-- set of rows whose columns match the record type defined by the given table.
768jsonPopulateRecordSet
769 :: TypeExpression schemas (nullity ('PGcomposite row)) -- ^ row type
770 -> Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json array
771 -> Query commons schemas params row
772jsonPopulateRecordSet = unsafePopulateFunction "json_populate_record_set"
774-- | Expands the outermost array of objects in the given binary JSON expression
775-- to a set of rows whose columns match the record type defined by the given
776-- table.
777jsonbPopulateRecordSet
778 :: TypeExpression schemas (nullity ('PGcomposite row)) -- ^ row type
779 -> Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb array
780 -> Query commons schemas params row
781jsonbPopulateRecordSet = unsafePopulateFunction "jsonb_populate_record_set"
783unsafeRecordFunction
784 :: (SListI record, json `In` PGJsonType)
785 => ByteString
786 -> Expression commons schemas params 'Ungrouped '[] (nullity json)
787 -> NP (Aliased (TypeExpression schemas)) record
788 -> Query commons schemas params record
789unsafeRecordFunction fun expr types = UnsafeQuery $
790 "SELECT * FROM " <> fun <> "("
791 <> renderSQL expr <> ")"
792 <+> "AS" <+> "x" <> parenthesized (renderCommaSeparated renderTy types)
793 where
794 renderTy :: Aliased (TypeExpression schemas) ty -> ByteString
795 renderTy (ty `As` alias) =
796 renderSQL alias <+> renderSQL ty
798-- | Builds an arbitrary record from a JSON object.
799jsonToRecord
800 :: SListI record
801 => Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json object
802 -> NP (Aliased (TypeExpression schemas)) record -- ^ record types
803 -> Query commons schemas params record
804jsonToRecord = unsafeRecordFunction "json_to_record"
806-- | Builds an arbitrary record from a binary JSON object.
807jsonbToRecord
808 :: SListI record
809 => Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb object
810 -> NP (Aliased (TypeExpression schemas)) record -- ^ record types
811 -> Query commons schemas params record
812jsonbToRecord = unsafeRecordFunction "jsonb_to_record"
814-- | Builds an arbitrary set of records from a JSON array of objects.
815jsonToRecordSet
816 :: SListI record
817 => Expression commons schemas params 'Ungrouped '[] (nullity 'PGjson) -- ^ json array
818 -> NP (Aliased (TypeExpression schemas)) record -- ^ record types
819 -> Query commons schemas params record
820jsonToRecordSet = unsafeRecordFunction "json_to_record_set"
822-- | Builds an arbitrary set of records from a binary JSON array of objects.
823jsonbToRecordSet
824 :: SListI record
825 => Expression commons schemas params 'Ungrouped '[] (nullity 'PGjsonb) -- ^ jsonb array
826 -> NP (Aliased (TypeExpression schemas)) record -- ^ record types
827 -> Query commons schemas params record
828jsonbToRecordSet = unsafeRecordFunction "jsonb_to_record_set"
830{-----------------------------------------
831FROM clauses
832-----------------------------------------}
834{- |
835A `FromClause` can be a table name, or a derived table such
836as a subquery, a @JOIN@ construct, or complex combinations of these.
837-}
838newtype FromClause commons schemas params from
839 = UnsafeFromClause { renderFromClause :: ByteString }
840 deriving (GHC.Generic,Show,Eq,Ord,NFData)
841instance RenderSQL (FromClause commons schemas params from) where
842 renderSQL = renderFromClause
844-- | A real `table` is a table from the database.
845table
846 :: (Has sch schemas schema, Has tab schema ('Table table))
847 => Aliased (QualifiedAlias sch) (alias ::: tab)
848 -> FromClause commons schemas params '[alias ::: TableToRow table]
849table (tab `As` alias) = UnsafeFromClause $
850 renderSQL tab <+> "AS" <+> renderSQL alias
852-- | `subquery` derives a table from a `Query`.
853subquery
854 :: Aliased (Query commons schemas params) query
855 -> FromClause commons schemas params '[query]
856subquery = UnsafeFromClause . renderAliased (parenthesized . renderQuery)
858-- | `view` derives a table from a `View`.
859view
860 :: (Has sch schemas schema, Has vw schema ('View view))
861 => Aliased (QualifiedAlias sch) (alias ::: vw)
862 -> FromClause commons schemas params '[alias ::: view]
863view (vw `As` alias) = UnsafeFromClause $
864 renderSQL vw <+> "AS" <+> renderSQL alias
866common
867 :: Has cte commons common
868 => Aliased Alias (alias ::: cte)
869 -> FromClause commons schemas params '[alias ::: common]
870common (cte `As` alias) = UnsafeFromClause $
871 renderSQL cte <+> "AS" <+> renderSQL alias
873{- | @left & crossJoin right@. For every possible combination of rows from
874 @left@ and @right@ (i.e., a Cartesian product), the joined table will contain
875 a row consisting of all columns in @left@ followed by all columns in @right@.
876 If the tables have @n@ and @m@ rows respectively, the joined table will
877 have @n * m@ rows.
878-}
879crossJoin
880 :: FromClause commons schemas params right
881 -- ^ right
882 -> FromClause commons schemas params left
883 -- ^ left
884 -> FromClause commons schemas params (Join left right)
885crossJoin right left = UnsafeFromClause $
886 renderSQL left <+> "CROSS JOIN" <+> renderSQL right
888{- | @left & innerJoin right on@. The joined table is filtered by
889the @on@ condition.
890-}
891innerJoin
892 :: FromClause commons schemas params right
893 -- ^ right
894 -> Condition commons schemas params 'Ungrouped (Join left right)
895 -- ^ @on@ condition
896 -> FromClause commons schemas params left
897 -- ^ left
898 -> FromClause commons schemas params (Join left right)
899innerJoin right on left = UnsafeFromClause $
900 renderSQL left <+> "INNER JOIN" <+> renderSQL right
901 <+> "ON" <+> renderSQL on
903{- | @left & leftOuterJoin right on@. First, an inner join is performed.
904 Then, for each row in @left@ that does not satisfy the @on@ condition with
905 any row in @right@, a joined row is added with null values in columns of @right@.
906 Thus, the joined table always has at least one row for each row in @left@.
907-}
908leftOuterJoin
909 :: FromClause commons schemas params right
910 -- ^ right
911 -> Condition commons schemas params 'Ungrouped (Join left right)
912 -- ^ @on@ condition
913 -> FromClause commons schemas params left
914 -- ^ left
915 -> FromClause commons schemas params (Join left (NullifyFrom right))
916leftOuterJoin right on left = UnsafeFromClause $
917 renderSQL left <+> "LEFT OUTER JOIN" <+> renderSQL right
918 <+> "ON" <+> renderSQL on
920{- | @left & rightOuterJoin right on@. First, an inner join is performed.
921 Then, for each row in @right@ that does not satisfy the @on@ condition with
922 any row in @left@, a joined row is added with null values in columns of @left@.
923 This is the converse of a left join: the result table will always
924 have a row for each row in @right@.
925-}
926rightOuterJoin
927 :: FromClause commons schemas params right
928 -- ^ right
929 -> Condition commons schemas params 'Ungrouped (Join left right)
930 -- ^ @on@ condition
931 -> FromClause commons schemas params left
932 -- ^ left
933 -> FromClause commons schemas params (Join (NullifyFrom left) right)
934rightOuterJoin right on left = UnsafeFromClause $
935 renderSQL left <+> "RIGHT OUTER JOIN" <+> renderSQL right
936 <+> "ON" <+> renderSQL on
938{- | @left & fullOuterJoin right on@. First, an inner join is performed.
939 Then, for each row in @left@ that does not satisfy the @on@ condition with
940 any row in @right@, a joined row is added with null values in columns of @right@.
941 Also, for each row of @right@ that does not satisfy the join condition
942 with any row in @left@, a joined row with null values in the columns of @left@
943 is added.
944-}
945fullOuterJoin
946 :: FromClause commons schemas params right
947 -- ^ right
948 -> Condition commons schemas params 'Ungrouped (Join left right)
949 -- ^ @on@ condition
950 -> FromClause commons schemas params left
951 -- ^ left
952 -> FromClause commons schemas params
953 (Join (NullifyFrom left) (NullifyFrom right))
954fullOuterJoin right on left = UnsafeFromClause $
955 renderSQL left <+> "FULL OUTER JOIN" <+> renderSQL right
956 <+> "ON" <+> renderSQL on
958{-----------------------------------------
959Grouping
960-----------------------------------------}
962-- | `By`s are used in `group` to reference a list of columns which are then
963-- used to group together those rows in a table that have the same values
964-- in all the columns listed. @By \#col@ will reference an unambiguous
965-- column @col@; otherwise @By2 (\#tab \! \#col)@ will reference a table
966-- qualified column @tab.col@.
967data By
968 (from :: FromType)
969 (by :: (Symbol,Symbol)) where
970 By1
971 :: (HasUnique table from columns, Has column columns ty)
972 => Alias column
973 -> By from '(table, column)
974 By2
975 :: (Has table from columns, Has column columns ty)
976 => Alias table
977 -> Alias column
978 -> By from '(table, column)
979deriving instance Show (By from by)
980deriving instance Eq (By from by)
981deriving instance Ord (By from by)
982instance RenderSQL (By from by) where
983 renderSQL = \case
984 By1 column -> renderSQL column
985 By2 rel column -> renderSQL rel <> "." <> renderSQL column
987instance (HasUnique rel rels cols, Has col cols ty, by ~ '(rel, col))
988 => IsLabel col (By rels by) where fromLabel = By1 fromLabel
989instance (HasUnique rel rels cols, Has col cols ty, bys ~ '[ '(rel, col)])
990 => IsLabel col (NP (By rels) bys) where fromLabel = By1 fromLabel :* Nil
991instance (Has rel rels cols, Has col cols ty, by ~ '(rel, col))
992 => IsQualified rel col (By rels by) where (!) = By2
993instance (Has rel rels cols, Has col cols ty, bys ~ '[ '(rel, col)])
994 => IsQualified rel col (NP (By rels) bys) where
995 rel ! col = By2 rel col :* Nil
997-- | A `GroupByClause` indicates the `Grouping` of a `TableExpression`.
998-- A `NoGroups` indicates `Ungrouped` while a `Group` indicates `Grouped`.
999-- @NoGroups@ is distinguised from @Group Nil@ since no aggregation can be
1000-- done on @NoGroups@ while all output `Expression`s must be aggregated
1001-- in @Group Nil@. In general, all output `Expression`s in the
1002-- complement of @bys@ must be aggregated in @Group bys@.
1003data GroupByClause grp from where
1004 NoGroups :: GroupByClause 'Ungrouped from
1005 Group
1006 :: SListI bys
1007 => NP (By from) bys
1008 -> GroupByClause ('Grouped bys) from
1010-- | Renders a `GroupByClause`.
1011instance RenderSQL (GroupByClause grp from) where
1012 renderSQL = \case
1013 NoGroups -> ""
1014 Group Nil -> ""
1015 Group bys -> " GROUP BY" <+> renderCommaSeparated renderSQL bys
1017-- | A `HavingClause` is used to eliminate groups that are not of interest.
1018-- An `Ungrouped` `TableExpression` may only use `NoHaving` while a `Grouped`
1019-- `TableExpression` must use `Having` whose conditions are combined with
1020-- `.&&`.
1021data HavingClause commons schemas params grp from where
1022 NoHaving :: HavingClause commons schemas params 'Ungrouped from
1023 Having
1024 :: [Condition commons schemas params ('Grouped bys) from]
1025 -> HavingClause commons schemas params ('Grouped bys) from
1026deriving instance Show (HavingClause commons schemas params grp from)
1027deriving instance Eq (HavingClause commons schemas params grp from)
1028deriving instance Ord (HavingClause commons schemas params grp from)
1030-- | Render a `HavingClause`.
1031instance RenderSQL (HavingClause commons schemas params grp from) where
1032 renderSQL = \case
1033 NoHaving -> ""
1034 Having [] -> ""
1035 Having conditions ->
1036 " HAVING" <+> commaSeparated (renderSQL <$> conditions)
1038{-----------------------------------------
1039Sorting
1040-----------------------------------------}
1042-- | `SortExpression`s are used by `sortBy` to optionally sort the results
1043-- of a `Query`. `Asc` or `Desc` set the sort direction of a `NotNull` result
1044-- column to ascending or descending. Ascending order puts smaller values
1045-- first, where "smaller" is defined in terms of the `.<` operator. Similarly,
1046-- descending order is determined with the `.>` operator. `AscNullsFirst`,
1047-- `AscNullsLast`, `DescNullsFirst` and `DescNullsLast` options are used to
1048-- determine whether nulls appear before or after non-null values in the sort
1049-- ordering of a `Null` result column.
1050data SortExpression commons schemas params grp from where
1051 Asc
1052 :: Expression commons schemas params grp from ('NotNull ty)
1053 -> SortExpression commons schemas params grp from
1054 Desc
1055 :: Expression commons schemas params grp from ('NotNull ty)
1056 -> SortExpression commons schemas params grp from
1057 AscNullsFirst
1058 :: Expression commons schemas params grp from ('Null ty)
1059 -> SortExpression commons schemas params grp from
1060 AscNullsLast
1061 :: Expression commons schemas params grp from ('Null ty)
1062 -> SortExpression commons schemas params grp from
1063 DescNullsFirst
1064 :: Expression commons schemas params grp from ('Null ty)
1065 -> SortExpression commons schemas params grp from
1066 DescNullsLast
1067 :: Expression commons schemas params grp from ('Null ty)
1068 -> SortExpression commons schemas params grp from
1069deriving instance Show (SortExpression commons schemas params grp from)
1071-- | Render a `SortExpression`.
1072instance RenderSQL (SortExpression commons schemas params grp from) where
1073 renderSQL = \case
1074 Asc expression -> renderSQL expression <+> "ASC"
1075 Desc expression -> renderSQL expression <+> "DESC"
1076 AscNullsFirst expression -> renderSQL expression
1077 <+> "ASC NULLS FIRST"
1078 DescNullsFirst expression -> renderSQL expression
1079 <+> "DESC NULLS FIRST"
1080 AscNullsLast expression -> renderSQL expression <+> "ASC NULLS LAST"
1081 DescNullsLast expression -> renderSQL expression <+> "DESC NULLS LAST"
1083unsafeSubqueryExpression
1084 :: ByteString
1085 -> Expression commons schemas params grp from ty
1086 -> Query commons schemas params '[alias ::: ty]
1087 -> Expression commons schemas params grp from (nullity 'PGbool)
1088unsafeSubqueryExpression op x q = UnsafeExpression $
1089 renderSQL x <+> op <+> parenthesized (renderSQL q)
1091unsafeRowSubqueryExpression
1092 :: SListI row
1093 => ByteString
1094 -> NP (Aliased (Expression commons schemas params grp from)) row
1095 -> Query commons schemas params row
1096 -> Expression commons schemas params grp from (nullity 'PGbool)
1097unsafeRowSubqueryExpression op xs q = UnsafeExpression $
1098 renderSQL (row xs) <+> op <+> parenthesized (renderSQL q)
1100-- | The right-hand side is a sub`Query`, which must return exactly one column.
1101-- The left-hand expression is evaluated and compared to each row of the
1102-- sub`Query` result. The result of `in_` is `true` if any equal subquery row is found.
1103-- The result is `false` if no equal row is found
1104-- (including the case where the subquery returns no rows).
1106-- >>> printSQL $ true `in_` values_ (true `as` #foo)
1107-- TRUE IN (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1108in_
1109 :: Expression commons schemas params grp from ty -- ^ expression
1110 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1111 -> Expression commons schemas params grp from (nullity 'PGbool)
1112in_ = unsafeSubqueryExpression "IN"
1114{- | The left-hand side of this form of `rowIn` is a row constructor.
1115The right-hand side is a sub`Query`,
1116which must return exactly as many columns as
1117there are expressions in the left-hand row.
1118The left-hand expressions are evaluated and compared row-wise to each row
1119of the subquery result. The result of `rowIn`
1120is `true` if any equal subquery row is found.
1121The result is `false` if no equal row is found
1122(including the case where the subquery returns no rows).
1124>>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1125>>> printSQL $ myRow `rowIn` values_ myRow
1126ROW(1, FALSE) IN (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1128rowIn
1129 :: SListI row
1130 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1131 -> Query commons schemas params row -- ^ subquery
1132 -> Expression commons schemas params grp from (nullity 'PGbool)
1133rowIn = unsafeRowSubqueryExpression "IN"
1135-- | >>> printSQL $ true `eqAll` values_ (true `as` #foo)
1136-- TRUE = ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1137eqAll
1138 :: Expression commons schemas params grp from ty -- ^ expression
1139 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1140 -> Expression commons schemas params grp from (nullity 'PGbool)
1141eqAll = unsafeSubqueryExpression "= ALL"
1143-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1144-- >>> printSQL $ myRow `rowEqAll` values_ myRow
1145-- ROW(1, FALSE) = ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1146rowEqAll
1147 :: SListI row
1148 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1149 -> Query commons schemas params row -- ^ subquery
1150 -> Expression commons schemas params grp from (nullity 'PGbool)
1151rowEqAll = unsafeRowSubqueryExpression "= ALL"
1153-- | >>> printSQL $ true `eqAny` values_ (true `as` #foo)
1154-- TRUE = ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1155eqAny
1156 :: Expression commons schemas params grp from ty -- ^ expression
1157 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1158 -> Expression commons schemas params grp from (nullity 'PGbool)
1159eqAny = unsafeSubqueryExpression "= ANY"
1161-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1162-- >>> printSQL $ myRow `rowEqAny` values_ myRow
1163-- ROW(1, FALSE) = ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1164rowEqAny
1165 :: SListI row
1166 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1167 -> Query commons schemas params row -- ^ subquery
1168 -> Expression commons schemas params grp from (nullity 'PGbool)
1169rowEqAny = unsafeRowSubqueryExpression "= ANY"
1171-- | >>> printSQL $ true `neqAll` values_ (true `as` #foo)
1172-- TRUE <> ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1173neqAll
1174 :: Expression commons schemas params grp from ty -- ^ expression
1175 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1176 -> Expression commons schemas params grp from (nullity 'PGbool)
1177neqAll = unsafeSubqueryExpression "<> ALL"
1179-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1180-- >>> printSQL $ myRow `rowNeqAll` values_ myRow
1181-- ROW(1, FALSE) <> ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1182rowNeqAll
1183 :: SListI row
1184 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1185 -> Query commons schemas params row -- ^ subquery
1186 -> Expression commons schemas params grp from (nullity 'PGbool)
1187rowNeqAll = unsafeRowSubqueryExpression "<> ALL"
1189-- | >>> printSQL $ true `neqAny` values_ (true `as` #foo)
1190-- TRUE <> ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1191neqAny
1192 :: Expression commons schemas params grp from ty -- ^ expression
1193 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1194 -> Expression commons schemas params grp from (nullity 'PGbool)
1195neqAny = unsafeSubqueryExpression "<> ANY"
1197-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1198-- >>> printSQL $ myRow `rowNeqAny` values_ myRow
1199-- ROW(1, FALSE) <> ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1200rowNeqAny
1201 :: SListI row
1202 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1203 -> Query commons schemas params row -- ^ subquery
1204 -> Expression commons schemas params grp from (nullity 'PGbool)
1205rowNeqAny = unsafeRowSubqueryExpression "<> ANY"
1207-- | >>> printSQL $ true `allLt` values_ (true `as` #foo)
1208-- TRUE ALL < (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1209allLt
1210 :: Expression commons schemas params grp from ty -- ^ expression
1211 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1212 -> Expression commons schemas params grp from (nullity 'PGbool)
1213allLt = unsafeSubqueryExpression "ALL <"
1215-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1216-- >>> printSQL $ myRow `rowLtAll` values_ myRow
1217-- ROW(1, FALSE) ALL < (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1218rowLtAll
1219 :: SListI row
1220 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1221 -> Query commons schemas params row -- ^ subquery
1222 -> Expression commons schemas params grp from (nullity 'PGbool)
1223rowLtAll = unsafeRowSubqueryExpression "ALL <"
1225-- | >>> printSQL $ true `ltAny` values_ (true `as` #foo)
1226-- TRUE ANY < (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1227ltAny
1228 :: Expression commons schemas params grp from ty -- ^ expression
1229 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1230 -> Expression commons schemas params grp from (nullity 'PGbool)
1231ltAny = unsafeSubqueryExpression "ANY <"
1233-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1234-- >>> printSQL $ myRow `rowLtAll` values_ myRow
1235-- ROW(1, FALSE) ALL < (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1236rowLtAny
1237 :: SListI row
1238 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1239 -> Query commons schemas params row -- ^ subquery
1240 -> Expression commons schemas params grp from (nullity 'PGbool)
1241rowLtAny = unsafeRowSubqueryExpression "ANY <"
1243-- | >>> printSQL $ true `lteAll` values_ (true `as` #foo)
1244-- TRUE <= ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1245lteAll
1246 :: Expression commons schemas params grp from ty -- ^ expression
1247 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1248 -> Expression commons schemas params grp from (nullity 'PGbool)
1249lteAll = unsafeSubqueryExpression "<= ALL"
1251-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1252-- >>> printSQL $ myRow `rowLteAll` values_ myRow
1253-- ROW(1, FALSE) <= ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1254rowLteAll
1255 :: SListI row
1256 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1257 -> Query commons schemas params row -- ^ subquery
1258 -> Expression commons schemas params grp from (nullity 'PGbool)
1259rowLteAll = unsafeRowSubqueryExpression "<= ALL"
1261-- | >>> printSQL $ true `lteAny` values_ (true `as` #foo)
1262-- TRUE <= ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1263lteAny
1264 :: Expression commons schemas params grp from ty -- ^ expression
1265 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1266 -> Expression commons schemas params grp from (nullity 'PGbool)
1267lteAny = unsafeSubqueryExpression "<= ANY"
1269-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1270-- >>> printSQL $ myRow `rowLteAny` values_ myRow
1271-- ROW(1, FALSE) <= ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1272rowLteAny
1273 :: SListI row
1274 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1275 -> Query commons schemas params row -- ^ subquery
1276 -> Expression commons schemas params grp from (nullity 'PGbool)
1277rowLteAny = unsafeRowSubqueryExpression "<= ANY"
1279-- | >>> printSQL $ true `gtAll` values_ (true `as` #foo)
1280-- TRUE > ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1281gtAll
1282 :: Expression commons schemas params grp from ty -- ^ expression
1283 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1284 -> Expression commons schemas params grp from (nullity 'PGbool)
1285gtAll = unsafeSubqueryExpression "> ALL"
1287-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1288-- >>> printSQL $ myRow `rowGtAll` values_ myRow
1289-- ROW(1, FALSE) > ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1290rowGtAll
1291 :: SListI row
1292 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1293 -> Query commons schemas params row -- ^ subquery
1294 -> Expression commons schemas params grp from (nullity 'PGbool)
1295rowGtAll = unsafeRowSubqueryExpression "> ALL"
1297-- | >>> printSQL $ true `gtAny` values_ (true `as` #foo)
1298-- TRUE > ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1299gtAny
1300 :: Expression commons schemas params grp from ty -- ^ expression
1301 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1302 -> Expression commons schemas params grp from (nullity 'PGbool)
1303gtAny = unsafeSubqueryExpression "> ANY"
1305-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1306-- >>> printSQL $ myRow `rowGtAny` values_ myRow
1307-- ROW(1, FALSE) > ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1308rowGtAny
1309 :: SListI row
1310 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1311 -> Query commons schemas params row -- ^ subquery
1312 -> Expression commons schemas params grp from (nullity 'PGbool)
1313rowGtAny = unsafeRowSubqueryExpression "> ANY"
1315-- | >>> printSQL $ true `gteAll` values_ (true `as` #foo)
1316-- TRUE >= ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1317gteAll
1318 :: Expression commons schemas params grp from ty -- ^ expression
1319 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1320 -> Expression commons schemas params grp from (nullity 'PGbool)
1321gteAll = unsafeSubqueryExpression ">= ALL"
1323-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1324-- >>> printSQL $ myRow `rowGteAll` values_ myRow
1325-- ROW(1, FALSE) >= ALL (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1326rowGteAll
1327 :: SListI row
1328 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1329 -> Query commons schemas params row -- ^ subquery
1330 -> Expression commons schemas params grp from (nullity 'PGbool)
1331rowGteAll = unsafeRowSubqueryExpression ">= ALL"
1333-- | >>> printSQL $ true `gteAny` values_ (true `as` #foo)
1334-- TRUE >= ANY (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))
1335gteAny
1336 :: Expression commons schemas params grp from ty -- ^ expression
1337 -> Query commons schemas params '[alias ::: ty] -- ^ subquery
1338 -> Expression commons schemas params grp from (nullity 'PGbool)
1339gteAny = unsafeSubqueryExpression ">= ANY"
1341-- | >>> let myRow = 1 `as` #foo :* false `as` #bar :: NP (Aliased (Expression commons schemas params grp from)) '["foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGbool]
1342-- >>> printSQL $ myRow `rowGteAny` values_ myRow
1343-- ROW(1, FALSE) >= ANY (SELECT * FROM (VALUES (1, FALSE)) AS t ("foo", "bar"))
1344rowGteAny
1345 :: SListI row
1346 => NP (Aliased (Expression commons schemas params grp from)) row -- ^ row constructor
1347 -> Query commons schemas params row -- ^ subquery
1348 -> Expression commons schemas params grp from (nullity 'PGbool)
1349rowGteAny = unsafeRowSubqueryExpression ">= ANY"
1351-- | A `CommonTableExpression` is an auxiliary statement in a `with` clause.
1352data CommonTableExpression statement
1353 (schemas :: SchemasType)
1354 (params :: [NullityType])
1355 (commons0 :: FromType)
1356 (commons1 :: FromType) where
1357 CommonTableExpression
1358 :: Aliased (statement commons schemas params) (cte ::: common)
1359 -> CommonTableExpression statement schemas params commons (cte ::: common ': commons)
1360instance
1361 ( KnownSymbol cte
1362 , commons1 ~ (cte ::: common ': commons)
1363 ) => Aliasable cte
1364 (statement commons schemas params common)
1365 (CommonTableExpression statement schemas params commons commons1) where
1366 statement `as` cte = CommonTableExpression (statement `as` cte)
1367instance
1368 ( KnownSymbol cte
1369 , commons1 ~ (cte ::: common ': commons)
1370 ) => Aliasable cte
1371 (statement commons schemas params common)
1372 (AlignedList (CommonTableExpression statement schemas params) commons commons1) where
1373 statement `as` cte = single (statement `as` cte)
1375-- | render a `CommonTableExpression`.
1376renderCommonTableExpression
1377 :: (forall cs ss ps row. statement cs ss ps row -> ByteString) -- ^ render statement
1378 -> CommonTableExpression statement schemas params commons0 commons1 -> ByteString
1379renderCommonTableExpression renderStatement
1380 (CommonTableExpression (statement `As` alias)) =
1381 renderSQL alias <+> "AS" <+> parenthesized (renderStatement statement)
1383-- | render a non-empty `AlignedList` of `CommonTableExpression`s.
1384renderCommonTableExpressions
1385 :: (forall cs ss ps row. statement cs ss ps row -> ByteString) -- ^ render statement
1386 -> CommonTableExpression statement schemas params commons0 commons1
1387 -> AlignedList (CommonTableExpression statement schemas params) commons1 commons2
1388 -> ByteString
1389renderCommonTableExpressions renderStatement cte ctes =
1390 renderCommonTableExpression renderStatement cte <> case ctes of
1391 Done -> ""
1392 cte' :>> ctes' -> "," <+>
1393 renderCommonTableExpressions renderStatement cte' ctes'
1395-- | `with` provides a way to write auxiliary statements for use in a larger query.
1396-- These statements, referred to as `CommonTableExpression`s, can be thought of as
1397-- defining temporary tables that exist just for one query.
1398class With statement where
1399 with
1400 :: AlignedList (CommonTableExpression statement schemas params) commons0 commons1
1401 -- ^ common table expressions
1402 -> statement commons1 schemas params row
1403 -- ^ larger query
1404 -> statement commons0 schemas params row
1405instance With Query where
1406 with Done query = query
1407 with (cte :>> ctes) query = UnsafeQuery $
1408 "WITH" <+> renderCommonTableExpressions renderSQL cte ctes
1409 <+> renderSQL query