h${              ! "# $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~                                                                                                                                                                                                                                          ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! " " " " " # # # # # # # # # # # $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ % % % & & & & & & & & & & & & & & & & & & & & & ' ' ' ' ' ' ' ' ' ' ' ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * + + + + + + + + + + + + + + + + + + + + , , , , , , , , , , , , , , , , , , , - - - - - - - - . . . . . . . . . . . . . . . . . . . . . . . . . . . . / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / 0 0 0 0 0 0 0 0 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5              6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 9 9 9 9 9 9 9 9 9 9 : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :                         ; ; ; ; < < < < < < < < < < < < < = = = = = =============>>>>>>>>>>>>>>>>??????? render functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone  -.?'$squeal-postgresqlA class for rendering SQL&squeal-postgresqlParenthesize a .'squeal-postgresqlSquare bracket a (squeal-postgresqlConcatenate two s with a space between.)squeal-postgresqlComma separate a list of s.*squeal-postgresqlAdd double quotes around a .+squeal-postgresqlAdd single quotes around a $ and escape single quotes within it.,squeal-postgresqlAdd single quotes around a $ and escape single quotes within it.-squeal-postgresqlEscape quote a string..squeal-postgresqlEscape quote a string./squeal-postgresql6Comma separate the renderings of a heterogeneous list.0squeal-postgresql6Comma separate the renderings of a heterogeneous list.1squeal-postgresqlComma separate the / renderings of a heterogeneous list, dropping s.2squeal-postgresqlRender a promoted .3squeal-postgresqlRender a promoted .4squeal-postgresql Print SQL.5squeal-postgresql5! a character to prevent injection$%&'()*+,-./012345$%45&'()*+,-./0123(7  exceptions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone ,y6squeal-postgresqls that can be thrown by Squeal.7squeal-postgresqlSQL exception state8squeal-postgresql@A function connection exception9squeal-postgresql-decoding exception function and error message:squeal-postgresqlunexpected number of columns;squeal-postgresql9too few rows, expected at least and actual number of rows<squeal-postgresqlthe state of LibPQ?squeal-postgresql https://www.postgresql.org/docs/current/static/errcodes-appendix.htmlAsqueal-postgresql,A pattern for deadlock detection exceptions.Bsqueal-postgresql/A pattern for serialization failure exceptions.Csqueal-postgresql4A pattern for check constraint violation exceptions.Dsqueal-postgresql*A pattern for unique violation exceptions.Esqueal-postgresqlCatch 6s.Fsqueal-postgresqlHandle 6s.Gsqueal-postgresql return a 6 or a result.Hsqueal-postgresqlThrow 6s.Esqueal-postgresqlhandlerFsqueal-postgresqlhandler! 6789:;<=>?@ABCDEFGH6789:;DCBA<=>?@! EFGH types(c) Eitan Chatav, 2010eitan@morphism.tech experimentalNone '(./56789:<>?6Nsqueal-postgresqlFixed-length, blank padded:kind! PG (FixChar 4)PG (FixChar 4) :: PGType = 'PGchar 4Osqueal-postgresql$Variable-length text type with limit:kind! PG (VarChar 4)PG (VarChar 4) :: PGType= 'PGvarchar 4Psqueal-postgresqlP? is a 1-tuple type, useful for encoding or decoding a singletonSsqueal-postgresqlThe S newtype is an indication that the Haskell type it's applied to should be stored as a  B.9:kind! PG (FixArray ((Double, Double), (Double, Double))) squeal-postgresql checks that one type level list is a subset of another, regardless of ordering and repeats.#:kind! SubsetList '[1,2,3] '[4,5,6]$SubsetList '[1,2,3] '[4,5,6] :: Bool= 'False%:kind! SubsetList '[1,2,3] '[1,2,3,4]&SubsetList '[1,2,3] '[1,2,3,4] :: Bool= 'True):kind! SubsetList '[1,2,3] '[0,1,0,2,0,3]*SubsetList '[1,2,3] '[0,1,0,2,0,3] :: Bool= 'True#:kind! SubsetList '[1,2,3] '[3,2,1]$SubsetList '[1,2,3] '[3,2,1] :: Bool= 'True#:kind! SubsetList '[1,1,1] '[3,2,1]$SubsetList '[1,1,1] '[3,2,1] :: Bool= 'Truesqueal-postgresql checks that one type level list is a sublist of another, with the same ordering. :kind! SubList '[1,2,3] '[4,5,6]!SubList '[1,2,3] '[4,5,6] :: Bool= 'False":kind! SubList '[1,2,3] '[1,2,3,4]#SubList '[1,2,3] '[1,2,3,4] :: Bool= 'True&:kind! SubList '[1,2,3] '[0,1,0,2,0,3]'SubList '[1,2,3] '[0,1,0,2,0,3] :: Bool= 'True :kind! SubList '[1,2,3] '[3,2,1]!SubList '[1,2,3] '[3,2,1] :: Bool= 'Falsesqueal-postgresqlCalculate the  of a type level list(:kind! Length '[Char,String,Bool,Double](Length '[Char,String,Bool,Double] :: Nat= 4squeal-postgresqlIn x xs" is a constraint that proves that x is in xs.squeal-postgresqlElem is a promoted HI.squeal-postgresqlThe  class is for appending type-level list parameterized constructors such as , J, and K.squeal-postgresql is simply promoted  and is used in JOINs in Ks.squeal-postgresql( is a utility function for splitting an  list into pieces.squeal-postgresql A useful operator for ending an $ list of length at least 2 without squeal-postgresqlA list of length .  8aliases(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28:>?Jsqueal-postgresql=es enables multi-schema support by allowing a reference to a  L,  M or  N> to be qualified by their schemas. By default, a qualifier of public is provided.:{ let& alias1 :: QualifiedAlias "sch" "tab" alias1 = #sch ! #tab( alias2 :: QualifiedAlias "public" "vw" alias2 = #vw%in printSQL alias1 >> printSQL alias2:} "sch"."tab""vw"squeal-postgresql Analagous to , the constraint  defines 0 for a column alias qualified by a table alias.squeal-postgresql extends  to take lists of aliases and fields and infer a list of  subfields.squeal-postgresqlHasIn fields (alias ::: field)" is a constraint that proves that fields has a field of alias ::: field. It is used in UPDATE&s to choose which subfields to update.squeal-postgresql is like  except it also retains the original list of fields being searched, so that error messages are more useful.squeal-postgresqlHas alias fields field# is a constraint that proves that fields has a field of alias ::: field , inferring field from alias and fields.squeal-postgresqlHasUnique alias fields field# is a constraint that proves that fields is a singleton of alias ::: field.squeal-postgresqlThe $ class provides a way to scrap your  s in an  list of  expressions.squeal-postgresqlThe ) operator is used to name an expression.  is like a demoted version of .8Just "hello" `As` #hi :: Aliased Maybe ("hi" ::: String)As (Just "hello") Aliassqueal-postgresql*es are proxies for a type level string or  and have an  instance so that with -XOverloadedLabels:set -XOverloadedLabels#foobar :: Alias "foobar"Aliassqueal-postgresqlA  constraint indicates that a table qualified column is a member of the auxiliary namespace created by GROUP BY. clauses and thus, may be called in an output O without aggregating.squeal-postgresql( is an auxiliary namespace, created by GROUP BY clauses (P%), and used for typesafe aggregationsqueal-postgresqlno aggregation permittedsqueal-postgresql8aggregation required for any column which is not groupedsqueal-postgresqlThe alias operator  is like a promoted version of 4, a type level pair between an alias and some type.squeal-postgresql=let renderMaybe = fromString . maybe "Nothing" (const "Just")6renderAliased renderMaybe (Just (3::Int) `As` #an_int)"Just AS \"an_int\""squeal-postgresqlMap a function over an  expression.squeal-postgresql$printSQL (#jimbob :: Alias "jimbob")"jimbob"9 6 Postgres type system(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28:>?{squeal-postgresql5Calculate the schema and name of a user defined type.squeal-postgresql6Helper to calculate the schema of a user defined type.squeal-postgresql*Calculate the name of a user defined type.squeal-postgresqlUpdatable lists of columnssqueal-postgresql No elem of xs6 appears more than once, in the context of assignment.squeal-postgresqlUtility class for ! to provide nicer error messages.squeal-postgresqlIs a type a valid JSON type?squeal-postgresqlIs a type a valid JSON key?squeal-postgresqlA  unit type with an  instancesqueal-postgresql looks very much like the ' class. Whereas the overloaded label, ! is used for column references, s are used for enum terms. A ' is called with type application like  @"beef".squeal-postgresql2A type family to use for a single schema database.squeal-postgresqlA database contains one or more named schemas, which in turn contain tables. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.1 '[ "id" ::: 'Def :=> 'NotNull 'PGint40 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]) , "emails" ::: 'Table (/ '[ "pk_emails" ::: 'PrimaryKey '["id"] , "fk_user_id" ::: 'ForeignKey '["user_id"] "public" "users" '["id"] ] :=>4 '[ "id" ::: 'Def :=> 'NotNull 'PGint43 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint43 , "email" ::: 'NoDef :=> 'Null 'PGtext ]) ]:}squeal-postgresqlReturn type of a functionsqueal-postgresqlfunctionsqueal-postgresqlset returning functionsqueal-postgresqlPostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each index type uses a different algorithm that is best suited to different types of queries.squeal-postgresqlB-trees can handle equality and range queries on data that can be sorted into some ordering.squeal-postgresql9Hash indexes can only handle simple equality comparisons.squeal-postgresqlGiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented.squeal-postgresqlSP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches.squeal-postgresqlGIN indexes are @inverted indexes@ which are appropriate for data values that contain multiple component values, such as arrays.squeal-postgresqlBRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table.squeal-postgresqlUse  to pair the parameter types with the return type of a function.:{$type family Fn :: FunctionType where; Fn = '[ 'NotNull 'PGint4] :=> 'Returns ('NotNull 'PGint4):}squeal-postgresqlA  is a user-created type, like a ,  or .squeal-postgresql Drop all s that involve a columnsqueal-postgresql Check if a  involves a columnsqueal-postgresql checks that a schema may be found as a subset of another in a database, regardless of ordering.squeal-postgresql checks that one 0 is a subset of another, regardless of ordering.:kind! SubsetDB '["a" ::: '["d" ::: 'Typedef 'PGint2, "b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4, "d" ::: 'Typedef 'PGint2]]SubsetDB '["a" ::: '["d" ::: 'Typedef 'PGint2, "b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4, "d" ::: 'Typedef 'PGint2]] :: Bool= 'Truesqueal-postgresql checks that one 1 is a sublist of another, with the same ordering.:kind! SubDB '["a" ::: '["b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4]]SubDB '["a" ::: '["b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4]] :: Bool= 'Truesqueal-postgresql)Move an object from one schema to anothersqueal-postgresql Similar to  but no error on non-existencesqueal-postgresqlRename alias0 alias1 xs replaces the alias alias0 by alias1 in xs and is used in Q and R.squeal-postgresql Similar to  but no error on non-existencesqueal-postgresqlAlter alias x xs& replaces the type associated with an alias in xs with the type x and is used in S and T.squeal-postgresql Similar to  but no error on non-existencesqueal-postgresql Similar to  but no error on non-existencesqueal-postgresql(Drop a particular flavor of schemum typesqueal-postgresql Drop alias xs" removes the type associated with alias in xs and is used in U statements and in  ALTER TABLE V statements.squeal-postgresql Similar to / but used to replace values with the same type.squeal-postgresql Similar to  but no error on pre-existencesqueal-postgresqlCreate alias x xs adds  alias ::: x to the end of xs and is used in :W statements and in  ALTER TABLE :X.squeal-postgresql# is an idempotent that nullifies a  used to nullify the left or right hand side of an outer join in a K.squeal-postgresql# is an idempotent that nullifies a .squeal-postgresql# is an idempotent that nullifies a .squeal-postgresql is a constraint that proves a  has some NOT NULL.squeal-postgresql is a constraint that proves a  has no NULLs.squeal-postgresql&Equality constraint on the underlying  of two columns.squeal-postgresqlIntegral Postgres types.squeal-postgresqlFloating Postgres types.squeal-postgresqlNumeric Postgres types.squeal-postgresqlConvert a table to a row type.squeal-postgresql removes table constraints.squeal-postgresql removes column constraints.squeal-postgresql is a row of s. It can be thought of as a product, or horizontal gluing and is used in Ks and  Ys.squeal-postgresqlA  is a row of 7s. They correspond to Haskell record types by means of Z[ and are used in many places.:{&type family PersonRow :: RowType where PersonRow =) '[ "name" ::: 'NotNull 'PGtext) , "age" ::: 'NotNull 'PGint4) , "dateOfBirth" ::: 'Null 'PGdate ]:}squeal-postgresql encodes a row of constraints on a table as well as the types of its columns.:{)type family UsersTable :: TableType where UsersTable =/ '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>1 '[ "id" ::: 'Def :=> 'NotNull 'PGint41 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]:}squeal-postgresqlA + must reference columns that either are a  or form a  constraint.squeal-postgresqlA  is a row of s.:{6type family UsersConstraints :: TableConstraints where< UsersConstraints = '[ "pk_users" ::: 'PrimaryKey '["id"] ]:}squeal-postgresql encodes various forms of data constraints of columns in a table. s give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.squeal-postgresql is a row of s.:{-type family UsersColumns :: ColumnsType where UsersColumns =- '[ "name" ::: 'NoDef :=> 'NotNull 'PGtext- , "id" ::: 'Def :=> 'NotNull 'PGint4 ]:}squeal-postgresql encodes the allowance of DEFAULT and NULL and the base  for a column. :set -XTypeFamilies -XTypeInTypeimport GHC.TypeLitstype family IdColumn :: ColumnType where IdColumn = 'Def :=> 'NotNull 'PGint4type family EmailColumn :: ColumnType where EmailColumn = 'NoDef :=> 'Null 'PGtextsqueal-postgresql encodes the availability of DEFAULT for inserts and updates. A column can be assigned a default value. A data \] command can also request explicitly that a column be set to its default value, without having to know what that value is.squeal-postgresqlDEFAULT% is available for inserts and updatessqueal-postgresqlDEFAULT' is unavailable for inserts and updatessqueal-postgresqlThe constraint operator,  is a type level pair between a "constraint" and some type, for use in pairing an  with a  to produce a  or a  and a  to produce a .squeal-postgresql: encodes the potential presence or definite absence of a NULL allowing operations which are sensitive to such to be well typed.:kind 'Null 'PGint4'Null 'PGint4 :: NullType:kind 'NotNull ('PGvarchar 50)$'NotNull ('PGvarchar 50) :: NullTypesqueal-postgresqlNULL may be presentsqueal-postgresqlNULL is absentsqueal-postgresql. is the promoted datakind of PostgreSQL types. :kind 'PGbool'PGbool :: PGTypesqueal-postgresqllogical Boolean (true/false)squeal-postgresqlsigned two-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned eight-byte integersqueal-postgresql arbitrary precision numeric typesqueal-postgresql0single precision floating-point number (4 bytes)squeal-postgresql0double precision floating-point number (8 bytes)squeal-postgresqlcurrency amountsqueal-postgresqlfixed-length character stringsqueal-postgresql variable-length character stringsqueal-postgresql variable-length character stringsqueal-postgresqlbinary data ("byte array")squeal-postgresqldate and time (no time zone)squeal-postgresql"date and time, including time zonesqueal-postgresql calendar date (year, month, day)squeal-postgresqltime of day (no time zone)squeal-postgresql time of day, including time zonesqueal-postgresql time spansqueal-postgresqluniversally unique identifiersqueal-postgresqlIPv4 or IPv6 host addresssqueal-postgresqltextual JSON datasqueal-postgresqlbinary JSON data, decomposedsqueal-postgresqlvariable length arraysqueal-postgresqlfixed length arraysqueal-postgresqlenumerated (enum) types are data types that comprise a static, ordered set of values.squeal-postgresqla composite type represents the structure of a row or record; it is essentially just a list of field names and their data types.squeal-postgresqlA tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word.squeal-postgresql;A tsquery value stores lexemes that are to be searched for.squeal-postgresqlObject identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.squeal-postgresqlRange types are data types representing a range of values of some element type (called the range's subtype).squeal-postgresql0an escape hatch for unsupported PostgreSQL types74embedding of Haskell types into Postgres type system(c) Eitan Chatav, 2010eitan@morphism.tech experimentalNone '(./56789:<>?1squeal-postgresql extracts  of the base type of nested homogeneous tuples, up to a depth of 10 for each dimension.squeal-postgresql turns Haskell nested homogeneous tuples into a list of lengths, up to a depth of 10 for each dimension.squeal-postgresql,Calculate the names of nullary constructors.squeal-postgresqlCalculates the name of a nullary constructor, otherwise generates a type error.squeal-postgresql&Calculates constructors of a datatype.squeal-postgresql takes the  of a haskell > and if it's a simple product returns it, otherwise giving a .squeal-postgresql turns a list of Haskell s into a list of s.squeal-postgresql turns a Haskell tuple type (including record types) into the corresponding list of s.#:kind! TuplePG (Double, Maybe Char)*TuplePG (Double, Maybe Char) :: [NullType]+= '[ 'NotNull 'PGfloat8, 'Null ('PGchar 1)]squeal-postgresql turns a Haskell type into a .:kind! NullPG DoubleNullPG Double :: NullType= 'NotNull 'PGfloat8:kind! NullPG (Maybe Double)!NullPG (Maybe Double) :: NullType= 'Null 'PGfloat8squeal-postgresql applies  to the fields of a list.squeal-postgresql turns a Haskell  into a . may be applied to normal Haskell record types provided they have  and  instances;data Person = Person { name :: Strict.Text, age :: Int32 } deriving GHC.Genericinstance SOP.Generic Person#instance SOP.HasDatatypeInfo Person:kind! RowPG Person$RowPG Person :: [(Symbol, NullType)]<= '["name" ::: 'NotNull 'PGtext, "age" ::: 'NotNull 'PGint4]squeal-postgresqlThe  type family calculates the constructors of a Haskell enum type.:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo Schwarma:kind! LabelsPG Schwarma+LabelsPG Schwarma :: [Type.ConstructorName]= '["Beef", "Lamb", "Chicken"]squeal-postgresqlThe  type family embeds a subset of Haskell types as Postgres types. As an open type family,  is extensible.:kind! PG LocalTimePG LocalTime :: PGType= 'PGtimestampThe preferred way to generate s of your own type is through generalized newtype deriving or via deriving.newtype UserId = UserId {getUserId :: UUID} deriving newtype IsPG:kind! PG UserIdPG UserId :: PGType = 'PGuuid:{data Answer = Yes | No deriving stock GHC.Generic6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)% deriving IsPG via Enumerated Answer:}:kind! PG AnswerPG Answer :: PGType= 'PGenum '["Yes", "No"]:{?$squeal-postgresqlThe  of a fieldsqueal-postgresqlThe  of a squeal-postgresqlThe  of an arraysqueal-postgresqlThe  of a :set -XTypeApplicationsconn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres"%runReaderT (oidOf @'[] @'PGbool) connOid 16 finish conn  database connections(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone./squeal-postgresql.Makes a new connection to the database server.This function opens a new database connection using the parameters taken from the string conninfo.The passed string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., ' and .To specify the schema you wish to connect with, use type application.:set -XDataKinds:set -XPolyKinds:set -XTypeOperatorstype DB = '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint2])]]:set -XTypeApplications:set -XOverloadedStringsconn <- connectdb @DB "host=localhost port=5432 dbname=exampledb user=postgres password=postgres"Note that, for now, squeal doesn't offer any protection from connecting with the wrong schema!squeal-postgresql$Closes the connection to the server.squeal-postgresqlSafely  to a smaller schema.squeal-postgresqlconninfo""structured query language(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>?squeal-postgresqlThe  type is parameterized by a db 4, against which the query is type-checked, an input params Haskell , and an ouput row Haskell .A  can be run using 7^, or if  params = () using 7_. Generally, params will be a Haskell tuple or record whose entries may be referenced using positional `s and row will be a Haskell record, whose entries will be targeted using overloaded labels.( is a type family which resolves into a , so don't be fooled by the input params and output row Haskell 1s, which are converted into appropriate Postgres [] params and  rows. Use -a4 to fix actual Haskell input params and output rows.*:set -XDeriveAnyClass -XDerivingStrategiestype Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint8, "col2" ::: 'Def :=> 'NotNull 'PGtext]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{6data Row = Row { col1 :: Maybe Int64, col2 :: String } deriving stock (GHC.Generic)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):}:{let1 qry :: Query_ (Public Schema) (Int64, Bool) Row qry = select Star (from (table #tab) & where_ (#col1 .> param @1 .&& notNull (param @2)))5 stmt :: Statement (Public Schema) (Int64, Bool) Row stmt = query qry:} :type qryqry :: Query '[] '[]< '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]]- '[ 'NotNull 'PGint8, 'NotNull 'PGbool]? '["col1" ::: 'Null 'PGint8, "col2" ::: 'NotNull 'PGtext] :type stmtstmt :: Statement< '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]] (Int64, Bool) Rowsqueal-postgresqlThe process of retrieving or the command to retrieve data from a database is called a . The general  type is parameterized bylat :: FromType - scope for !b and subquery expressions,with :: FromType - scope for all c table expressions,db :: SchemasType - scope for all ds and es,params :: [NullType] - scope for all f`s,row :: RowType - return type of the .Let's see some  examples. simple query:type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let qry :: Query lat with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]' qry = select Star (from (table #tab))in printSQL qry:}SELECT * FROM "tab" AS "tab"restricted query::{ let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry =< select_ ((#col1 + #col2) `as` #col1 :* #col1 `as` #col2) ( from (table #tab)! & where_ (#col1 .> #col2) & where_ (#col2 .> 0) )in printSQL qry:}SELECT ("col1" + "col2") AS "col1", "col1" AS "col2" FROM "tab" AS "tab" WHERE (("col1" > "col2") AND ("col2" > (0 :: int4))) subquery::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (subquery (select Star (from (table #tab)) `as` #sub)))in printSQL qry:}5SELECT * FROM (SELECT * FROM "tab" AS "tab") AS "sub"limits and offsets::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (table #tab) & limit 100 & offset 2 & limit 50 & offset 2)in printSQL qry:}.SELECT * FROM "tab" AS "tab" LIMIT 50 OFFSET 4parameterized query::{let qry :: Query '[] with (Public Schema) '[ 'NotNull 'PGint4] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (table #tab) & where_ (#col1 .> param @1))in printSQL qry:}:SELECT * FROM "tab" AS "tab" WHERE ("col1" > ($1 :: int4))aggregation query::{ let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint8, "col2" ::: 'NotNull 'PGint4] qry = 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 qry:}SELECT COALESCE(sum(ALL "col2"), (0 :: int8)) AS "col1", "col1" AS "col2" FROM "tab" AS "table1" GROUP BY "col1" HAVING (sum(DISTINCT "col2") > (1 :: int8)) sorted query::{let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]? qry = select Star (from (table #tab) & orderBy [#col1 & Asc])in printSQL qry:}0SELECT * FROM "tab" AS "tab" ORDER BY "col1" ASCjoins::{type OrdersColumns =1 '[ "id" ::: 'NoDef :=> 'NotNull 'PGint44 , "price" ::: 'NoDef :=> 'NotNull 'PGfloat42 , "customer_id" ::: 'NoDef :=> 'NotNull 'PGint45 , "shipper_id" ::: 'NoDef :=> 'NotNull 'PGint4 ]:}:{type OrdersConstraints =& '["pk_orders" ::: PrimaryKey '["id"] ,"fk_customers" ::: ForeignKey '["customer_id"] "public" "customers" '["id"] ,"fk_shippers" ::: ForeignKey '["shipper_id"] "public" "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) ]:}:{type OrderRow =# '[ "price" ::: 'NotNull 'PGfloat4( , "customerName" ::: 'NotNull 'PGtext' , "shipperName" ::: 'NotNull 'PGtext ]:}:{let= qry :: Query lat with (Public OrdersSchema) params OrderRow qry = 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 qry:}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 qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select (#t1 & DotStar) (from (table (#tab `as` #t1) & crossJoin (table (#tab `as` #t2))))in printSQL qry:}9SELECT "t1".* FROM "tab" AS "t1" CROSS JOIN "tab" AS "t2"value queries::{let qry :: Query lat with db params '["col1" ::: 'NotNull 'PGtext, "col2" ::: 'NotNull 'PGbool] qry = values* ("true" `as` #col1 :* true `as` #col2), ["false" `as` #col1 :* false `as` #col2]in printSQL qry:}SELECT * FROM (VALUES ((E'true' :: text), TRUE), ((E'false' :: text), FALSE)) AS t ("col1", "col2")set operations::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (table #tab)) `unionAll` select Star (from (table #tab))in printSQL qry:}(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab") with query::{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with (2 select Star (from (table #tab)) `as` #cte1 :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"window functions::{let qry :: Query '[] with (Public Schema) db '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint8] qry = select (#col1 & Also (rank `as` #col2 `Over` (partitionBy #col1 & orderBy [#col2 & Asc]))) (from (table #tab))in printSQL qry:}SELECT "col1" AS "col1", rank() OVER (PARTITION BY "col1" ORDER BY "col2" ASC) AS "col2" FROM "tab" AS "tab"correlated subqueries::{ let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint4] qry =. select #col1 (from (table (#tab `as` #t1)) & where_ (exists (/ select Star (from (table (#tab `as` #t2))0 & where_ (#t2 ! #col2 .== #t1 ! #col1)))))in printSQL qry:}SELECT "col1" AS "col1" FROM "tab" AS "t1" WHERE EXISTS (SELECT * FROM "tab" AS "t2" WHERE ("t2"."col2" = "t1"."col1"))squeal-postgresqlThe results of two queries can be combined using the set operation  . Duplicate rows are eliminated.squeal-postgresqlThe results of two queries can be combined using the set operation 2, the disjoint union. Duplicate rows are retained.squeal-postgresqlThe results of two queries can be combined using the set operation 2, the intersection. Duplicate rows are eliminated.squeal-postgresqlThe results of two queries can be combined using the set operation 0, the intersection. Duplicate rows are retained.squeal-postgresqlThe results of two queries can be combined using the set operation 4, the set difference. Duplicate rows are eliminated.squeal-postgresqlThe results of two queries can be combined using the set operation 2, the set difference. Duplicate rows are retained.squeal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresql  with statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>? squeal-postgresqlA  is an auxiliary statement in a  clause.squeal-postgresqlWhether the contents of the WITH clause are materialized. If a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. Note: Use of  or  requires PostgreSQL version 12 or higher. For earlier versions, use : which in those earlier versions of PostgreSQL behaves as . PostgreSQL 12 both changes the default behavior as well as adds options for customizing the materialization behavior.squeal-postgresqlBy default, folding happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. Note: this is the behavior in PostgreSQL 12+. In PostgreSQL 11 and earlier, all CTEs are materialized.squeal-postgresqlYou can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query. Requires PostgreSQL 12+.squeal-postgresqlor by specifying NOT MATERIALIZED to force it to be merged into the parent query. Requires PostgreSQL 12+.squeal-postgresql provides a way to write auxiliary statements for use in a larger query. These statements, referred to as s, can be thought of as defining temporary tables that exist just for one query. can be used for a  . Multiple #s can be chained together with the   constructor   , and each  is constructed via overloaded .type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with (2 select Star (from (table #tab)) `as` #cte1 :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2")You can use data-modifying statements in . This allows you to perform several different operations in the same query. An example is:type ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]:{let manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[] manp = with (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del) (insertInto_ #products_deleted (Subquery (select Star (from (common #del)))))in printSQL manp:}WITH "del" AS (DELETE FROM "products" AS "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" AS "products_deleted" SELECT * FROM "del" AS "del"squeal-postgresqlA   can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:import Data.Monoid (Sum (..))import Data.Int (Int64):{ let) sum100 :: Statement db () (Sum Int64) sum100 = query $ withRecursive, ( values_ ((1 & astype int) `as` #n) `unionAll`$ select_ ((#n + 1) `as` #n)= (from (common #t) & where_ (#n .< 100)) `as` #t ) ( select_5 (fromNull 0 (sum_ (All #n)) `as` #getSum). (from (common #t) & groupBy Nil) ) in printSQL sum100:}WITH RECURSIVE "t" AS ((SELECT * FROM (VALUES (((1 :: int4) :: int))) AS t ("n")) UNION ALL (SELECT ("n" + (1 :: int4)) AS "n" FROM "t" AS "t" WHERE ("n" < (100 :: int4)))) SELECT COALESCE(sum(ALL "n"), (0 :: int8)) AS "getSum" FROM "t" AS "t"The general form of a recursive WITH query is always a non-recursive term, then  (or ), then a recursive term, where only the recursive term can contain a reference to the query's own output.squeal-postgresql-Force separate calculation of the WITH query.type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with ( materialized (select Star (from (table #tab)) `as` #cte1) :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS MATERIALIZED (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"Note: if the last CTE has  or  you must add `:>> Done`.!Requires PostgreSQL 12 or higher.squeal-postgresql8Force the WITH query to be merged into the parent query.type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with (2 select Star (from (table #tab)) `as` #cte1 :>> notMaterialized (select Star (from (common #cte1)) `as` #cte2) :>> Done) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS NOT MATERIALIZED (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"Note: if the last CTE has  or ' you must add `:>> Done` to finish the  .!Requires PostgreSQL 12 or higher.squeal-postgresqlaliased statementsqueal-postgresql!materialization of the CTE outputsqueal-postgresqlcommon table expressionssqueal-postgresql larger querysqueal-postgresqlrecursive querysqueal-postgresql larger querysqueal-postgresqlCTEsqueal-postgresqlCTE   from clauses(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>?squeal-postgresqlA ? can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these.squeal-postgresqlA real  is a table from the database.squeal-postgresql derives a table from a ?. The subquery may not reference columns provided by preceding  items. Use !b> if the subquery must reference columns provided by preceding  items.squeal-postgresql derives a table from a .squeal-postgresql0 derives a table from a common table expression.squeal-postgresql(renamable) table aliassqueal-postgresqlaliased squeal-postgresql(renamable) view aliassqueal-postgresql)(renamable) common table expression aliasoptional expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(./squeal-postgresql is either  or 2ting of a value, parameterized by an appropriate .squeal-postgresqlUse the  value for a column.squeal-postgresql a value for a column.squeal-postgresql pattern analagous to .squeal-postgresqlMap a function over an  expression. expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>?{squeal-postgresqlIntersection operatorsqueal-postgresqlContained by operatorssqueal-postgresqlA  RankNType for functions with a variable-length list of homogeneous arguments and at least 1 more argument.squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType for functions with a fixed-length list of heterogeneous arguments. Use the . operator to end your argument lists, like so.printSQL (unsafeFunctionN "fun" (true :* false :* localTime *: true))!fun(TRUE, FALSE, LOCALTIME, TRUE)squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType for functions with a single argument. These could be either function calls or unary operators. This is a subtype of the usual Haskell function type gh9, indeed a subcategory as it is closed under the usual gi and gj.squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType for binary operators.squeal-postgresqlAn  is a closed  . It is a F RankNType but don't be scared. Think of it as an expression which sees no namespaces, so you can't use parameters or alias references. It can be used as a simple piece of more complex s.squeal-postgresql;s are used in a variety of contexts, such as in the target k of the l" command, as new column values in ,m or ,n, or in search os in a number of commands.The expression syntax allows the calculation of values from primitive expression using arithmetic, logical, and other operations.The type parameters of  arelat ::  , the from+ clauses of any lat queries in which the % is a correlated subquery expression;with ::  , the ps that are in scope for the ;grp ::  , the  of the from clause which may limit which columns may be referenced by alias;db ::  ;, the schemas of your database that are in scope for the ;from ::  , the from clause which the ) may use to reference columns by alias;ty ::  , the type of the .squeal-postgresql;printSQL (unsafeFunctionVar "greatest" [true, null_] false)greatest(TRUE, NULL, FALSE)squeal-postgresql)printSQL $ unsafeBinaryOp "OR" true false(TRUE OR FALSE)squeal-postgresql"printSQL $ unsafeLeftOp "NOT" true (NOT TRUE)squeal-postgresql-printSQL $ true & unsafeRightOp "IS NOT TRUE"(TRUE IS NOT TRUE)squeal-postgresql"printSQL $ unsafeFunction "f" truef(TRUE)squeal-postgresql1Call a user defined function of a single variable>type Fn = '[ 'Null 'PGint4] :=> 'Returns ('NotNull 'PGnumeric)&type Schema = '["fn" ::: 'Function Fn]:{let fn :: Fun (Public Schema) ('Null 'PGint4) ('NotNull 'PGnumeric) fn = function #fnin printSQL (fn 1):}"fn"((1 :: int4))squeal-postgresqlprintSQL $ unsafeFunctionN "f" (currentTime :* localTimestamp :* false *: inline 'a')9f(CURRENT_TIME, LOCALTIMESTAMP, FALSE, (E'a' :: char(1)))squeal-postgresql*Call a user defined multivariable functiontype Fn = '[ 'Null 'PGint4, 'Null 'PGbool] :=> 'Returns ('NotNull 'PGnumeric)&type Schema = '["fn" ::: 'Function Fn]:{let fn :: FunN (Public Schema) '[ 'Null 'PGint4, 'Null 'PGbool] ('NotNull 'PGnumeric) fn = functionN #fnin printSQL (fn (1 *: true)):}"fn"((1 :: int4), TRUE)squeal-postgresqlinputssqueal-postgresqlmust have at least 1 inputsqueal-postgresqloutputsqueal-postgresqlinputssqueal-postgresqloutputsqueal-postgresqlinputsqueal-postgresqloutputsqueal-postgresql left inputsqueal-postgresql right inputsqueal-postgresqloutputsqueal-postgresqlcannot reference aliasessqueal-postgresql function namesqueal-postgresqlfunction alias44values statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>?Zsqueal-postgresql 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 lat with db '[] RowprintSQL querySELECT * FROM (VALUES ((1 :: int4), (E'one' :: text))) AS t ("a", "b")squeal-postgresql computes a row value or set of row values specified by value expressions.squeal-postgresqlWhen more than one row is specified, all the rows must must have the same number of elementssqueal-postgresqlone row of valuesset returning functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>? squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType4 for set returning functions with multiple argument.squeal-postgresqlA  RankNType- for set returning functions with 1 argument.squeal-postgresql>Escape hatch for a set returning function of a single variablesqueal-postgresql?Call a user defined set returning function of a single variabletype Fn = '[ 'Null 'PGbool] :=> 'ReturnsTable '["ret" ::: 'NotNull 'PGnumeric]&type Schema = '["fn" ::: 'Function Fn]:{let fn :: SetFun (Public Schema) ('Null 'PGbool) ("fn" ::: '["ret" ::: 'NotNull 'PGnumeric]) fn = setFunction #fnin printSQL (fn true):} "fn"(TRUE)squeal-postgresql7Escape hatch for a multivariable set returning functionsqueal-postgresql8Call a user defined multivariable set returning functiontype Fn = '[ 'Null 'PGbool, 'Null 'PGtext] :=> 'ReturnsTable '["ret" ::: 'NotNull 'PGnumeric]&type Schema = '["fn" ::: 'Function Fn]:{ let fn :: SetFunN (Public Schema)$ '[ 'Null 'PGbool, 'Null 'PGtext]/ ("fn" ::: '["ret" ::: 'NotNull 'PGnumeric]) fn = setFunctionN #fnin printSQL (fn (true *: "hi")):}"fn"(TRUE, (E'hi' :: text))squeal-postgresql generateSeries (start :* stop)"Generate a series of values, from start to stop with a step size of one,printSQL (generateSeries @'PGint4 (1 *: 10))*generate_series((1 :: int4), (10 :: int4))squeal-postgresql *generateSeriesStep (start :* stop *: step)"Generate a series of values, from start to stop with a step size of step6printSQL (generateSeriesStep @'PGint8 (2 :* 100 *: 2))8generate_series((2 :: int8), (100 :: int8), (2 :: int8))squeal-postgresql /generateSeriesTimestamp (start :* stop *: step)&Generate a series of timestamps, from start to stop with a step size of step:{let start = now" stop = now !+ interval_ 10 Years step = interval_ 1 Months=in printSQL (generateSeriesTimestamp (start :* stop *: step)):}generate_series(now(), (now() + (INTERVAL '10.000 years')), (INTERVAL '1.000 months')) squeal-postgresqlinputsqueal-postgresqloutputsqueal-postgresqlinputsqueal-postgresqloutputsqueal-postgresqloutputsqueal-postgresqlset returning functionsqueal-postgresqlfunction aliassqueal-postgresqlset returning functionsqueal-postgresqlfunction aliassqueal-postgresqlset returning functionsqueal-postgresqlset returning functionsqueal-postgresqlset returning function  type expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(/8<>?1squeal-postgresqlLike PGTyped but also accounts for null.squeal-postgresqlLike PGTyped but also accounts for null.squeal-postgresqls are used in W commands.squeal-postgresqlLift  to a fieldsqueal-postgresql is a demoted version of a squeal-postgresqls are used in s and W commands.squeal-postgresqlprintSQL $ true & cast int4(TRUE :: int4)squeal-postgresqlA safe version of * which just matches a value with its type.printSQL (1 & astype int)((1 :: int4) :: int)squeal-postgresql" will add a type annotation to an = which can be useful for fixing the storage type of a value.printSQL (inferredtype true)(TRUE :: bool)squeal-postgresql The enum or composite type in a  can be expressed by its alias.squeal-postgresql&The composite type corresponding to a + definition can be expressed by its alias.squeal-postgresql&The composite type corresponding to a + definition can be expressed by its alias.squeal-postgresqllogical Boolean (true/false)squeal-postgresqlsigned two-byte integersqueal-postgresqlsigned two-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned eight-byte integersqueal-postgresqlsigned eight-byte integersqueal-postgresql arbitrary precision numeric typesqueal-postgresql0single precision floating-point number (4 bytes)squeal-postgresql0single precision floating-point number (4 bytes)squeal-postgresql0double precision floating-point number (8 bytes)squeal-postgresql0double precision floating-point number (8 bytes)squeal-postgresqlcurrency amountsqueal-postgresql variable-length character stringsqueal-postgresqlfixed-length character stringsqueal-postgresqlfixed-length character stringsqueal-postgresql variable-length character stringsqueal-postgresql variable-length character stringsqueal-postgresqlbinary data ("byte array")squeal-postgresqldate and time (no time zone)squeal-postgresql"date and time, including time zonesqueal-postgresql"date and time, including time zonesqueal-postgresql calendar date (year, month, day)squeal-postgresqltime of day (no time zone)squeal-postgresql time of day, including time zonesqueal-postgresql time of day, including time zonesqueal-postgresql time spansqueal-postgresqluniversally unique identifiersqueal-postgresqlIPv4 or IPv6 host addresssqueal-postgresqltextual JSON datasqueal-postgresqlbinary JSON data, decomposedsqueal-postgresqlvariable length arraysqueal-postgresqlfixed length arrayrenderSQL (fixarray @'[2] json) "json[2]"squeal-postgresqltext search querysqueal-postgresqltext search documentsqueal-postgresqlObject identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.squeal-postgresqlRange of integersqueal-postgresqlRange of bigintsqueal-postgresqlRange of numericsqueal-postgresql$Range of timestamp without time zonesqueal-postgresql!Range of timestamp with time zonesqueal-postgresql Range of datesqueal-postgresqlAnonymous composite recordsqueal-postgresqlSpecify  from a Haskell type.printSQL $ pgtypeFrom @StringtextprintSQL $ pgtypeFrom @Doublefloat8squeal-postgresqlused in W0 commands as a column constraint to note that NULL may be present in a columnsqueal-postgresqlused in W- commands as a column constraint to ensure NULL is not present in a columnsqueal-postgresqlused in W3 commands as a column constraint to give a defaultsqueal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresql Specify null  from a Haskell type.'printSQL $ nulltypeFrom @(Maybe String)textprintSQL $ nulltypeFrom @Doublefloat8squeal-postgresqlSpecify  from a Haskell type.)printSQL $ columntypeFrom @(Maybe String) text NULL!printSQL $ columntypeFrom @Doublefloat8 NOT NULL squeal-postgresqltype to cast assqueal-postgresqlvalue to convertsqueal-postgresqltype to specify assqueal-postgresqlvaluesqueal-postgresqlvaluesqueal-postgresql type aliassqueal-postgresql table aliassqueal-postgresql view aliassqueal-postgresqltypesqueal-postgresqltypesqueal-postgresql default valuesqueal-postgresql column type!date/time functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone ./8 squeal-postgresqlA  to use in  construction.squeal-postgresql&Affine space operations on time types.squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !+ 365)(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) + (365 :: int4))squeal-postgresql+printSQL (365 +! makeDate (1984 :* 7 *: 3))((365 :: int4) + make_date((1984 :: int4), (7 :: int4), (3 :: int4)))squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !- 365)(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) - (365 :: int4))squeal-postgresql4printSQL (makeDate (1984 :* 7 *: 3) !-! currentDate)(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) - CURRENT_DATE)squeal-postgresql&Calculate the return time type of the  .squeal-postgresqlprintSQL currentDate CURRENT_DATEsqueal-postgresqlprintSQL currentTime CURRENT_TIMEsqueal-postgresqlprintSQL currentTimestampCURRENT_TIMESTAMPsqueal-postgresqlprintSQL localTime LOCALTIMEsqueal-postgresqlprintSQL localTimestampLOCALTIMESTAMPsqueal-postgresql%Current date and time (equivalent to ) printSQL nownow()squeal-postgresql+Create date from year, month and day fields$printSQL (makeDate (1984 :* 7 *: 3))3make_date((1984 :: int4), (7 :: int4), (3 :: int4))squeal-postgresql0Create time from hour, minute and seconds fields%printSQL (makeTime (8 :* 15 *: 23.5))6make_time((8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresqlCreate timestamp from year, month, day, hour, minute and seconds fields=printSQL (makeTimestamp (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))make_timestamp((2013 :: int4), (7 :: int4), (15 :: int4), (8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresqlCreate timestamp with time zone from year, month, day, hour, minute and seconds fields; the current time zone is used?printSQL (makeTimestamptz (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))make_timestamptz((2013 :: int4), (7 :: int4), (15 :: int4), (8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresql1Truncate a timestamp with the specified precisionprintSQL $ dateTrunc Quarter (makeTimestamp (2010 :* 5 :* 6 :* 14 :* 45 *: 11.4))date_trunc('quarter', make_timestamp((2010 :: int4), (5 :: int4), (6 :: int4), (14 :: int4), (45 :: int4), (11.4 :: float8)))squeal-postgresqlConvert a timestamp, timestamp with time zone, or time of day with timezone to a different timezone using an interval offset or specific timezone denoted by text. When using the interval offset, the interval duration must be less than one day or 24 hours.printSQL $ (makeTimestamp (2009 :* 7 :* 22 :* 19 :* 45 *: 11.4)) `atTimeZone` (interval_ 8 Hours)(make_timestamp((2009 :: int4), (7 :: int4), (22 :: int4), (19 :: int4), (45 :: int4), (11.4 :: float8)) AT TIME ZONE (INTERVAL '8.000 hours')):{ let" timezone :: Expr (null 'PGtext) timezone = "EST" in printSQL $ (makeTimestamptz (2015 :* 9 :* 15 :* 4 :* 45 *: 11.4)) `atTimeZone` timezone:}(make_timestamptz((2015 :: int4), (9 :: int4), (15 :: int4), (4 :: int4), (45 :: int4), (11.4 :: float8)) AT TIME ZONE (E'EST' :: text))squeal-postgresqlprintSQL $ interval_ 7 Days(INTERVAL '7.000 days')!!6666#text search functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone /+squeal-postgresqlq matches tsquery ?squeal-postgresqlAND r s togethersqueal-postgresqlOR r s togethersqueal-postgresql negate a rsqueal-postgresqlr followed by rsqueal-postgresqlconvert array of lexemes to qsqueal-postgresqlnumber of lexemes in qsqueal-postgresql$number of lexemes plus operators in rsqueal-postgresqlproduce r ignoring punctuationsqueal-postgresqlproduce r2 that searches for a phrase, ignoring punctuationsqueal-postgresqlproduce r from a web search style querysqueal-postgresqlget indexable part of a rsqueal-postgresqlnormalize words and convert to rsqueal-postgresqlreduce document text to qsqueal-postgresql!assign weight to each element of qsqueal-postgresql"remove positions and weights from qsqueal-postgresql#jsonToTSvector (document *: filter)> reduce each value in the document, specified by filter to a q, and then concatenate those in document order to produce a single q. filter is a s array, that enumerates what kind of elements need to be included into the resulting q. Possible values for filter are "string" (to include all string values), "numeric" (to include all numeric values in the string format), "boolean" (to include all Boolean values in the string format "true"/"false"), "key" (to include all keys) or "all" (to include all above). These values can be combined together to include, e.g. all string and numeric values.squeal-postgresql$jsonbToTSvector (document *: filter)> reduce each value in the document, specified by filter to a q, and then concatenate those in document order to produce a single q. filter is a t array, that enumerates what kind of elements need to be included into the resulting q. Possible values for filter are "string" (to include all string values), "numeric" (to include all numeric values in the string format), "boolean" (to include all Boolean values in the string format "true"/"false"), "key" (to include all keys) or "all" (to include all above). These values can be combined together to include, e.g. all string and numeric values.squeal-postgresqlremove given lexeme from qsqueal-postgresql-select only elements with given weights from qsqueal-postgresql display a r matchtext functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone /2squeal-postgresqlprintSQL $ lower "ARRRGGG"lower((E'ARRRGGG' :: text))squeal-postgresqlprintSQL $ upper "eeee"upper((E'eeee' :: text))squeal-postgresqlprintSQL $ charLength "four"char_length((E'four' :: text))squeal-postgresqlThe  expression returns true if the string matches the supplied pattern. If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case  acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.printSQL $ "abc" `like` "a%"'((E'abc' :: text) LIKE (E'a%' :: text))squeal-postgresqlThe key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale.printSQL $ "abc" `ilike` "a%"(((E'abc' :: text) ILIKE (E'a%' :: text))squeal-postgresql9Determines the location of the substring match using the  function. Returns the 1-based index of the first match, if no match exists the function returns (0).+printSQL $ strpos ("string" *: "substring")3strpos((E'string' :: text), (E'substring' :: text))squeal-postgresqlOver the string in the first argument, replace all occurrences of the second argument with the third and return the modified string./printSQL $ replace ("string" :* "from" *: "to")replace((E'string' :: text), (E'from' :: text), (E'to' :: text))sort expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone  '(/2>89squeal-postgresqlThe $ clause causes the result rows of a Y) to be sorted according to the specified (s). If two rows are equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.You can also control the order in which rows are processed by window functions using  within u.squeal-postgresqls are used by & to optionally sort the results of a v.  or  set the sort direction of a  result column to ascending or descending. Ascending order puts smaller values first, where "smaller" is defined in terms of the &w operator. Similarly, descending order is determined with the &x operator. , ,  and  options are used to determine whether nulls appear before or after non-null values in the sort ordering of a  result column.squeal-postgresqlsortssqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort by  range types and functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone /5678:<>?B#squeal-postgresqlA = datatype that comprises connected subsets of the real line.squeal-postgresql The type of  for a .squeal-postgresql unboundedsqueal-postgresql inclusivesqueal-postgresql exclusivesqueal-postgresql Construct a (printSQL $ range tstzrange (atLeast now)tstzrange(now(), NULL, '[)')(printSQL $ range numrange (0 <=..< 2*pi);numrange((0.0 :: numeric), ((2.0 :: numeric) * pi()), '[)') printSQL $ range int4range Empty('empty' :: int4range)squeal-postgresqlFinite  constructorsqueal-postgresqlFinite  constructorsqueal-postgresqlFinite  constructorsqueal-postgresqlFinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlA point on the linesqueal-postgresqlThe  linesqueal-postgresqlrange is contained bysqueal-postgresqlcontains rangesqueal-postgresql?strictly left of, return false when an empty range is involvedsqueal-postgresqlstrictly right of, return false when an empty range is involvedsqueal-postgresqldoes not extend to the right of, return false when an empty range is involvedsqueal-postgresqldoes not extend to the left of, return false when an empty range is involvedsqueal-postgresql?V squeal-postgresqlA  constraint to ensure that a Haskell type is a product type, has a , and all its terms have known Oids, and can be encoded to corresponding Postgres types.squeal-postgresqlParameter encoding for  tuples and records.$import qualified GHC.Generics as GHC$import qualified Generics.SOP as SOP?data Two = Two Int16 String deriving (GHC.Generic, SOP.Generic)conn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[] '[ 'NotNull 'PGint2, 'NotNull 'PGtext] Two encode = genericParams9in runReaderT (runEncodeParams encode (Two 2 "two")) conn:},K (Just "\NUL\STX") :* K (Just "two") :* Nil:{let encode :: EncodeParams '[] '[ 'NotNull 'PGint2, 'NotNull 'PGtext] (Int16, String) encode = genericParams6in runReaderT (runEncodeParams encode (2, "two")) conn:},K (Just "\NUL\STX") :* K (Just "two") :* Nil finish connsqueal-postgresql$ describes an encoding of a Haskell  into a list of parameter s.conn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[] '[ 'NotNull 'PGint2, 'NotNull ('PGchar 1), 'NotNull 'PGtext] (Int16, (Char, String))$ encode = fst .* fst.snd *. snd.snd;in runReaderT (runEncodeParams encode (1,('a',"foo"))) conn:} x) .* (\(_,y,_) -> y) *. (\(_,_,z) -> z)in runReaderT (runEncodeParams encode (Nothing, "foo", 'z')) conn:}2K Nothing :* K (Just "foo") :* K (Just "z") :* Nil finish connsqueal-postgresqlEncode 1 parameter.conn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let7 encode :: EncodeParams '[] '[ 'NotNull 'PGint4] Int32 encode = aParam0in runReaderT (runEncodeParams encode 1776) conn:}"K (Just "\NUL\NUL\ACK\240") :* Nil finish connsqueal-postgresqlAppend parameter encodings.conn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[]* '[ 'NotNull 'PGint4, 'NotNull 'PGint2] (Int32, Int16) encode = contramap fst aParam `appendParams` contramap snd aParam5in runReaderT (runEncodeParams encode (1776, 2)) conn:}9K (Just "\NUL\NUL\ACK\240") :* K (Just "\NUL\STX") :* Nil finish connsqueal-postgresqlheadsqueal-postgresqltailsqueal-postgresqlsecond to lastsqueal-postgresqllastsqueal-postgresqlleftsqueal-postgresqlright58decoding of result values(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone ./<>?msqueal-postgresqlA  constraint to ensure that a Haskell type is a record type, has a , and all its fields and can be decoded from corresponding Postgres fields.squeal-postgresqlRow decoder for  records.$import qualified GHC.Generics as GHC$import qualified Generics.SOP as SOPdata Two = Two {frst :: Int16, scnd :: String} deriving (Show, GHC.Generic, SOP.Generic, SOP.HasDatatypeInfo):{let decode :: DecodeRow '[ "frst" ::: 'NotNull 'PGint2, "scnd" ::: 'NotNull 'PGtext] Two decode = genericRowin runDecodeRow decode (SOP.K (Just "\NUL\STX") :* SOP.K (Just "two") :* Nil):}$Right (Two {frst = 2, scnd = "two"})squeal-postgresql& describes a decoding of a PostgreSQL  into a Haskell .' has an interface given by the classes , , , , ,  , and .:set -XOverloadedLabels:{let decode :: DecodeRow '[ "fst" ::: 'NotNull 'PGint2, "snd" ::: 'NotNull ('PGchar 1)] (Int16, Char) decode = (,) <$> #fst <*> #sndin runDecodeRow decode (SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* Nil):} Right (1,'a')There is also an  instance for  )s, useful for decoding outer joined rows.:{let decode :: DecodeRow< '[ "fst" ::: 'Null 'PGint2, "snd" ::: 'Null ('PGchar 1)] (Maybe (Int16, Char)), decode = runMaybeT $ (,) <$> #fst <*> #sndin runDecodeRow decode (SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* Nil):}Right (Just (1,'a'))squeal-postgresqlA * constraint gives a decoding to a Haskell  from the binary format of a PostgreSQL fixed-length array. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint lifts the  parser to a decoding of a (Symbol, NullityType) to a  , decoding s to (s. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint lifts the  parser to a decoding of a  NullityType to a  , decoding s to (s. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint gives a parser from the binary format of a PostgreSQL  into a Haskell .squeal-postgresql:set -XMultiParamTypeClasses -XGeneralizedNewtypeDeriving -XDerivingStrategies -XDerivingVia -XUndecidableInstancesimport GHC.Generics as GHC:{*newtype UserId = UserId { getId :: Int64 }! deriving newtype (IsPG, FromPG):}:{data Complex = Complex { real :: Double , imaginary :: Double } deriving stock GHC.Generic8 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)1 deriving (IsPG, FromPG) via Composite Complex:}:{,data Direction = North | South | East | West deriving stock GHC.Generic6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)2 deriving (IsPG, FromPG) via Enumerated Direction:}squeal-postgresql Converts a  type from postgresql-binary for use in the  method of .squeal-postgresql:set -XTypeFamilies:{data Complex = Complex { real :: Double , imaginary :: Double }instance IsPG Complex where# type PG Complex = 'PGcomposite '[ "re" ::: 'NotNull 'PGfloat8, "im" ::: 'NotNull 'PGfloat8]instance FromPG Complex where fromPG = rowValue $ do re <- #re im <- #im. return Complex {real = re, imaginary = im}:}squeal-postgresqlRun a .squeal-postgresql2Append two row decoders with a combining function.import GHC.Generics as GHC:{ &data L = L {fst :: Int16, snd :: Char}$ deriving stock (GHC.Generic, Show)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)'data R = R {thrd :: Bool, frth :: Bool}$ deriving stock (GHC.Generic, Show)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo) type Row = '[ "fst" ::: 'NotNull 'PGint2,! "snd" ::: 'NotNull ('PGchar 1), "thrd" ::: 'NotNull 'PGbool, "frth" ::: 'NotNull 'PGbool]:}:{ let decode :: DecodeRow Row (L,R)/ decode = appendRows (,) genericRow genericRow row4 = SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* SOP.K (Just "\NUL") :* SOP.K (Just "\NUL") :* Nilin runDecodeRow decode row4:}=Right (L {fst = 1, snd = 'a'},R {thrd = False, frth = False})squeal-postgresql:Cons a column and a row decoder with a combining function.:{let decode :: DecodeRow '["fst" ::: 'NotNull 'PGtext, "snd" ::: 'NotNull 'PGint2, "thrd" ::: 'NotNull ('PGchar 1)] (String, (Int16, Char))4 decode = consRow (,) #fst (consRow (,) #snd #thrd)in runDecodeRow decode (SOP.K (Just "hi") :* SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* Nil):}Right ("hi",(1,'a'))squeal-postgresqlSmart constructor for a .squeal-postgresql:{ &data Dir = North | East | South | Westinstance IsPG Dir where; type PG Dir = 'PGenum '["north", "south", "east", "west"]instance FromPG Dir where fromPG = enumValue $ label @"north" North :* label @"south" South :* label @"east" East :* label @"west" West:}squeal-postgresqlfieldssqueal-postgresqlcombining functionsqueal-postgresql left decodersqueal-postgresql right decodersqueal-postgresqlcombining functionsqueal-postgresql alias of headsqueal-postgresql tail decodersqueal-postgresqllabelsresults(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone  '(>?wsqueal-postgresqlA $ operation extracts values from the  of a .y4 operation. There is no need to define instances of . An instance of  implies an instance of . However, the constraint  does not imply the constraint .squeal-postgresql5Get a row corresponding to a given row number from a <, throwing an exception if the row number is out of bounds.squeal-postgresqlGet all rows from a .squeal-postgresql%Get the first row if possible from a .squeal-postgresql8Returns the number of rows (tuples) in the query result.squeal-postgresql;Returns the number of columns (fields) in the query result.squeal-postgresqlReturns the command status tag from the SQL command that generated the . Commonly this is just the name of the command, but it might include additional data such as the number of rows processed.squeal-postgresqlReturns the number of rows affected by the SQL command. This function returns  the number of rows affected by the SQL statement that generated the . This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement,or an EXECUTE of a prepared query that contains an INSERT, UPDATE, or DELETE statement. If the command that generated the PGresult was anything else,  returns .squeal-postgresql)Returns the result status of the command.squeal-postgresql Check if a 's status is either  or  otherwise  a 7.squeal-postgresqlReturns the error message most recently generated by an operation on the connection.squeal-postgresqlReturns the error code most recently generated by an operation on the connection. https://www.postgresql.org/docs/current/static/errcodes-appendix.htmlsqueal-postgresqls are generated by executing 7zs in a .y.They contain an underlying  and a .squeal-postgresql:Intended to be used for unfolding in streaming libraries, 8 takes a total number of rows (which can be found with  ) and a 2 and given a row number if it's too large returns >, otherwise returning the row along with the next row number.squeal-postgresqlLifts actions on results from LibPQ.squeal-postgresqltotal number of rowssqueal-postgresqlresultsqueal-postgresql row numberout-of-line parameters(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(/>?{%squeal-postgresqlA  constraint is used to indicate a value that is supplied externally to a SQL statement. 7{, 7| and 7} support specifying data values separately from the SQL command string, in which case 3s are used to refer to the out-of-line data values.squeal-postgresql takes a  using type application and a .printSQL (parameter @1 int4) ($1 :: int4)squeal-postgresql takes a 7 using type application and for basic types, infers a .$printSQL (param @1 @('Null 'PGint4)) ($1 :: int4)squeal-postgresqlparammath functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone /squeal-postgresql:{let% expression :: Expr (null 'PGfloat4) expression = atan2_ (pi *: 2)in printSQL expression:}atan2(pi(), (2.0 :: float4))squeal-postgresql&integer division, truncates the result:{let expression :: Expression grp lat with db params from (null 'PGint2) expression = 5 `quot_` 2in printSQL expression:}((5 :: int2) / (2 :: int2))squeal-postgresqlremainder upon integer division:{let expression :: Expression grp lat with db params from (null 'PGint2) expression = 5 `rem_` 2in printSQL expression:}((5 :: int2) % (2 :: int2))squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGfloat4) expression = trunc piin printSQL expression:} trunc(pi())squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGfloat4) expression = round_ piin printSQL expression:} round(pi())squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGfloat4) expression = ceiling_ piin printSQL expression:} ceiling(pi())!logical expressions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone /squeal-postgresqlA  is an , which can evaluate to ,  or ~1. This is because SQL uses a three valued logic.squeal-postgresql printSQL trueTRUEsqueal-postgresqlprintSQL falseFALSEsqueal-postgresqlprintSQL $ not_ true (NOT TRUE)squeal-postgresqlprintSQL $ true .&& false(TRUE AND FALSE)squeal-postgresqlprintSQL $ true .|| false(TRUE OR FALSE)squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGint2)9 expression = caseWhenThenElse [(true, 1), (false, 2)] 3in printSQL expression:}CASE WHEN TRUE THEN (1 :: int2) WHEN FALSE THEN (2 :: int2) ELSE (3 :: int2) ENDsqueal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGint2)" expression = ifThenElse true 1 0in printSQL expression:}4CASE WHEN TRUE THEN (1 :: int2) ELSE (0 :: int2) ENDsqueal-postgresqlwhens and thenssqueal-postgresqlelsesqueal-postgresqlthensqueal-postgresqlelse32 intermediate table expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>?squeal-postgresqlTo prevent the operation from 3 for other transactions to commit, use either the  or  option.squeal-postgresql%wait for other transactions to commitsqueal-postgresql%reports an error, rather than waitingsqueal-postgresql?any selected rows that cannot be immediately locked are skippedsqueal-postgresqlRow-level locks, which are listed as below with the contexts in which they are used automatically by PostgreSQL. Note that a transaction can hold conflicting locks on the same row, even in different subtransactions; but other than that, two transactions can never hold conflicting locks on the same row. Row-level locks do not affect data querying; they block only writers and lockers to the same row. Row-level locks are released at transaction end or during savepoint rollback.squeal-postgresql " causes the rows retrieved by the +l statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt 1n, 3, +l  , +l  , +l   or +l   of these rows will be blocked until the current transaction ends; conversely, +l   will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a 0 or 0 transaction, however, an error will be thrown if a row to be locked has changed since the transaction started.The  # lock mode is also acquired by any 3 a row, and also by an  that modifies the values on certain columns. Currently, the set of columns considered for the 1n case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.squeal-postgresqlBehaves similarly to  , except that the lock acquired is weaker: this lock will not block +l   commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any 1n that does not acquire a   lock.squeal-postgresqlBehaves similarly to  $, except that the lock is weaker: +l   is blocked, but not +l  . A key-shared lock blocks other transactions from performing 3 or any 1n. that changes the key values, but not other , and neither does it prevent +l  , +l  , or +l  .squeal-postgresqlIf specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the +l3 are simply read as usual. A locking clause with a  table list affects all tables used in the statement. If a locking clause is applied to a  or $, it affects all tables used in the  or ). However, these clauses do not apply to  queries referenced by the primary query. If you want row locking to occur within a  query, specify a  within the  query.squeal-postgresqlA ; is used to eliminate groups that are not of interest. An   may only use  while a   must use % whose conditions are combined with .squeal-postgresqlA  indicates the  of a .squeal-postgresqls are used in  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.squeal-postgresqlA 4 computes a table. The table expression contains a " that is optionally followed by a , , , ,   and . 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.squeal-postgresqlA table reference that can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these.squeal-postgresql)optional search coditions, combined with . After the processing of the  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 ; this is not required, but otherwise the WHERE clause will be fairly useless.squeal-postgresqlThe  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.squeal-postgresql"If a table has been grouped using 0, but only certain groups are of interest, the  can be used, much like a ;, to eliminate groups from the result. Expressions in the  can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function).squeal-postgresqlThe . is for optional sorting. When more than one  is specified, the later (right) values are used to sort rows that are equal according to the earlier (left) values.squeal-postgresqlThe  is combined with  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).squeal-postgresqlThe  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.squeal-postgresql) can be added to a table expression with .squeal-postgresqlA  generates a  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  may be transformed by , , , ,  and  , using the  operator to match the left-to-right sequencing of their placement in SQL.squeal-postgresqlA  is an endomorphism of (s which adds a search condition to the .squeal-postgresqlA  is a transformation of s which switches its  from  to . Use  groupBy Nil/ to perform a "grand total" aggregation query.squeal-postgresqlA  is an endomorphism of (s which adds a search condition to the .squeal-postgresqlA  is an endomorphism of s which adds to the .squeal-postgresqlAn  is an endomorphism of s which adds to the .squeal-postgresqlAdd a  to a  . Multiple s can be written if it is necessary to specify different locking behavior for different tables. If the same table is mentioned (or implicitly affected) by more than one locking clause, then it is processed as if it was only specified by the strongest one. Similarly, a table is processed as  if that is specified in any of the clauses affecting it. Otherwise, it is processed as  if that is specified in any of the clauses affecting it. Further, a / cannot be added to a grouped table expression.squeal-postgresql Render a .squeal-postgresql Render a squeal-postgresql lock strengthsqueal-postgresql table listsqueal-postgresql wait or notsqueal-postgresqltable referencesqueal-postgresqlfiltering conditionsqueal-postgresqlgrouped columnssqueal-postgresqlhaving conditionsqueal-postgresqllimit parametersqueal-postgresqloffset parametersqueal-postgresqlrow-level lock%%! Squeal joins(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(-./28<>?7 squeal-postgresqlA   is the right hand side of a  ,  ,  ,  ,   join of s. squeal-postgresqlleft & cross (Join 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. squeal-postgresqlleft & 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. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresqlleft & inner (Join right) on&. The joined table is filtered by the on condition. squeal-postgresqlleft & innerJoin right on&. The joined table is filtered by the on condition. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql left & leftOuter (Join 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 right7, 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. squeal-postgresqlleft & 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 right7, 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. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql!left & rightOuter (Join 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 left7, 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. squeal-postgresqlleft & 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 left7, 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. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql left & fullOuter (Join 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 right7, 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 left2, a joined row with null values in the columns of left is added. squeal-postgresqlleft & 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 right7, 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 left2, a joined row with null values in the columns of left is added. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql A standard . It is not allowed to reference columns provided by preceding  items. squeal-postgresqlSubqueries can be preceded by  . This allows them to reference columns provided by preceding  items. squeal-postgresql+Set returning functions can be preceded by  . This allows them to reference columns provided by preceding  items.squeal-postgresqlargument squeal-postgresql;Set returning multi-argument functions can be preceded by  . This allows them to reference columns provided by preceding  items.squeal-postgresql arguments squeal-postgresqlrightsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft  "subquery expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone / squeal-postgresqlThe argument of   is an arbitrary subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of   is 1; if the subquery returns no rows, the result of   is .The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion. squeal-postgresqlThe right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given 3, which must yield a Boolean result. The result of   is  if all rows yield true (including the case where the subquery returns no rows). The result is  if any  result is found. The result is #0 if no comparison with a subquery row returns &, and at least one comparison returns #.7printSQL $ subAll true (.==) (values_ (true `as` #foo))9(TRUE = ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))) squeal-postgresqlThe right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given 3, which must yield a Boolean result. The result of   is  if any # result is obtained. The result is  if no true result is found (including the case where the subquery returns no rows).;printSQL $ subAny "foo" like (values_ ("foobar" `as` #foo))((E'foo' :: text) LIKE ANY (SELECT * FROM (VALUES ((E'foobar' :: text))) AS t ("foo"))) squeal-postgresqlThe result is  if the left-hand expression's result is equal to any of the right-hand expressions.*printSQL $ true `in_` [true, false, null_]TRUE IN (TRUE, FALSE, NULL) squeal-postgresqlThe result is  if the left-hand expression's result is not equal to any of the right-hand expressions.&printSQL $ true `notIn` [false, null_]TRUE NOT IN (FALSE, NULL) squeal-postgresqlsubquery squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquery squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquery squeal-postgresql expression squeal-postgresql expression  #null expressions and handlers(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone  / squeal-postgresql(Make the return type of the type family  if both arguments are, or  otherwise. squeal-postgresql analagous to printSQL null_NULL squeal-postgresql analagous to printSQL $ notNull trueTRUE squeal-postgresql Analagous to  inverse to  , useful when you know an  is ., because, for instance, you've filtered out NULL values in a column. squeal-postgresqlSome expressions are null polymorphic which may raise inference issues. Use   to fix their nullity as . squeal-postgresql+return the leftmost value which is not NULL'printSQL $ coalesce [null_, true] falseCOALESCE(NULL, TRUE, FALSE) squeal-postgresql analagous to  using COALESCEprintSQL $ fromNull true null_COALESCE(NULL, TRUE) squeal-postgresqlprintSQL $ null_ & isNull NULL IS NULL squeal-postgresqlprintSQL $ null_ & isNotNullNULL IS NOT NULL squeal-postgresql analagous to  using IS NULL$printSQL $ matchNull true not_ null_4CASE WHEN NULL IS NULL THEN TRUE ELSE (NOT NULL) END squeal-postgresqlright inverse to  ", if its arguments are equal then   gives NULL.:set -XTypeApplications%printSQL (nullIf (false *: param @1))NULLIF(FALSE, ($1 :: bool)) squeal-postgresqlnull polymorphic squeal-postgresqlwhat to convert NULL to squeal-postgresqlwhat to convert NULL tosqueal-postgresqlfunction to perform when NULL is absent $&json and jsonb functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone '(./>?K/ squeal-postgresqlBuild rows from Json types. squeal-postgresqlBuild rows from Json types. squeal-postgresqlBuilds a possibly-heterogeneously-typed JSON object out of a variadic argument list. The elements of the argument list must alternate between text and values. squeal-postgresql8Get JSON value (object field or array element) at a key. squeal-postgresqlGet JSON value (object field or array element) at a key, as text. squeal-postgresql#Get JSON value at a specified path. squeal-postgresql+Get JSON value at a specified path as text. squeal-postgresql?Does the string exist as a top-level key within the JSON value? squeal-postgresql6Do any of these array strings exist as top-level keys? squeal-postgresql6Do all of these array strings exist as top-level keys? squeal-postgresqlDelete a key or keys from a JSON object, or remove an array element.If the right operand is text : Delete key / value pair or string element from left operand. Key / value pairs are matched based on their key value, text[] : Delete multiple key / value pairs or string elements from left operand. Key / value pairs are matched based on their key value, integer : Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array. squeal-postgresqlDelete the field or element with specified path (for JSON arrays, negative integers count from the end) squeal-postgresqlReturns the value as json. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json value. squeal-postgresqlReturns the value as jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid jsonb value. squeal-postgresqlReturns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays. squeal-postgresql!Returns the row as a JSON object. squeal-postgresqlBuilds a possibly-heterogeneously-typed JSON array out of a variadic argument list. squeal-postgresqlBuilds a possibly-heterogeneously-typed (binary) JSON array out of a variadic argument list. squeal-postgresqlBuilds a JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair. squeal-postgresqlBuilds a binary JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair. squeal-postgresqlThis is an alternate form of   that takes two arrays; one for keys and one for values, that are zipped pairwise to create a JSON object. squeal-postgresqlThis is an alternate form of   that takes two arrays; one for keys and one for values, that are zipped pairwise to create a binary JSON object. squeal-postgresql;Returns the number of elements in the outermost JSON array. squeal-postgresqlReturns the number of elements in the outermost binary JSON array. squeal-postgresqlReturns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null. squeal-postgresqlReturns the type of the outermost binary JSON value as a text string. Possible types are object, array, string, number, boolean, and null. squeal-postgresqlReturns its argument with all object fields that have null values omitted. Other null values are untouched. squeal-postgresqlReturns its argument with all object fields that have null values omitted. Other null values are untouched. squeal-postgresql . jsonbSet target path new_value create_missing?Returns target with the section designated by path replaced by  new_value , or with  new_value added if create_missing is  and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays. squeal-postgresql / jsonbInsert target path new_value insert_afterReturns target with  new_value inserted. If target section designated by path is in a JSONB array,  new_value- will be inserted before target or after if  insert_after is =. If target section designated by path is in JSONB object,  new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays. squeal-postgresql+Returns its argument as indented JSON text. squeal-postgresqlExpands the outermost JSON object into a set of key/value pairs.printSQL (select Star (from (jsonEach (inline (Json (object ["a" .= "foo", "b" .= "bar"])))))):SELECT * FROM json_each(('{"a":"foo","b":"bar"}' :: json)) squeal-postgresqlExpands the outermost binary JSON object into a set of key/value pairs.printSQL (select Star (from (jsonbEach (inline (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))))