!                ! "# $ % & ' ( ) * + , - . / 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 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 ijklmnopqrstuvwxyz{|}~      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~            ! " # $ % & ' ( ) * + , - . / 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)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+{+|+},~,,,,,,,,,,,,,,,,,,----------............................//////////////////////////////////////001111111111111111111112233334444444444444444 4 4 4 4 4444444444444444444 4!4"4#$%&'()*+,-./05152535455565758595:5;5<5=6>6?6@6A6B6C6D6E6F6G6H6I6J6K6L6M6N6O6P6Q6R6S6T6U6V6W6X Y Z [7\7]7^7_7`7a8b8c8d8e8f8g8h8i8j8k9l9m9n9o9p9q9r9s9t9u9v9w9x9y9z9{9|9}9~99999999999999999999::::;;;;;;;;;;;;;<<<<<<<<<<<<<<<<<<================>>>>>>>? render functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone ,->FHSX_ki$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 experimentalNonegy)6squeal-postgresqls that can be thrown by Squeal.7squeal-postgresqlSQL exception state8squeal-postgresql?@ 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 Ehttps://www.postgresql.org/docs/current/static/errcodes-appendix.htmlAsqueal-postgresql/A pattern for serialization failure exceptions.Bsqueal-postgresql4A pattern for check constraint violation exceptions.Csqueal-postgresql*A pattern for unique violation exceptions.Dsqueal-postgresqlCatch 6s.Esqueal-postgresqlHandle 6s.Fsqueal-postgresql return a 6 or a result.Gsqueal-postgresqlThrow 6s.Dsqueal-postgresqlhandlerEsqueal-postgresqlhandler! 6789:;<=>?@ABCDEFG6789:;CBA<=>?@! DEFG types(c) Eitan Chatav, 2010eitan@morphism.tech experimentalNone&'-.456789;=>?@ACHUVX_kKMsqueal-postgresqlFixed-length, blank padded:kind! PG (FixChar 4)PG (FixChar 4) :: PGType = 'PGchar 4Nsqueal-postgresql$Variable-length text type with limit:kind! PG (VarChar 4)PG (VarChar 4) :: PGType= 'PGvarchar 4Osqueal-postgresqlO? is a 1-tuple type, useful for encoding or decoding a singletonRsqueal-postgresqlThe RV newtype is an indication that the Haskell type it's applied to should be stored as a  A.9:kind! PG (FixArray ((Double, Double), (Double, Double)))<PG (FixArray ((Double, Double), (Double, Double))) :: PGType*= 'PGfixarray '[2, 2] ('NotNull 'PGfloat8)Usqueal-postgresqlThe UV newtype is an indication that the Haskell type it's applied to should be stored as a  B.import Data.Vector$:kind! PG (VarArray (Vector Double))'PG (VarArray (Vector Double)) :: PGType"= 'PGvararray ('NotNull 'PGfloat8)Xsqueal-postgresqlThe XV newtype is an indication that the Haskell type it's applied to should be stored as a  C.:kind! PG (Enumerated Ordering)"PG (Enumerated Ordering) :: PGType= 'PGenum '["LT", "EQ", "GT"][squeal-postgresqlThe [V newtype is an indication that the Haskell type it's applied to should be stored as a  D.:{data Complex = Complex { real :: Double , imaginary :: Double } deriving stock GHC.Generic8 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):}:kind! PG (Composite Complex) PG (Composite Complex) :: PGType= 'PGcomposite$ '["real" ::: 'NotNull 'PGfloat8,) "imaginary" ::: 'NotNull 'PGfloat8]^squeal-postgresqlThe ^V newtype is an indication that the Haskell type it's applied to should be stored as a  E.:kind! PG (Jsonb [String])PG (Jsonb [String]) :: PGType = 'PGjsonbasqueal-postgresqlThe aV newtype is an indication that the Haskell type it's applied to should be stored as a  F.:kind! PG (Json [String])PG (Json [String]) :: PGType = 'PGjsondsqueal-postgresqlThe dG newtype stores a monetary value in terms of the number of cents, i.e.  $2,000.20 would be expressed as Money { cents = 200020 }.:kind! PG MoneyPG Money :: PGType = 'PGmoneygsqueal-postgresqlConstructor for Nhsqueal-postgresql Access the  of a Nisqueal-postgresqlConstructor for Mjsqueal-postgresql Access the  of a MMNOPQRSTUVWXYZ[\]^_`abcdefghijdefabc^_`[\]XYZUVWRSTNghMijOPQ list related types and functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.>@AHSUVX_kqsqueal-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 GH.squeal-postgresqlThe L class is for appending type-level list parameterized constructors such as , I, and J.squeal-postgresql is simply promoted  and is used in JOINs in Js.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&',-.179=>?@ACHSUVX_kqսsqueal-postgresql=es enables multi-schema support by allowing a reference to a  K,  L or  M> 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 k 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 f 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 N without aggregating.squeal-postgresql( is an auxiliary namespace, created by GROUP BY clauses (O%), 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&',-.179=>?@ACHSUVX_kqzZbsqueal-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.<There are several reasons why one might want to use schemas:LTo allow many users to use one database without interfering with each other.NTo organize database objects into logical groups to make them more manageable.sThird-party applications can be put into separate schemas so they do not collide with the names of other objects.squeal-postgresqlCA schema of a database consists of a list of aliased, user-defined  s.:{&type family Schema :: SchemaType where Schema = '[ "users" ::: 'Table (3 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>1 '[ "id" ::: 'Def :=> 'NotNull 'PGint40 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]) , "emails" ::: 'Table (/ '[ "pk_emails" ::: 'PrimaryKey '["id"]L , "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-postgresql]B-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-postgresqleSP-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 0s that involve a columnsqueal-postgresql Check if a 0 involves a columnsqueal-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 P and Q.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 R and S.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 T statements and in  ALTER TABLE U 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 9V statements and in  ALTER TABLE 9W.squeal-postgresql# is an idempotent that nullifies a +E used to nullify the left or right hand side of an outer join in a J. squeal-postgresql # is an idempotent that nullifies a ,.!squeal-postgresql!# is an idempotent that nullifies a ;."squeal-postgresql" is a constraint that proves a 5 has some NOT NULL.#squeal-postgresql# is a constraint that proves a 5 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 ,Js. It can be thought of as a product, or horizontal gluing and is used in Js and  Xs.,squeal-postgresqlA , is a row of ;7s. They correspond to Haskell record types by means of YZ and are used in many places.:{&type family PersonRow :: RowType where PersonRow =) '[ "name" ::: 'NotNull 'PGtext) , "age" ::: 'NotNull 'PGint4) , "dateOfBirth" ::: 'Null 'PGdate ]:}-squeal-postgresql-N 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 4+ must reference columns that either are a 3 or form a 2 constraint./squeal-postgresqlA / is a row of 0s.:{6type family UsersConstraints :: TableConstraints where< UsersConstraints = '[ "pk_users" ::: 'PrimaryKey '["id"] ]:}0squeal-postgresql0D encodes various forms of data constraints of columns in a table. 0s 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.5squeal-postgresql5 is a row of 6s.:{-type family UsersColumns :: ColumnsType where UsersColumns =- '[ "name" ::: 'NoDef :=> 'NotNull 'PGtext- , "id" ::: 'Def :=> 'NotNull 'PGint4 ]:}6squeal-postgresql6 encodes the allowance of DEFAULT and NULL and the base > for a column. :set -XTypeFamilies -XTypeInTypeimport GHC.TypeLitsMtype family IdColumn :: ColumnType where IdColumn = 'Def :=> 'NotNull 'PGint4Rtype family EmailColumn :: ColumnType where EmailColumn = 'NoDef :=> 'Null 'PGtext7squeal-postgresql7 encodes the availability of DEFAULTM 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.8squeal-postgresqlDEFAULT% is available for inserts and updates9squeal-postgresqlDEFAULT' is unavailable for inserts and updates:squeal-postgresqlThe constraint operator, :T is a type level pair between a "constraint" and some type, for use in pairing an 7 with a ; to produce a 6 or a / and a 5 to produce a -.;squeal-postgresql;: encodes the potential presence or definite absence of a NULLB allowing operations which are sensitive to such to be well typed.:kind 'Null 'PGint4'Null 'PGint4 :: NullType:kind 'NotNull ('PGvarchar 50)$'NotNull ('PGvarchar 50) :: NullType<squeal-postgresqlNULL may be present=squeal-postgresqlNULL is absent>squeal-postgresql>. is the promoted datakind of PostgreSQL types. :kind 'PGbool'PGbool :: PGType?squeal-postgresqllogical Boolean (true/false)@squeal-postgresqlsigned two-byte integerAsqueal-postgresqlsigned four-byte integerBsqueal-postgresqlsigned eight-byte integerCsqueal-postgresql arbitrary precision numeric typeDsqueal-postgresql0single precision floating-point number (4 bytes)Esqueal-postgresql0double precision floating-point number (8 bytes)Fsqueal-postgresqlcurrency amountGsqueal-postgresqlfixed-length character stringHsqueal-postgresql variable-length character stringIsqueal-postgresql variable-length character stringJsqueal-postgresqlbinary data ("byte array")Ksqueal-postgresqldate and time (no time zone)Lsqueal-postgresql"date and time, including time zoneMsqueal-postgresql calendar date (year, month, day)Nsqueal-postgresqltime of day (no time zone)Osqueal-postgresql time of day, including time zonePsqueal-postgresql time spanQsqueal-postgresqluniversally unique identifierRsqueal-postgresqlIPv4 or IPv6 host addressSsqueal-postgresqltextual JSON dataTsqueal-postgresqlbinary JSON data, decomposedUsqueal-postgresqlvariable length arrayVsqueal-postgresqlfixed length arrayWsqueal-postgresqlUenumerated (enum) types are data types that comprise a static, ordered set of values.Xsqueal-postgresqla composite type represents the structure of a row or record; it is essentially just a list of field names and their data types.Ysqueal-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.Zsqueal-postgresql;A tsquery value stores lexemes that are to be searched for.[squeal-postgresqlfObject identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.\squeal-postgresqllRange 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 typeso      !"#$%&'()*+,-./0123456789:;<=>STXWUV?@ABCDEFGHIJKLMNOPQRYZ[\]o>STXWUV?@ABCDEFGHIJKLMNOPQRYZ[\];<=,+65-     :78901234/.'%&$#"! )*(:74embedding of Haskell types into Postgres type system(c) Eitan Chatav, 2010eitan@morphism.tech experimentalNone&'-.456789;=>?@ACHUVX_k.isqueal-postgresqli extracts qX of the base type of nested homogeneous tuples, up to a depth of 10 for each dimension.jsqueal-postgresqlji turns Haskell nested homogeneous tuples into a list of lengths, up to a depth of 10 for each dimension.ksqueal-postgresql,Calculate the names of nullary constructors.lsqueal-postgresqlPCalculates the name of a nullary constructor, otherwise generates a type error.msqueal-postgresql&Calculates constructors of a datatype.nsqueal-postgresqln takes the  of a haskell > and if it's a simple product returns it, otherwise giving a .osqueal-postgresqlo turns a list of Haskell s into a list of ;s.psqueal-postgresqlpT 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)]qsqueal-postgresqlq turns a Haskell type into a ;.:kind! NullPG DoubleNullPG Double :: NullType= 'NotNull 'PGfloat8:kind! NullPG (Maybe Double)!NullPG (Maybe Double) :: NullType= 'Null 'PGfloat8rsqueal-postgresqlr applies q to the fields of a list.ssqueal-postgresqls turns a Haskell  into a ,.sB may be applied to normal Haskell record types provided they have  and  instances;Odata 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]tsqueal-postgresqlThe t@ 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"]usqueal-postgresqlThe vY type family embeds a subset of Haskell types as Postgres types. As an open type family, v is extensible.:kind! PG LocalTimePG LocalTime :: PGType= 'PGtimestampThe preferred way to generate vKs of your own type is through generalized newtype deriving or via deriving.Anewtype 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"]:{<data Complex = Complex {real :: Double, imaginary :: Double} deriving stock GHC.Generic6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)% deriving IsPG via Composite Complex:}:kind! PG ComplexPG Complex :: PGType= 'PGcomposite$ '["real" ::: 'NotNull 'PGfloat8,) "imaginary" ::: 'NotNull 'PGfloat8]wsqueal-postgresqlTxsqueal-postgresqlSysqueal-postgresqlFzsqueal-postgresqlH{squeal-postgresqlH|squeal-postgresqlS}squeal-postgresqlR~squeal-postgresqlQsqueal-postgresqlPsqueal-postgresqlOsqueal-postgresqlNsqueal-postgresqlMsqueal-postgresqlLsqueal-postgresqlKsqueal-postgresqlJsqueal-postgresqlJsqueal-postgresqlIsqueal-postgresqlIsqueal-postgresqlIsqueal-postgresqlG 1squeal-postgresqlEsqueal-postgresqlDsqueal-postgresqlCsqueal-postgresql@squeal-postgresqlBsqueal-postgresqlAsqueal-postgresql@squeal-postgresql?squeal-postgresqlU (q x)squeal-postgresqlU (q x)squeal-postgresqlX (s hask)squeal-postgresqlW (t hask)squeal-postgresqlV (j hask) (i hask)ijklmnopqrstuvuvqpstjionrmlkobject identifiers(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.=>?@AHUVXk7squeal-postgresqlThe  of a fieldsqueal-postgresqlThe  of a ;squeal-postgresqlThe  of an arraysqueal-postgresqlThe  of a >:set -XTypeApplicationsbconn <- 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-.HSUVXsqueal-postgresql.Makes a new connection to the database server.bThis 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 .ETo specify the schema you wish to connect with, use type application.:set -XDataKinds:set -XPolyKinds:set -XTypeOperators]type DB = '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint2])]]:set -XTypeApplications:set -XOverloadedStringsaconn <- 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&',-.17;=>?@AHMSUVX_ekqcsqueal-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 6], or if  params = () using 6^. Generally, paramsT will be a Haskell tuple or record whose entries may be referenced using positional _s and rowR 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 -`4 to fix actual Haskell input params and output rows.*:set -XDeriveAnyClass -XDerivingStrategies[type 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-postgresqlVThe process of retrieving or the command to retrieve data from a database is called a . The general  type is parameterized bylat :: FromType - scope for !a and subquery expressions,with :: FromType - scope for all b table expressions,db :: SchemasType - scope for all cs and ds,params :: [NullType] - scope for all e_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)]:{letg 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::{ letj 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::{letj qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]Q 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::{letj qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]T 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::{letx qry :: Query '[] with (Public Schema) '[ 'NotNull 'PGint4] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]D qry = select Star (from (table #tab) & where_ (#col1 .> param @1))in printSQL qry:}:SELECT * FROM "tab" AS "tab" WHERE ("col1" > ($1 :: int4))aggregation query::{ letj qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint8, "col2" ::: 'NotNull 'PGint4] qry =L 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::{letj 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"]N ,"fk_customers" ::: ForeignKey '["customer_id"] "public" "customers" '["id"]M ,"fk_shippers" ::: ForeignKey '["shipper_id"] "public" "shippers" '["id"] ]:}ctype NamesColumns = '["id" ::: 'NoDef :=> 'NotNull 'PGint4, "name" ::: 'NoDef :=> 'NotNull 'PGtext]Dtype CustomersConstraints = '["pk_customers" ::: PrimaryKey '["id"]]Btype ShippersConstraints = '["pk_shippers" ::: PrimaryKey '["id"]]:{type OrdersSchema =@ '[ "orders" ::: 'Table (OrdersConstraints :=> OrdersColumns)C , "customers" ::: 'Table (CustomersConstraints :=> NamesColumns)C , "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::{letj qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select (#t1 & DotStar)F (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:}cSELECT * FROM (VALUES ((E'true' :: text), TRUE), ((E'false' :: text), FALSE)) AS t ("col1", "col2")set operations::{letj qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]R qry = select Star (from (table #tab)) `unionAll` select Star (from (table #tab))in printSQL qry:}G(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab") with query::{ letj 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:}xWITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"window functions::{letf qry :: Query '[] with (Public Schema) db '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint8] qry = selectW (#col1 & Also (rank `as` #col2 `Over` (partitionBy #col1 & orderBy [#col2 & Asc]))) (from (table #tab))in printSQL qry:}lSELECT "col1" AS "col1", rank() OVER (PARTITION BY "col1" ORDER BY "col2" ASC) AS "col2" FROM "tab" AS "tab"correlated subqueries::{ letM 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:}wSELECT "col1" AS "col1" FROM "tab" AS "t1" WHERE EXISTS (SELECT * FROM "tab" AS "t2" WHERE ("t2"."col2" = "t1"."col1"))squeal-postgresqlDThe results of two queries can be combined using the set operation  . Duplicate rows are eliminated.squeal-postgresqlDThe results of two queries can be combined using the set operation 2, the disjoint union. Duplicate rows are retained.squeal-postgresqlDThe results of two queries can be combined using the set operation 2, the intersection. Duplicate rows are eliminated.squeal-postgresqlDThe results of two queries can be combined using the set operation 0, the intersection. Duplicate rows are retained.squeal-postgresqlDThe results of two queries can be combined using the set operation 4, the set difference. Duplicate rows are eliminated.squeal-postgresqlDThe results of two queries can be combined using the set operation 2, the set difference. Duplicate rows are retained.  with statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17;=>?@AHMSUVX_ekqsqueal-postgresqlA  is an auxiliary statement in a  clause.squeal-postgresqlj provides a way to write auxiliary statements for use in a larger query. These statements, referred to as Ps, 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)]:{ letj 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:}xWITH "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:itype ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]:{letL manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[] manp = withQ (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del)Q (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  i 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"PThe general form of a recursive WITH query is always a non-recursive term, then  (or j), then a recursive term, where only the recursive term can contain a reference to the query's own output.squeal-postgresqlaliased statementsqueal-postgresqlcommon table expressionssqueal-postgresql larger querysqueal-postgresqlrecursive querysqueal-postgresql larger query from clauses(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17;=>?@AHMSUVX_ekqsqueal-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 !a> 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&'-.HSUVX_gqsqueal-postgresql is either  or 2ting of a value, parameterized by an appropriate 7.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&',-.17;=>?@ACFHMSUVX_ksqueal-postgresqlIntersection operatorsqueal-postgresqlContained by operatorssqueal-postgresqlA  RankNTypea 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  RankNTypeL for functions with a fixed-length list of heterogeneous arguments. Use the . operator to end your argument lists, like so.EprintSQL (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 fg9, indeed a subcategory as it is closed under the usual fh and fi. squeal-postgresqlLike !+ but depends on the schemas of the database!squeal-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 j of the k" command, as new column values in ,l or ,m, or in search ns 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 os that are in scope for the #;grp ::  , the  of the fromC 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]:{letA fn :: Fun (Public Schema) ('Null 'PGint4) ('NotNull 'PGnumeric) fn = function #fnin printSQL (fn 1):}"fn"((1 :: int4)),squeal-postgresqlUprintSQL $ unsafeFunctionN "f" (currentTime :* localTimestamp :* false *: inline 'a')9f(CURRENT_TIME, LOCALTIMESTAMP, FALSE, (E'a' :: char(1)))-squeal-postgresql*Call a user defined multivariable functionMtype Fn = '[ 'Null 'PGint4, 'Null 'PGbool] :=> 'Returns ('NotNull 'PGnumeric)&type Schema = '["fn" ::: 'Function Fn]:{letS 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-postgresqloutput squeal-postgresql left inputsqueal-postgresql right inputsqueal-postgresqloutput"squeal-postgresqlcannot reference aliases+squeal-postgresql function name-squeal-postgresqlfunction alias !"#$%&'()*+,-#$%"*+()! '&,-values statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17;=>?@AHMSUVX_ekq`squeal-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]Olet query = values (1 `as` #a :* "one" `as` #b) [] :: Query lat with db '[] RowprintSQL queryFSELECT * FROM (VALUES ((1 :: int4), (E'one' :: text))) AS t ("a", "b")asqueal-postgresqlaK computes a row value or set of row values specified by value expressions.`squeal-postgresql]When more than one row is specified, all the rows must must have the same number of elementsasqueal-postgresqlone row of values`a`aset returning functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17;=>?@AHMSUVX_ekq bsqueal-postgresqlLike d+ but depends on the schemas of the databasecsqueal-postgresqlLike e+ but depends on the schemas of the databasedsqueal-postgresqlA  RankNType4 for set returning functions with multiple argument.esqueal-postgresqlA  RankNType- for set returning functions with 1 argument.fsqueal-postgresql>Escape hatch for a set returning function of a single variablegsqueal-postgresql?Call a user defined set returning function of a single variableNtype Fn = '[ 'Null 'PGbool] :=> 'ReturnsTable '["ret" ::: 'NotNull 'PGnumeric]&type Schema = '["fn" ::: 'Function Fn]:{letZ fn :: SetFun (Public Schema) ('Null 'PGbool) ("fn" ::: '["ret" ::: 'NotNull 'PGnumeric]) fn = setFunction #fnin printSQL (fn true):} "fn"(TRUE)hsqueal-postgresql7Escape hatch for a multivariable set returning functionisqueal-postgresql8Call a user defined multivariable set returning function]type 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))jsqueal-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))ksqueal-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))lsqueal-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)):}Vgenerate_series(now(), (now() + (INTERVAL '10.000 years')), (INTERVAL '1.000 months')) bsqueal-postgresqlinputsqueal-postgresqloutputcsqueal-postgresqlinputsqueal-postgresqloutputdsqueal-postgresqloutputfsqueal-postgresqlset returning functiongsqueal-postgresqlfunction aliashsqueal-postgresqlset returning functionisqueal-postgresqlfunction aliasjsqueal-postgresqlset returning functionksqueal-postgresqlset returning functionlsqueal-postgresqlset returning function bcdefghijkl edcbjklfghitype expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'.7;=>?@AHMSUVXkkDmsqueal-postgresqlLike PGTyped but also accounts for null.osqueal-postgresqlLike PGTyped but also accounts for null.qsqueal-postgresqlqs are used in V commands.tsqueal-postgresqlLift v to a fieldvsqueal-postgresqlw is a demoted version of a >xsqueal-postgresqlxs are used in {s and V 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-postgresqlgObject 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 x from a Haskell type.printSQL $ pgtypeFrom @StringtextprintSQL $ pgtypeFrom @Doublefloat8squeal-postgresqlused in V0 commands as a column constraint to note that NULL may be present in a columnsqueal-postgresqlused in V- commands as a column constraint to ensure NULL is not present in a columnsqueal-postgresqlused in V3 commands as a column constraint to give a defaultsqueal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type @squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type @squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type Asqueal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type Asqueal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type Bsqueal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type Bsqueal-postgresql Specify null x from a Haskell type.'printSQL $ nulltypeFrom @(Maybe String)textprintSQL $ nulltypeFrom @Doublefloat8squeal-postgresqlSpecify q from a Haskell type.)printSQL $ columntypeFrom @(Maybe String) text NULL!printSQL $ columntypeFrom @Doublefloat8 NOT NULL {squeal-postgresqltype to cast assqueal-postgresqlvalue to convert|squeal-postgresqltype to specify assqueal-postgresqlvalue}squeal-postgresqlvalue~squeal-postgresql type aliassqueal-postgresql table aliassqueal-postgresql view aliassqueal-postgresqltypesqueal-postgresqltypesqueal-postgresql default valuesqueal-postgresql column typeLmnopqrstuvwxyz{|}~L{|}xyz~qrsvwopmntu!date/time functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone -.7@ACHSUVX_squeal-postgresqlA  to use in  construction.squeal-postgresql&Affine space operations on time types.squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !+ 365)E(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) + (365 :: int4))squeal-postgresql+printSQL (365 +! makeDate (1984 :* 7 *: 3))E((365 :: int4) + make_date((1984 :: int4), (7 :: int4), (3 :: int4)))squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !- 365)E(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) - (365 :: int4))squeal-postgresql4printSQL (makeDate (1984 :* 7 *: 3) !-! currentDate)D(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) - CURRENT_DATE)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-postgresqlGCreate timestamp from year, month, day, hour, minute and seconds fields=printSQL (makeTimestamp (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))fmake_timestamp((2013 :: int4), (7 :: int4), (15 :: int4), (8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresqluCreate 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))hmake_timestamptz((2013 :: int4), (7 :: int4), (15 :: int4), (8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresqlprintSQL $ interval_ 7 Days(INTERVAL '7.000 days')6666#text search functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UV!squeal-postgresqlp matches tsquery ?squeal-postgresqlAND q s togethersqueal-postgresqlOR q s togethersqueal-postgresql negate a qsqueal-postgresqlq followed by qsqueal-postgresqlconvert array of lexemes to psqueal-postgresqlnumber of lexemes in psqueal-postgresql$number of lexemes plus operators in q squeal-postgresqlproduce q ignoring punctuation!squeal-postgresqlproduce q2 that searches for a phrase, ignoring punctuation"squeal-postgresqlproduce q from a web search style query#squeal-postgresqlget indexable part of a q$squeal-postgresqlnormalize words and convert to q%squeal-postgresqlreduce document text to p&squeal-postgresql!assign weight to each element of p'squeal-postgresql"remove positions and weights from p(squeal-postgresql#jsonToTSvector (document *: filter)> reduce each value in the document, specified by filter to a pD, and then concatenate those in document order to produce a single p. filter is a rV array, that enumerates what kind of elements need to be included into the resulting pp. 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 pD, and then concatenate those in document order to produce a single p. filter is a sV array, that enumerates what kind of elements need to be included into the resulting pp. 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 p+squeal-postgresql-select only elements with given weights from p,squeal-postgresql display a q match !"#$%&'()*+, !"#$%&'()*+,text functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UVW-squeal-postgresqlprintSQL $ lower "ARRRGGG"lower((E'ARRRGGG' :: text)).squeal-postgresqlprintSQL $ upper "eeee"upper((E'eeee' :: text))/squeal-postgresqlprintSQL $ charLength "four"char_length((E'four' :: text))0squeal-postgresqlThe 0 expression returns true if the string matches the supplied pattern. If patterns does not contain percent signs or underscores, then the pattern only represents the string itself; in that case 0 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))1squeal-postgresqlrThe 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))-./01-./01sort expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone &'.1=?@AC_2squeal-postgresqlThe 3$ clause causes the result rows of a X) to be sorted according to the specified 4(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.UYou can also control the order in which rows are processed by window functions using 3 within t.4squeal-postgresql4s are used by 3& to optionally sort the results of a u. 5 or 6 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 &v@ operator. Similarly, descending order is determined with the &w operator. 7, 8, 9 and :p options are used to determine whether nulls appear before or after non-null values in the sort ordering of a < result column.3squeal-postgresqlsorts5squeal-postgresqlsort by6squeal-postgresqlsort by7squeal-postgresqlsort by8squeal-postgresqlsort by9squeal-postgresqlsort by:squeal-postgresqlsort by 23456789: 456789:23range types and functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.45679;=>?@AHSUVX_egk#>squeal-postgresqlA >= datatype that comprises connected subsets of the real line.Asqueal-postgresql The type of A for a >.Bsqueal-postgresql unboundedCsqueal-postgresql inclusiveDsqueal-postgresql exclusiveEsqueal-postgresql Construct a E(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)Fsqueal-postgresqlFinite > constructorGsqueal-postgresqlFinite > constructorHsqueal-postgresqlFinite > constructorIsqueal-postgresqlFinite > constructorJsqueal-postgresqlHalf-infinite > constructorKsqueal-postgresqlHalf-infinite > constructorLsqueal-postgresqlHalf-infinite > constructorMsqueal-postgresqlHalf-infinite > constructorNsqueal-postgresqlA point on the lineOsqueal-postgresqlThe O linePsqueal-postgresqlrange is contained byQsqueal-postgresqlcontains rangeRsqueal-postgresql?strictly left of, return false when an empty range is involvedSsqueal-postgresql@strictly right of, return false when an empty range is involvedTsqueal-postgresqlNdoes not extend to the right of, return false when an empty range is involvedUsqueal-postgresqlMdoes not extend to the left of, return false when an empty range is involvedVsqueal-postgresql<is adjacent to, return false when an empty range is involvedWsqueal-postgresqlVunion, will fail if the resulting range would need to contain two disjoint sub-rangesXsqueal-postgresql intersectionYsqueal-postgresql[difference, will fail if the resulting range would need to contain two disjoint sub-rangesZsqueal-postgresqllower bound of range[squeal-postgresqlupper bound of range\squeal-postgresqlis the range empty?]squeal-postgresqlis the lower bound inclusive?^squeal-postgresqlis the lower bound infinite?_squeal-postgresqlis the upper bound inclusive?`squeal-postgresqlis the upper bound infinite?asqueal-postgresql:the smallest range which includes both of the given rangesbsqueal-postgresql\ (v hask)Esqueal-postgresql range typesqueal-postgresqlrange of values$>?@ACBDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`a$>?@FGHIJKLMNOACBDEPQRSTUVWXYZ[\]^_`aF4G4H4I4 encoding of statement parameters(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone,-.8=>?@AHSUVX_kBd usqueal-postgresqlu$ describes an encoding of a Haskell  into a list of parameter ;s.bconn <- 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:}<K (Just "\NUL\SOH") :* K (Just "a") :* K (Just "foo") :* Nil finish connxsqueal-postgresqlA x+ constraint gives an encoding of a Haskell c into the binary format of a PostgreSQL fixed-length array. You should not define instances for x", just use the provided instances.|squeal-postgresqlA | constraint lifts the  parser to an encoding of a (Symbol, Type) to a (Symbol, NullityType) , encoding <s to (s. You should not define instances for |", just use the provided instances.~squeal-postgresqlA ~+ constraint gives an encoding of a Haskell . into into the binary format of a PostgreSQL ;'. You should not define instances for ~#, just use the provided instances.squeal-postgresqlA + constraint gives an encoding of a Haskell . into into the binary format of a PostgreSQL >.squeal-postgresql#:set -XTypeApplications -XDataKindsbconn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres"!runReaderT (toPG @'[] False) conn"\NUL"(runReaderT (toPG @'[] (0 :: Int16)) conn "\NUL\NUL"(runReaderT (toPG @'[] (0 :: Int32)) conn"\NUL\NUL\NUL\NUL"9:set -XMultiParamTypeClasses -XGeneralizedNewtypeDerivingOnewtype UserId = UserId { getUserId :: Int64 } deriving newtype (IsPG, ToPG db)&runReaderT (toPG @'[] (UserId 0)) conn""\NUL\NUL\NUL\NUL\NUL\NUL\NUL\NUL" finish connsqueal-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)bconn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{letG encode :: EncodeParams '[] '[ 'NotNull 'PGint2, 'NotNull 'PGtext] Two encode = genericParams9in runReaderT (runEncodeParams encode (Two 2 "two")) conn:},K (Just "\NUL\STX") :* K (Just "two") :* Nil:{letS 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-postgresqlEncode 0 parameters.squeal-postgresqlCons a parameter encoding.bconn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[]' '[ 'Null 'PGint4, 'NotNull 'PGtext] (Maybe Int32, String)" encode = fst .* snd .* nilParams<in runReaderT (runEncodeParams encode (Nothing, "foo")) conn:}"K Nothing :* K (Just "foo") :* Nil finish connsqueal-postgresqlEnd a parameter encoding.bconn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[]= '[ 'Null 'PGint4, 'NotNull 'PGtext, 'NotNull ('PGchar 1)] (Maybe Int32, String, Char)@ encode = (\(x,_,_) -> x) .* (\(_,y,_) -> y) *. (\(_,_,z) -> z)Ain runReaderT (runEncodeParams encode (Nothing, "foo", 'z')) conn:}2K Nothing :* K (Just "foo") :* K (Just "z") :* Nil finish connsqueal-postgresqlEncode 1 parameter.bconn <- 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.bconn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[]* '[ 'NotNull 'PGint4, 'NotNull 'PGint2] (Int32, Int16)C 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-postgresqlrightuvwx{yz|}~uvw~|}x{yz58decoding of result values(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.;=>?@ACHMUVX_ksqueal-postgresql& describes a decoding of a PostgreSQL , into a Haskell .' has an interface given by the classes , , , , ,  , and .:set -XOverloadedLabels:{let decode :: DecodeRowB '[ "fst" ::: 'NotNull 'PGint2, "snd" ::: 'NotNull ('PGchar 1)] (Int16, Char) decode = (,) <$> #fst <*> #sndKin 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 <*> #sndKin 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 c 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 C constraint gives a parser from the binary format of a PostgreSQL > into a Haskell .squeal-postgresqls: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-postgresqlRow decoder for  records.$import qualified GHC.Generics as GHC$import qualified Generics.SOP as SOPmdata Two = Two {frst :: Int16, scnd :: String} deriving (Show, GHC.Generic, SOP.Generic, SOP.HasDatatypeInfo):{letV decode :: DecodeRow '[ "frst" ::: 'NotNull 'PGint2, "scnd" ::: 'NotNull 'PGtext] Two decode = genericRowMin runDecodeRow decode (SOP.K (Just "\NUL\STX") :* SOP.K (Just "two") :* Nil):}$Right (Two {frst = 2, scnd = "two"})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-postgresqlcombining functionsqueal-postgresql left decodersqueal-postgresql right decodersqueal-postgresqlcombining functionsqueal-postgresql alias of headsqueal-postgresql tail decoderresults(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone &'>X_ksqueal-postgresqls are generated by executing 6xs in a .y.They contain an underlying  and a .squeal-postgresql5Get a row corresponding to a given row number from a <, throwing an exception if the row number is out of bounds.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-postgresqlGet all rows from a .squeal-postgresql%Get the first row if possible from a .squeal-postgresqlLifts actions on results from LibPQ.squeal-postgresql8Returns the number of rows (tuples) in the query result.squeal-postgresql;Returns the number of columns (fields) in the query result.squeal-postgresql)Returns the result status of the command.squeal-postgresqlGReturns 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-postgresqlNReturns the number of rows affected by the SQL command. This function returns E 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 Check if a 's status is either  or  otherwise  a 7.squeal-postgresqlUReturns the error message most recently generated by an operation on the connection.squeal-postgresqlRReturns the error code most recently generated by an operation on the connection. Ehttps://www.postgresql.org/docs/current/static/errcodes-appendix.htmlsqueal-postgresqltotal number of rowssqueal-postgresqlresultsqueal-postgresql row numberout-of-line parameters(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'.=>?@ACHSUVXksqueal-postgresqlA X constraint is used to indicate a value that is supplied externally to a SQL statement. 6z, 6{ and 6|V 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 x.printSQL (parameter @1 int4) ($1 :: int4) squeal-postgresql  takes a 7 using type application and for basic types, infers a x.$printSQL (param @1 @('Null 'PGint4)) ($1 :: int4) squeal-postgresqlparam  math functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UV΄ 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:{letE 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:{letE expression :: Expression grp lat with db params from (null 'PGint2) expression = 5 `rem_` 2in printSQL expression:}((5 :: int2) % (2 :: int2))squeal-postgresql:{letG expression :: Expression grp lat with db params from (null 'PGfloat4) expression = trunc piin printSQL expression:} trunc(pi())squeal-postgresql:{letG expression :: Expression grp lat with db params from (null 'PGfloat4) expression = round_ piin printSQL expression:} round(pi())squeal-postgresql:{letG 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.UVsqueal-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:{letE expression :: Expression grp lat with db params from (null 'PGint2)9 expression = caseWhenThenElse [(true, 1), (false, 2)] 3in printSQL expression:}PCASE WHEN TRUE THEN (1 :: int2) WHEN FALSE THEN (2 :: int2) ELSE (3 :: int2) ENDsqueal-postgresql:{letE 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&',-.17;=>?@AHMSUVX_ekqYsqueal-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 +k 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 0m, 2, +k $ , +k $  , +k $ ! or +k $ "Q of these rows will be blocked until the current transaction ends; conversely, +k $  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 / or /q 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 2 a row, and also by an a that modifies the values on certain columns. Currently, the set of columns considered for the 0m 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 $ F, except that the lock acquired is weaker: this lock will not block +k $ "d commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any 0m that does not acquire a $  lock."squeal-postgresqlBehaves similarly to $ !$, except that the lock is weaker: +k $  is blocked, but not +k $  B. A key-shared lock blocks other transactions from performing 2 or any 0m. that changes the key values, but not other , and neither does it prevent +k $  , +k $ !, or +k $ ".#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 +k3 are simply read as usual. A locking clause with a Z 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 T 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-postgresql+s 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 0" that is optionally followed by a 1, 2, 3, 4, 5 6 and 7. 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.0squeal-postgresqlVA table reference that can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these.1squeal-postgresql)optional search coditions, combined with . After the processing of the 0! 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 0P; this is not required, but otherwise the WHERE clause will be fairly useless.2squeal-postgresqlThe 2 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.3squeal-postgresql"If a table has been grouped using :0, but only certain groups are of interest, the 3 can be used, much like a 1;, to eliminate groups from the result. Expressions in the 3w can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function).4squeal-postgresqlThe 4. is for optional sorting. When more than one 4u is specified, the later (right) values are used to sort rows that are equal according to the earlier (left) values.5squeal-postgresqlThe 5 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).6squeal-postgresqlThe 6 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.7squeal-postgresql7) can be added to a table expression with >.8squeal-postgresqlA 8 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 8 may be transformed by 9, :, ;, 3, < and = , using the K operator to match the left-to-right sequencing of their placement in SQL.9squeal-postgresqlA 9 is an endomorphism of .(s which adds a search condition to the 1.: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 3.<squeal-postgresqlA < is an endomorphism of .s which adds to the 5.=squeal-postgresqlAn = is an endomorphism of .s which adds to the 6.>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 X if that is specified in any of the clauses affecting it. Otherwise, it is processed as 9 if that is specified in any of the clauses affecting it.Esqueal-postgresql Render a %.Jsqueal-postgresql Render a .$squeal-postgresql lock strengthsqueal-postgresql table listsqueal-postgresql wait or not8squeal-postgresqltable reference9squeal-postgresqlfiltering condition:squeal-postgresqlgrouped columns;squeal-postgresqlhaving condition<squeal-postgresqllimit parameter=squeal-postgresqloffset parameter>squeal-postgresqlrow-level lock% !"#$%&'()*+,-./0123456789:;<=>%./0123456789:;<=>+,-()*%&'#$ !"! Squeal joins(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17;=>?@AHMSUVX_ekqcsqueal-postgresqlA c is the right hand side of a h, k, n, q, t join of s.hsqueal-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.isqueal-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.jsqueal-postgresqlLike i with a 8 but allowed to reference columns provided by preceding  items.ksqueal-postgresqlleft & inner (Join right) on&. The joined table is filtered by the on condition.lsqueal-postgresqlleft & innerJoin right on&. The joined table is filtered by the on condition.msqueal-postgresqlLike l with a 8 but allowed to reference columns provided by preceding  items.nsqueal-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 rightE. Thus, the joined table always has at least one row for each row in left.osqueal-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 rightE. Thus, the joined table always has at least one row for each row in left.psqueal-postgresqlLike o with a 8 but allowed to reference columns provided by preceding  items.qsqueal-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.rsqueal-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.ssqueal-postgresqlLike r with a 8 but allowed to reference columns provided by preceding  items.tsqueal-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.usqueal-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.vsqueal-postgresqlLike u with a 8 but allowed to reference columns provided by preceding  items.dsqueal-postgresql A standard A. It is not allowed to reference columns provided by preceding  items.esqueal-postgresqlSubqueries can be preceded by e@. This allows them to reference columns provided by preceding  items.fsqueal-postgresql+Set returning functions can be preceded by f@. This allows them to reference columns provided by preceding  items.squeal-postgresqlargumentgsqueal-postgresql;Set returning multi-argument functions can be preceded by g@. This allows them to reference columns provided by preceding  items.squeal-postgresql argumentshsqueal-postgresqlrightsqueal-postgresqlleftisqueal-postgresqlrightsqueal-postgresqlleftjsqueal-postgresqlright subquerysqueal-postgresqlleftksqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftlsqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftmsqueal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleftnsqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftosqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftpsqueal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleftqsqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftrsqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftssqueal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqllefttsqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftusqueal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleftvsqueal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleftcdefghijklmnopqrstuvcdefghijklmnopqrstuv"subquery expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.SUVXExsqueal-postgresqlThe argument of x 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 x is 1; if the subquery returns no rows, the result of x 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.ysqueal-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 y 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")))zsqueal-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 z is  if any # result is obtained. The result is T if no true result is found (including the case where the subquery returns no rows).;printSQL $ subAny "foo" like (values_ ("foobar" `as` #foo))W((E'foo' :: text) LIKE ANY (SELECT * FROM (VALUES ((E'foobar' :: text))) AS t ("foo"))){squeal-postgresqlThe result is T 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 X 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)xsqueal-postgresqlsubqueryysqueal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubqueryzsqueal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquery{squeal-postgresql expression|squeal-postgresql expressionxyz{|x{|yz#null expressions and handlers(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.SUVX }squeal-postgresql analagous to printSQL null_NULL~squeal-postgresql analagous to printSQL $ notNull trueTRUEsqueal-postgresql Analagous to  inverse to ~, useful when you know an # is =., because, for instance, you've filtered out NULL values in a column.squeal-postgresqlMSome 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 NULLsqueal-postgresqlprintSQL $ null_ & isNotNullNULL IS NOT NULLsqueal-postgresql analagous to  using IS NULL$printSQL $ matchNull true not_ null_4CASE WHEN NULL IS NULL THEN TRUE ELSE (NOT NULL) ENDsqueal-postgresqlright inverse to ", if its arguments are equal then  gives NULL.:set -XTypeApplications%printSQL (nullIf (false *: param @1))NULLIF(FALSE, ($1 :: bool))squeal-postgresqlnull polymorphicsqueal-postgresqlwhat to convert NULL tosqueal-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&'-.=>?HSUVXekd-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-postgresqlAGet 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-postgresqlDDelete a key or keys from a JSON object, or remove an array element.If the right operand is text w: 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-postgresqlhDelete 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-postgresqlgReturns 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-postgresqlTBuilds a possibly-heterogeneously-typed JSON array out of a variadic argument list.squeal-postgresql]Builds 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 k 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 s 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-postgresqlBReturns 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-postgresqllReturns its argument with all object fields that have null values omitted. Other null values are untouched.squeal-postgresqllReturns 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_valueF 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-postgresql@Expands 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-postgresqlGExpands the outermost binary JSON object into a set of key/value pairs.`printSQL (select Star (from (jsonbEach (inline (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))))<SELECT * FROM jsonb_each(('{"a":"foo","b":"bar"}' :: jsonb))squeal-postgresql@Expands the outermost JSON object into a set of key/value pairs.bprintSQL (select Star (from (jsonEachText (inline (Json (object ["a" .= "foo", "b" .= "bar"]))))))?SELECT * FROM json_each_text(('{"a":"foo","b":"bar"}' :: json))squeal-postgresqlGExpands the outermost binary JSON object into a set of key/value pairs.dprintSQL (select Star (from (jsonbEachText (inline (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))))ASELECT * FROM jsonb_each_text(('{"a":"foo","b":"bar"}' :: jsonb))squeal-postgresql1Returns set of keys in the outermost JSON object.OprintSQL (jsonObjectKeys (inline (Json (object ["a" .= "foo", "b" .= "bar"]))))3json_object_keys(('{"a":"foo","b":"bar"}' :: json))squeal-postgresql1Returns set of keys in the outermost JSON object.QprintSQL (jsonbObjectKeys (inline (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))5jsonb_object_keys(('{"a":"foo","b":"bar"}' :: jsonb))squeal-postgresqleExpands the JSON expression to a row whose columns match the record type defined by the given table.squeal-postgresqllExpands the binary JSON expression to a row whose columns match the record type defined by the given table.squeal-postgresqlExpands the outermost array of objects in the given JSON expression to a set of rows whose columns match the record type defined by the given table.squeal-postgresqlExpands the outermost array of objects in the given binary JSON expression to a set of rows whose columns match the record type defined by the given table.squeal-postgresql.Builds an arbitrary record from a JSON object.squeal-postgresql5Builds an arbitrary record from a binary JSON object.squeal-postgresql@Builds an arbitrary set of records from a JSON array of objects.squeal-postgresqlGBuilds an arbitrary set of records from a binary JSON array of objects.squeal-postgresql json typesqueal-postgresqlrow typesqueal-postgresqlrow typesqueal-postgresql json type// 88889 9 9 66%composite functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.=>?@AHSUVXekxsqueal-postgresqlA row constructor is an expression that builds a row value (also called a composite value) using values for its member fields.:{type Complex = 'PGcomposite' '[ "real" ::: 'NotNull 'PGfloat8) , "imaginary" ::: 'NotNull 'PGfloat8 ]:}llet i = row (0 `as` #real :* 1 `as` #imaginary) :: Expression grp lat with db params from ('NotNull Complex) printSQL i%ROW((0.0 :: float8), (1.0 :: float8))squeal-postgresql7A row constructor on all columns in a table expression.squeal-postgresql:{type Complex = 'PGcomposite' '[ "real" ::: 'NotNull 'PGfloat8) , "imaginary" ::: 'NotNull 'PGfloat8 ]/type Schema = '["complex" ::: 'Typedef Complex]:}xlet i = row (0 `as` #real :* 1 `as` #imaginary) :: Expression lat '[] grp (Public Schema) from params ('NotNull Complex)(printSQL $ i & field #complex #imaginary>(ROW((0.0 :: float8), (1.0 :: float8))::"complex")."imaginary"squeal-postgresql1zero or more expressions for the row field valuessqueal-postgresqlrow typesqueal-postgresql field name&"comparison functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone -.HSUVXpsqueal-postgresqlA  RankNType! for comparison expressions like .squeal-postgresqlComparison operations like , , , ,  and  will produce NULLs if one of their arguments is NULL.printSQL $ true .== null_ (TRUE = NULL)squeal-postgresqlprintSQL $ true ./= null_(TRUE <> NULL)squeal-postgresqlprintSQL $ true .>= null_(TRUE >= NULL)squeal-postgresqlprintSQL $ true .< null_ (TRUE < NULL)squeal-postgresqlprintSQL $ true .<= null_(TRUE <= NULL)squeal-postgresqlprintSQL $ true .> null_ (TRUE > NULL)squeal-postgresql/let expr = greatest [param @1] currentTimestamp printSQL expr=GREATEST(($1 :: timestamp with time zone), CURRENT_TIMESTAMP)squeal-postgresql)printSQL $ least [null_] currentTimestampLEAST(NULL, CURRENT_TIMESTAMP)squeal-postgresql(printSQL $ true `between` (null_, false)TRUE BETWEEN NULL AND FALSEsqueal-postgresql+printSQL $ true `notBetween` (null_, false)TRUE NOT BETWEEN NULL AND FALSEsqueal-postgresql,between, after sorting the comparison values1printSQL $ true `betweenSymmetric` (null_, false)%TRUE BETWEEN SYMMETRIC NULL AND FALSEsqueal-postgresql0not between, after sorting the comparison values4printSQL $ true `notBetweenSymmetric` (null_, false))TRUE NOT BETWEEN SYMMETRIC NULL AND FALSEsqueal-postgresql/not equal, treating null like an ordinary value&printSQL $ true `isDistinctFrom` null_(TRUE IS DISTINCT FROM NULL)squeal-postgresql+equal, treating null like an ordinary value)printSQL $ true `isNotDistinctFrom` null_ (TRUE IS NOT DISTINCT FROM NULL)squeal-postgresqlis trueprintSQL $ true & isTrue(TRUE IS TRUE)squeal-postgresqlis false or unknownprintSQL $ true & isNotTrue(TRUE IS NOT TRUE)squeal-postgresqlis falseprintSQL $ true & isFalse(TRUE IS FALSE)squeal-postgresqlis true or unknownprintSQL $ true & isNotFalse(TRUE IS NOT FALSE)squeal-postgresql is unknownprintSQL $ true & isUnknown(TRUE IS UNKNOWN)squeal-postgresqlis true or falseprintSQL $ true & isNotUnknown(TRUE IS NOT UNKNOWN)squeal-postgresqlbounds444444'array functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.=>?@AHSUVXekޡ squeal-postgresqlConstruct an array.%printSQL $ array [null_, false, true]ARRAY[NULL, FALSE, TRUE]squeal-postgresql Safely construct an empty array.printSQL $ array0 text(ARRAY[] :: text[])squeal-postgresqlConstruct a fixed length array.*printSQL $ array1 (null_ :* false *: true)ARRAY[NULL, FALSE, TRUE]%:type array1 (null_ :* false *: true) array1 (null_ :* false *: true) :: Expression grp lat with db params from0 (null ('PGfixarray '[3] ('Null 'PGbool)))squeal-postgresqlConstruct a fixed size matrix.HprintSQL $ array2 ((null_ :* false *: true) *: (false :* null_ *: true))/ARRAY[[NULL, FALSE, TRUE], [FALSE, NULL, TRUE]]C:type array2 ((null_ :* false *: true) *: (false :* null_ *: true)) =array2 ((null_ :* false *: true) *: (false :* null_ *: true)) :: Expression grp lat with db params from3 (null ('PGfixarray '[2, 3] ('Null 'PGbool)))squeal-postgresql3printSQL $ cardinality (array [null_, false, true])%cardinality(ARRAY[NULL, FALSE, TRUE])squeal-postgresql/printSQL $ array [null_, false, true] & index 2(ARRAY[NULL, FALSE, TRUE])[2]squeal-postgresql)Typesafe indexing of fixed length arrays.-printSQL $ array1 (true *: false) & index1 @1(ARRAY[TRUE, FALSE])[1]squeal-postgresql)Typesafe indexing of fixed size matrices.EprintSQL $ array2 ((true *: false) *: (false *: true)) & index2 @1 @2+(ARRAY[[TRUE, FALSE], [FALSE, TRUE]])[1][2]squeal-postgresql Expand an array to a set of rows.printSQL $ unnest (array [null_, false, true]) unnest(ARRAY[NULL, FALSE, TRUE])squeal-postgresqlThe right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given !3, which must yield a Boolean result. The result of  is e if all comparisons yield true (including the case where the array has zero elements). The result is  if any false result is found.;If the array expression yields a null array, the result of F will be null. If the left-hand expression yields null, the result of 4 is ordinarily null (though a non-strict comparison ! could possibly yield a different result). Also, if the right-hand array contains any null elements and no false comparison result is obtained, the result of = will be null, not true (again, assuming a strict comparison !Y). This is in accordance with SQL's normal rules for Boolean combinations of null values.9printSQL $ arrAll true (.==) (array [true, false, null_])'(TRUE = ALL (ARRAY[TRUE, FALSE, NULL]))/printSQL $ arrAll "hi" like (array ["bi","hi"])D((E'hi' :: text) LIKE ALL (ARRAY[(E'bi' :: text), (E'hi' :: text)]))squeal-postgresqlThe right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given !3, which must yield a Boolean result. The result of  is / if any true result is obtained. The result is S if no true result is found (including the case where the array has zero elements).;If the array expression yields a null array, the result of F will be null. If the left-hand expression yields null, the result of 4 is ordinarily null (though a non-strict comparison ! could possibly yield a different result). Also, if the right-hand array contains any null elements and no true comparison result is obtained, the result of > will be null, not false (again, assuming a strict comparison !Y). This is in accordance with SQL's normal rules for Boolean combinations of null values.9printSQL $ arrAny true (.==) (array [true, false, null_])'(TRUE = ANY (ARRAY[TRUE, FALSE, NULL]))/printSQL $ arrAny "hi" like (array ["bi","hi"])D((E'hi' :: text) LIKE ANY (ARRAY[(E'bi' :: text), (E'hi' :: text)]))squeal-postgresqlarray elementssqueal-postgresqlarray elementssqueal-postgresqlmatrix elementssqueal-postgresqlindexsqueal-postgresql vector indexsqueal-postgresql matrix indexsqueal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlarraysqueal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlarray  (inline expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.=>?@AHSUVX_`ksqueal-postgresqlLifts  to a column entrysqueal-postgresql'Haskell record field as a inline columnsqueal-postgresqlLifts  to fields.squeal-postgresqlLifts  to ;s.squeal-postgresqlThe 7 class allows embedding a Haskell value directly as an # using .printSQL (inline 'a')(E'a' :: char(1))printSQL (inline (1 :: Double))(1.0 :: float8)-printSQL (inline (Json ([1, 2] :: [Double])))('[1.0,2.0]' :: json)!printSQL (inline (Enumerated GT))'GT'squeal-postgresql0Inline a Haskell record as a row of expressions.squeal-postgresql-Inline a Haskell record as a list of columns.squeal-postgresqlrecordsqueal-postgresqlrecord  )!aggregate functions and arguments(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.17=>?@ACHUV_gn/ squeal-postgresqlA type family that calculates   type of a >. squeal-postgresqlA type family that calculates   > of a given argument >.squeal-postgresqlPermits filtering *s and ssqueal-postgresqlIf 8 is specified, then only the input rows for which the Q evaluates to true are fed to the aggregate function; other rows are discarded.squeal-postgresqls are used for the input of  #s.squeal-postgresql3squeal-postgresqlsqueal-postgresql? functions compute a single result from a set of input values.  functions can be used as  # s as well as *s.squeal-postgresql4A special aggregation that does not require an input:{letT expression :: Expression ('Grouped bys) '[] with db params from ('NotNull 'PGint8) expression = countStarin printSQL expression:}count(*)squeal-postgresql:{leto expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: null ty]] ('NotNull 'PGint8) expression = count (All #col)in printSQL expression:}count(ALL "col")squeal-postgresql:{letx expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: 'Null 'PGnumeric]] ('Null 'PGnumeric)? expression = sum_ (Distinct #col & filterWhere (#col .< 100))in printSQL expression:}?sum(DISTINCT "col") FILTER (WHERE ("col" < (100.0 :: numeric)))squeal-postgresql9input values, including nulls, concatenated into an array:{let expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: 'Null 'PGnumeric]] ('Null ('PGvararray ('Null 'PGnumeric)))] expression = arrayAgg (All #col & orderBy [AscNullsFirst #col] & filterWhere (#col .< 100))in printSQL expression:}_array_agg(ALL "col" ORDER BY "col" ASC NULLS FIRST) FILTER (WHERE ("col" < (100.0 :: numeric)))squeal-postgresql!aggregates values as a JSON arraysqueal-postgresql!aggregates values as a JSON arraysqueal-postgresql=the bitwise AND of all non-null input values, or null if none:{letq expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: null 'PGint4]] ('Null 'PGint4)% expression = bitAnd (Distinct #col)in printSQL expression:}bit_and(DISTINCT "col")squeal-postgresql<the bitwise OR of all non-null input values, or null if none:{letq expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: null 'PGint4]] ('Null 'PGint4) expression = bitOr (All #col)in printSQL expression:}bit_or(ALL "col")squeal-postgresql2true if all input values are true, otherwise false:{letn winFun :: WindowFunction 'Ungrouped '[] with db params '[tab ::: '["col" ::: null 'PGbool]] ('Null 'PGbool) winFun = boolAnd (Window #col)in printSQL winFun:}bool_and("col") squeal-postgresql9true if at least one input value is true, otherwise false:{letq expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: null 'PGbool]] ('Null 'PGbool) expression = boolOr (All #col)in printSQL expression:}bool_or(ALL "col")!squeal-postgresqlequivalent to :{letq expression :: Expression ('Grouped bys) '[] with db params '[tab ::: '["col" ::: null 'PGbool]] ('Null 'PGbool)$ expression = every (Distinct #col)in printSQL expression:}every(DISTINCT "col")"squeal-postgresql3maximum value of expression across all input values#squeal-postgresql3minimum value of expression across all input values$squeal-postgresql1the average (arithmetic mean) of all input values%squeal-postgresqlcorrelation coefficient:{let expression :: Expression ('Grouped g) '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)% expression = corr (Alls (#y *: #x))in printSQL expression:}corr(ALL "y", "x")&squeal-postgresqlpopulation covariance:{let expression :: Expression ('Grouped g) '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)) expression = covarPop (Alls (#y *: #x))in printSQL expression:}covar_pop(ALL "y", "x")'squeal-postgresqlsample covariance:{let winFun :: WindowFunction 'Ungrouped '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)) winFun = covarSamp (Windows (#y *: #x))in printSQL winFun:}covar_samp("y", "x")(squeal-postgresql.average of the independent variable (sum(X)/N):{let expression :: Expression ('Grouped g) '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)) expression = regrAvgX (Alls (#y *: #x))in printSQL expression:}regr_avgx(ALL "y", "x"))squeal-postgresql,average of the dependent variable (sum(Y)/N):{let winFun :: WindowFunction 'Ungrouped '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)( winFun = regrAvgY (Windows (#y *: #x))in printSQL winFun:}regr_avgy("y", "x")*squeal-postgresql:number of input rows in which both expressions are nonnull:{let winFun :: WindowFunction 'Ungrouped '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGint8)) winFun = regrCount (Windows (#y *: #x))in printSQL winFun:}regr_count("y", "x")+squeal-postgresqlSy-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs:{let expression :: Expression ('Grouped g) '[] c s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8). expression = regrIntercept (Alls (#y *: #x))in printSQL expression:}regr_intercept(ALL "y", "x"),squeal-postgresql regr_r2(Y, X)', square of the correlation coefficient-squeal-postgresqlregr_slope(Y, X)P, slope of the least-squares-fit linear equation determined by the (X, Y) pairs.squeal-postgresqlregr_sxx(Y, X)G, sum(X^2) - sum(X)^2/N ( sum of squares  of the independent variable)/squeal-postgresqlregr_sxy(Y, X)[, sum(X*Y) - sum(X) * sum(Y)/N ( sum of products  of independent times dependent variable)0squeal-postgresqlregr_syy(Y, X)E, sum(Y^2) - sum(Y)^2/N ( sum of squares  of the dependent variable)1squeal-postgresqlhistorical alias for 32squeal-postgresql1population standard deviation of the input values3squeal-postgresql-sample standard deviation of the input values4squeal-postgresqlhistorical alias for 65squeal-postgresqlVpopulation variance of the input values (square of the population standard deviation)6squeal-postgresqlNsample variance of the input values (square of the sample standard deviation)7squeal-postgresql7j invokes the aggregate once for each distinct set of values, for multiple expressions, found in the input.8squeal-postgresql8Y invokes the aggregate once for each distinct value of the expression found in the input.9squeal-postgresql:F invokes the aggregate on multiple arguments once for each input row.:squeal-postgresql:E invokes the aggregate on a single argument once for each input row.;squeal-postgresql;d invokes the aggregate on a single argument once for each input row where the argument is not null<squeal-postgresql<c invokes the aggregate once for each distinct, not null value of the expression found in the input.squeal-postgresql*escape hatch to define aggregate functions'squeal-postgresql#include rows which evaluate to truesqueal-postgresqlargumentsqueal-postgresqlargumentsqueal-postgresqlargumentsqueal-postgresqlargumentsqueal-postgresqlargumentsqueal-postgresqlargumentsqueal-postgresqlargumentsqueal-postgresqlargument squeal-postgresqlargument!squeal-postgresqlargument"squeal-postgresqlargument#squeal-postgresqlargument$squeal-postgresqlargument%squeal-postgresql arguments&squeal-postgresql arguments'squeal-postgresql arguments(squeal-postgresql arguments)squeal-postgresql arguments*squeal-postgresql arguments+squeal-postgresql arguments,squeal-postgresql arguments-squeal-postgresql arguments.squeal-postgresql arguments/squeal-postgresql arguments0squeal-postgresql arguments1squeal-postgresqlargument2squeal-postgresqlargument3squeal-postgresqlargument4squeal-postgresqlargument5squeal-postgresqlargument6squeal-postgresqlargument7squeal-postgresql arguments8squeal-postgresqlargument9squeal-postgresql arguments:squeal-postgresqlargument;squeal-postgresqlargument<squeal-postgresqlargumentsqueal-postgresqlfunction1   !"#$%&'()*+,-./0123456789:;<1 !"#$%&'()*+,-./0123456:9;87<  *+window functions, arguments and definitions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'.7;=>?@AHMSUVX_gAsqueal-postgresqlA  RankNTypeS for window functions with a fixed-length list of heterogeneous arguments. Use the % operator to end your argument lists.Bsqueal-postgresqlA  RankNType& for window functions with 1 argument.Csqueal-postgresqlA  RankNType( for window functions with no arguments.Dsqueal-postgresqlDs are used for the input of Hs.Fsqueal-postgresqlN or MGsqueal-postgresqlHsqueal-postgresqlA window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.Ksqueal-postgresqlA KD is a set of table rows that are somehow related to the current rowMsqueal-postgresqlM invokes a H on multiple argument.Nsqueal-postgresqlN invokes a H on a single argument.Osqueal-postgresqlThe O clause within tP divides the rows into groups, or partitions, that share the same values of the O #x(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.Psqueal-postgresql*escape hatch for defining window functionsQsqueal-postgresql9escape hatch for defining multi-argument window functionsRsqueal-postgresql+rank of the current row with gaps; same as S of its first peer printSQL rankrank()Ssqueal-postgresql?number of the current row within its partition, counting from 1printSQL rowNumber row_number()Tsqueal-postgresqlFrank of the current row without gaps; this function counts peer groupsprintSQL denseRank dense_rank()Usqueal-postgresqlIrelative rank of the current row: (rank - 1) / (total partition rows - 1)printSQL percentRankpercent_rank()Vsqueal-postgresqlmcumulative distribution: (number of partition rows preceding or peer with current row) / total partition rowsprintSQL cumeDist cume_dist()Wsqueal-postgresql[integer ranging from 1 to the argument value, dividing the partition as equally as possibleprintSQL $ ntile (Window 5)ntile((5 :: int4))Xsqueal-postgresqlreturns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row.Ysqueal-postgresqlreturns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row.Zsqueal-postgresqlLreturns value evaluated at the row that is the first row of the window frame[squeal-postgresqlKreturns value evaluated at the row that is the last row of the window frame\squeal-postgresqlqreturns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such rowAsqueal-postgresqlinputssqueal-postgresqloutputBsqueal-postgresqlinputsqueal-postgresqloutputCsqueal-postgresqlcannot reference aliasesLsqueal-postgresqlO clausesqueal-postgresql3 clauseMsqueal-postgresql argumentsNsqueal-postgresqlargumentOsqueal-postgresql partitionsABCDEFGHIJKLMNOPQRSTUVWXYZ[\KLOHIJDEFGNMCBARSTUVWXYZ[\PQ+select statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17;=>?@AHMSUVX_ekqisqueal-postgresqlThe simplest kinds of i are j and k which emits all columns that a . produces. A select l is a list of #s. A i could be a list of Hs m K.  is can be selected with n.osqueal-postgresqlthe . in the o command constructs an intermediate virtual table by possibly combining tables, views, eliminating rows, grouping, etc. This table is finally passed on to processing by the select list. The iI determines which columns of the intermediate table are actually output.psqueal-postgresqlLike o but takes an  list of #s instead of a general i.qsqueal-postgresqlvAfter the select list has been processed, the result table can be subject to the elimination of duplicate rows using q.rsqueal-postgresqlLike q but takes an  list of #s instead of a general i.ssqueal-postgresqls keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY. ORDER BY is used to ensure that the desired row appears first.The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.In order to guarantee they match and reduce redundancy, this function will prepend the The DISTINCT ON expressions to the ORDER BY clause.tsqueal-postgresqlLike s but takes an  list of #s instead of a general i. jsqueal-postgresql table in the Jksqueal-postgresql table with lsqueal-postgresql list of  #smsqueal-postgresql list of  Hsnsqueal-postgresql iosqueal-postgresql selectionsqueal-postgresqlintermediate virtual tablepsqueal-postgresql select listsqueal-postgresqlintermediate virtual tableqsqueal-postgresql selectionsqueal-postgresqlintermediate virtual tablersqueal-postgresql select listsqueal-postgresqlintermediate virtual tablessqueal-postgresql:DISTINCT ON expression(s) and prepended to ORDER BY clausesqueal-postgresql selectionsqueal-postgresqlintermediate virtual tabletsqueal-postgresql0distinct on and return the first row in orderingsqueal-postgresql selectionsqueal-postgresqlintermediate virtual table ijlmknopqrst opqrstijlmkn,data manipulation language(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.7;=>?@AHMSUVX_gkqP5}squeal-postgresqlSpecify additional tables with  an - list of table expressions, allowing columns from other tables to appear in the WHERE condition. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table in the 0 list, unless you wish to set up a self-join. ~( if no additional tables are to be used.squeal-postgresqlA  computes and returns value(s) based on each row actually inserted, updated or deleted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. Only rows that were successfully inserted or updated or deleted will be returned. For example, if a row was locked but not updated because an 1 1= condition was not satisfied, the row will not be returned.  j* will return all columns in the row. Use  8 in the common case where no return values are desired.squeal-postgresqlThe  type is parameterized by a db -, against which it is type-checked, an input params Haskell , and an ouput row Haskell . Generally, paramsT will be a Haskell tuple or record whose entries may be referenced using positional s and rowR will be a Haskell record, whose entries will be targeted using overloaded labels.A  can be run using 6z, or if  params = () using 6.( 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 -4 to fix actual Haskell input params and output rows.*:set -XDeriveAnyClass -XDerivingStrategies[type 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):}:{let: manp :: Manipulation_ (Public Schema) (Int64, Int64) RowQ manp = deleteFrom #tab NoUsing (#col1 .== param @1 + param @2) (Returning Star)6 stmt :: Statement (Public Schema) (Int64, Int64) Row stmt = manipulation manp:} :type manpmanp :: Manipulation '[]< '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]]- '[ 'NotNull 'PGint8, 'NotNull 'PGint8]? '["col1" ::: 'Null 'PGint8, "col2" ::: 'NotNull 'PGtext] :type stmtstmt :: Statement< '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]] (Int64, Int64) Rowsqueal-postgresqlA d is a statement which may modify data in the database, but does not alter its schemas. Examples are 1ls, 0ms and 2s. A  is also considered a % even though it does not modify data. The general  type is parameterized bywith :: FromType - scope for all  table expressions,db :: SchemasType - scope for all s and s,params :: [NullType] - scope for all e_s,row :: RowType - return type of the .Let's see some examples of s.simple insert:[type Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint4, "col2" ::: 'Def :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let3 manp :: Manipulation with (Public Schema) '[] '[] manp =G insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))in printSQL manp:}IINSERT INTO "tab" AS "tab" ("col1", "col2") VALUES ((2 :: int4), DEFAULT)!out-of-line parameterized insert:^type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{letD manp :: Manipulation with (Public Schema) '[ 'NotNull 'PGint4] '[] manp = insertInto_ #tab $ Values_7 (Default `as` #col1 :* Set (param @1) `as` #col2)in printSQL manp:}JINSERT INTO "tab" AS "tab" ("col1", "col2") VALUES (DEFAULT, ($1 :: int4))in-line parameterized insert:^type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{Idata Row = Row { col1 :: Optional SOP.I ('Def :=> Int32), col2 :: Int32 } deriving stock (GHC.Generic)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):}:{letA manp :: Row -> Row -> Manipulation with (Public Schema) '[] '[]> manp row1 row2 = insertInto_ #tab $ inlineValues row1 [row2]0 row1 = Row {col1 = Default, col2 = 2 :: Int32}@ row2 = Row {col1 = NotDefault (3 :: Int32), col2 = 4 :: Int32}in printSQL (manp row1 row2):}eINSERT INTO "tab" AS "tab" ("col1", "col2") VALUES (DEFAULT, (2 :: int4)), ((3 :: int4), (4 :: int4))returning insert::{letN manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4] manp =D insertInto #tab (Values_ (Set 2 `as` #col1 :* Set 3 `as` #col2))) OnConflictDoRaise (Returning #col1)in printSQL manp:}hINSERT INTO "tab" AS "tab" ("col1", "col2") VALUES ((2 :: int4), (3 :: int4)) RETURNING "col1" AS "col1"upsert:jtype CustomersColumns = '["name" ::: 'NoDef :=> 'NotNull 'PGtext, "email" ::: 'NoDef :=> 'NotNull 'PGtext]9type CustomersConstraints = '["uq" ::: 'Unique '["name"]]\type CustomersSchema = '["customers" ::: 'Table (CustomersConstraints :=> CustomersColumns)]:{ let< manp :: Manipulation with (Public CustomersSchema) '[] '[] manp = insertInto #customersQ (Values_ (Set "John Smith" `as` #name :* Set "john@smith.com" `as` #email))$ (OnConflict (OnConstraint #uq)\ (DoUpdate (Set (#excluded ! #email <> "; " <> #customers ! #email) `as` #email) [])) (Returning_ Nil)in printSQL manp:}INSERT INTO "customers" AS "customers" ("name", "email") VALUES ((E'John Smith' :: text), (E'john@smith.com' :: text)) ON CONFLICT ON CONSTRAINT "uq" DO UPDATE SET "email" = ("excluded"."email" || ((E'; ' :: text) || "customers"."email")) query insert::{let3 manp :: Manipulation with (Public Schema) '[] '[]F manp = insertInto_ #tab (Subquery (select Star (from (table #tab))))in printSQL manp:}7INSERT INTO "tab" AS "tab" SELECT * FROM "tab" AS "tab"update::{let3 manp :: Manipulation with (Public Schema) '[] '[]: manp = update_ #tab (Set 2 `as` #col1) (#col1 ./= #col2)in printSQL manp:}GUPDATE "tab" AS "tab" SET "col1" = (2 :: int4) WHERE ("col1" <> "col2")delete::{letk manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]C manp = deleteFrom #tab NoUsing (#col1 .== #col2) (Returning Star)in printSQL manp:}>DELETE FROM "tab" AS "tab" WHERE ("col1" = "col2") RETURNING *delete and using clause::{type Schema3 =' '[ "tab" ::: 'Table ('[] :=> Columns)- , "other_tab" ::: 'Table ('[] :=> Columns)/ , "third_tab" ::: 'Table ('[] :=> Columns) ]:}:{ let4 manp :: Manipulation with (Public Schema3) '[] '[] manp =H deleteFrom #tab (Using (table #other_tab & also (table #third_tab)))+ ( (#tab ! #col2 .== #other_tab ! #col2)/ .&& (#tab ! #col2 .== #third_tab ! #col2) ) (Returning_ Nil)in printSQL manp:}DELETE FROM "tab" AS "tab" USING "other_tab" AS "other_tab", "third_tab" AS "third_tab" WHERE (("tab"."col2" = "other_tab"."col2") AND ("tab"."col2" = "third_tab"."col2"))with manipulation:itype ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]:{letL manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[] manp = withQ (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del)Q (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-postgresql a lsqueal-postgresql Convert a  into a .squeal-postgresql what to usesqueal-postgresql row of valuessqueal-postgresql to embed as a  }~ }~- statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone &',.4567>SXbsqueal-postgresqlA  constraint to ensure that a Haskell type is a record type, and all its fields and can be decoded from corresponding Postgres fields.squeal-postgresqlA  constraint to ensure that a Haskell type is a product type, all its terms have known Oids, and can be encoded to corresponding Postgres types.squeal-postgresqlA  consists of a \ or a  that can be run in a .y.squeal-postgresql6Constructor for a data manipulation language statementsqueal-postgresql5Constructor for a structured query language statementsqueal-postgresql;Smart constructor for a structured query language statementsqueal-postgresql<Smart constructor for a data manipulation language statementsqueal-postgresqlencoding of parameterssqueal-postgresqldecoding of returned rowssqueal-postgresql1l, 0m, or 2, ...squeal-postgresqlencoding of parameterssqueal-postgresqldecoding of returned rowssqueal-postgresql+k, , ...squeal-postgresql+k, , ...squeal-postgresql1l, 0m, or 2, .... session monad(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.8=>?@ACHSVXkq /squeal-postgresql is an mtl style constraint, similar to  , for using ?@ to run s.squeal-postgresql runs a  which takes out-of-line _s.import Data.Int (Int32, Int64)import Data.Monoid (Sum(Sum)):{ let2 sumOf :: Statement db (Int32, Int32) (Sum Int32) sumOf = query $ values_ $$ ( param @1 @('NotNull 'PGint4) +" param @2 @('NotNull 'PGint4) ) `as` #getSumina withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do' result <- executeParams sumOf (2,2) firstRow result:}Just (Sum {getSum = 4})squeal-postgresql runs a returning-free .)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Data.Int(Int32):{let- insertion :: Statement DB (Int32, Int32) ()9 insertion = manipulation $ insertInto_ #tab $ Values_ $4 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*1 Set (param @2 @('NotNull 'PGint4)) `as` #col2% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup- & pqThen (executeParams_ insertion (2,2)) & pqThen (define teardown):}squeal-postgresql runs a parameter-free .import Data.Int(Int32):{ let two :: Expr ('NotNull 'PGint4) two = 2, twoPlusTwo :: Statement db () (Only Int32); twoPlusTwo = query $ values_ $ (two + two) `as` #fromOnlyina withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do result <- execute twoPlusTwo firstRow result:}Just (Only {fromOnly = 4})squeal-postgresql' runs a parameter-free, returning-free .:{let silence :: Statement db () () silence = manipulation $; UnsafeManipulation "Set client_min_messages TO WARNING"ino withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ execute_ silence:}squeal-postgresql runs a  on a e container by first preparing the statement, then running the prepared statement on each element.import Data.Int (Int32, Int64)import Data.Monoid (Sum(Sum)):{ let2 sumOf :: Statement db (Int32, Int32) (Sum Int32) sumOf = query $ values_ $$ ( param @1 @('NotNull 'PGint4) +" param @2 @('NotNull 'PGint4) ) `as` #getSumina withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do8 results <- executePrepared sumOf [(2,2),(3,3),(4,4)] traverse firstRow results:}I[Just (Sum {getSum = 4}),Just (Sum {getSum = 6}),Just (Sum {getSum = 8})]squeal-postgresql runs a returning-free  on a e container by first preparing the statement, then running the prepared statement on each element.)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Data.Int(Int32):{let- insertion :: Statement DB (Int32, Int32) ()9 insertion = manipulation $ insertInto_ #tab $ Values_ $4 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*1 Set (param @2 @('NotNull 'PGint4)) `as` #col2% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup= & pqThen (executePrepared_ insertion [(2,2),(3,3),(4,4)]) & pqThen (define teardown):}squeal-postgresql runs a .)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Control.Monad.IO.Classimport Data.Int(Int32):{let; insertAdd :: Manipulation_ DB (Int32, Int32) (Only Int32) insertAdd = insertInto #tab  ( Values_ $8 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*5 Set (param @2 @('NotNull 'PGint4)) `as` #col2 ) OnConflictDoRaise3 ( Returning_ ((#col1 + #col2) `as` #fromOnly) )% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup & pqThen ( doB result <- manipulateParams insertAdd (2::Int32,2::Int32)/ Just (Only answer) <- firstRow result* liftIO $ print (answer :: Int32) ) & pqThen (define teardown):}4squeal-postgresql runs a !, for a returning-free statement.)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Data.Int(Int32):{let1 insertion :: Manipulation_ DB (Int32, Int32) ()* insertion = insertInto_ #tab $ Values_ $4 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*1 Set (param @2 @('NotNull 'PGint4)) `as` #col2% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup> & pqThen (manipulateParams_ insertion (2::Int32,2::Int32)) & pqThen (define teardown):}squeal-postgresql runs a !, for a parameter-free statement.)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Control.Monad.IO.Classimport Data.Int(Int32):{let6 insertTwoPlusTwo :: Manipulation_ DB () (Only Int32)% insertTwoPlusTwo = insertInto #tab 4 (Values_ $ Set 2 `as` #col1 :* Set 2 `as` #col2) OnConflictDoRaise1 (Returning_ ((#col1 + #col2) `as` #fromOnly))% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup & pqThen ( do/ result <- manipulate insertTwoPlusTwo/ Just (Only answer) <- firstRow result* liftIO $ print (answer :: Int32) ) & pqThen (define teardown):}4squeal-postgresql runs a 1, for a returning-free, parameter-free statement.:{let# silence :: Manipulation_ db () ()C silence = UnsafeManipulation "Set client_min_messages TO WARNING"inr withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ manipulate_ silence:}squeal-postgresql runs a .import Data.Int (Int32, Int64)import Control.Monad.IO.Classimport Data.Monoid (Sum(Sum)):{ let/ sumOf :: Query_ db (Int32, Int32) (Sum Int32) sumOf = values_ $$ ( param @1 @('NotNull 'PGint4) +" param @2 @('NotNull 'PGint4) ) `as` #getSumina withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do6 result <- runQueryParams sumOf (2::Int32,2::Int32)& Just (Sum four) <- firstRow result" liftIO $ print (four :: Int32):}4squeal-postgresql runs a !, for a parameter-free statement.import Data.Int (Int32, Int64)import Control.Monad.IO.Classimport Data.Monoid (Sum(Sum)):{ let( twoPlusTwo :: Query_ db () (Sum Int32)- twoPlusTwo = values_ $ (2 + 2) `as` #getSumina withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do! result <- runQuery twoPlusTwo& Just (Sum four) <- firstRow result" liftIO $ print (four :: Int32):}4squeal-postgresql runs a  on a a container by first preparing the statement, then running the prepared statement on each element.)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Control.Monad.IO.Classimport Data.Int(Int32):{let; insertAdd :: Manipulation_ DB (Int32, Int32) (Only Int32) insertAdd = insertInto #tab  ( Values_ $8 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*5 Set (param @2 @('NotNull 'PGint4)) `as` #col2 ) OnConflictDoRaise3 ( Returning_ ((#col1 + #col2) `as` #fromOnly) )% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup & pqThen ( doQ results <- traversePrepared insertAdd [(2::Int32,2::Int32),(3,3),(4,4)]. answers <- traverse firstRow resultsJ liftIO $ print [answer :: Int32 | Just (Only answer) <- answers] ) & pqThen (define teardown):}[4,6,8]squeal-postgresql is a flipped )type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Control.Monad.IO.Classimport Data.Int(Int32):{let; insertAdd :: Manipulation_ DB (Int32, Int32) (Only Int32) insertAdd = insertInto #tab  ( Values_ $8 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*5 Set (param @2 @('NotNull 'PGint4)) `as` #col2 ) OnConflictDoRaise3 ( Returning_ ((#col1 + #col2) `as` #fromOnly) )% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup & pqThen ( doL results <- forPrepared [(2::Int32,2::Int32),(3,3),(4,4)] insertAdd. answers <- traverse firstRow resultsJ liftIO $ print [answer :: Int32 | Just (Only answer) <- answers] ) & pqThen (define teardown):}[4,6,8]squeal-postgresql runs a returning-free  on a a container by first preparing the statement, then running the prepared statement on each element.)type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Data.Int(Int32):{let1 insertion :: Manipulation_ DB (Int32, Int32) ()* insertion = insertInto_ #tab $ Values_ $4 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*1 Set (param @2 @('NotNull 'PGint4)) `as` #col2% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setupL & pqThen (traversePrepared_ insertion [(2::Int32,2::Int32),(3,3),(4,4)]) & pqThen (define teardown):}squeal-postgresql is a flipped )type Column = 'NoDef :=> 'NotNull 'PGint46type Columns = '["col1" ::: Column, "col2" ::: Column]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]type DB = Public Schemaimport Data.Int(Int32):{let1 insertion :: Manipulation_ DB (Int32, Int32) ()* insertion = insertInto_ #tab $ Values_ $4 Set (param @1 @('NotNull 'PGint4)) `as` #col1 :*1 Set (param @2 @('NotNull 'PGint4)) `as` #col2% setup :: Definition (Public '[]) DB setup = createTable #tab$ ( notNullable int4 `as` #col1 :*! notNullable int4 `as` #col2 ) Nil( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin^ withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setupG & pqThen (forPrepared_ [(2::Int32,2::Int32),(3,3),(4,4)] insertion) & pqThen (define teardown):} squeal-postgresqlquery or manipulationsqueal-postgresql parameterssqueal-postgresqlquery or manipulationsqueal-postgresql parameterssqueal-postgresqlquery or manipulationsqueal-postgresqlquery or manipulationsqueal-postgresqllist of parameterssqueal-postgresqlquery or manipulationsqueal-postgresqllist of parameterssqueal-postgresql1l, 0m, or 2 , and friendssqueal-postgresql1, 0, or 2 , and friendssqueal-postgresql+k and friendssqueal-postgresql+k and friendssqueal-postgresql1l, 0m, or 2 , and friendssqueal-postgresql1l, 0m, or 2 , and friendssqueal-postgresql1, 0, or 2 , and friendssqueal-postgresql1, 0, or 2 , and friends/transaction control language(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.>H_ x:squeal-postgresqlThe D transaction property has no effect unless the transaction is also  and . When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a j transaction and without any risk of contributing to or being canceled by a serialization failure. This 4 is well suited for long-running reports or backups.squeal-postgresqlBThe transaction access mode determines whether the transaction is  or . ' is the default. When a transaction is /, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and  COPY FROMB if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTEZ if the command they would execute is among those listed. This is a high-level notion of * that does not prevent all writes to disk.squeal-postgresqlSThe SQL standard defines four levels of transaction isolation. The most strict is h, which is defined by the standard in a paragraph which says that any concurrent execution of a set of ; transactions is guaranteed to produce the same effect as running them one at a time in some order. The other three levels are defined in terms of phenomena, resulting from interaction between concurrent transactions, which must not occur at each level. The phenomena which are prohibited at various levels are: Dirty readK: A transaction reads data written by a concurrent uncommitted transaction.Nonrepeatable read: A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read). Phantom read: A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.Serialization anomaly: The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's  mode behaves like . This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.squeal-postgresqlDirty read is not possible. Nonrepeatable read is not possible. Phantom read is not possible. Serialization anomaly is not possible.squeal-postgresqlDirty read is not possible. Nonrepeatable read is not possible. Phantom read is not possible. Serialization anomaly is possible.squeal-postgresql{Dirty read is not possible. Nonrepeatable read is possible. Phantom read is possible. Serialization anomaly is possible.squeal-postgresql{Dirty read is not possible. Nonrepeatable read is possible. Phantom read is possible. Serialization anomaly is possible.squeal-postgresql>The available transaction characteristics are the transaction , the transaction  ( or  ), and the .squeal-postgresqlRun a computation ; first , then run the computation,  & and rethrow the exception, otherwise  and  the result.squeal-postgresqlRun a computation , in .squeal-postgresql a computation;first ,then  the computation,*if it raises a serialization failure then  and restart the transaction,&if it raises any other exception then  and rethrow the exception, otherwise  and  the result.squeal-postgresqlRun a computation ; Like  but always , useful in testing.squeal-postgresqlRun a computation  in . squeal-postgresqlBEGIN a transaction.squeal-postgresqlCOMMIT a transaction.squeal-postgresqlROLLBACK a transaction.squeal-postgresql with a  ,   and  .squeal-postgresql with a  ,   and  @. This mode is well suited for long-running reports or backups.squeal-postgresql Render an .squeal-postgresql Render an .squeal-postgresql Render a .squeal-postgresql Render a .squeal-postgresqlrun inside a transactionsqueal-postgresqlrun inside a transactionsqueal-postgresqlrun inside a transactionsqueal-postgresql#run inside an ephemeral transactionsqueal-postgresql#run inside an ephemeral transaction0update statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.7;=>?@AHMSUVX_gkq Zsqueal-postgresqlAn \ command changes the values of the specified columns in all rows that satisfy the condition.^type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]Ytype Schema = '["tab1" ::: 'Table ('[] :=> Columns), "tab2" ::: 'Table ('[] :=> Columns)]:{ let/ manp :: Manipulation with (Public Schema) '[]" '["col1" ::: 'NotNull 'PGint4," "col2" ::: 'NotNull 'PGint4] manp = update (#tab1 `as` #t1)& (Set (2 + #t2 ! #col2) `as` #col1)$ (Using (table (#tab2 `as` #t2)))! (#t1 ! #col1 ./= #t2 ! #col2) (Returning (#t1 & DotStar))in printSQL manp:}UPDATE "tab1" AS "t1" SET "col1" = ((2 :: int4) + "t2"."col2") FROM "tab2" AS "t2" WHERE ("t1"."col1" <> "t2"."col2") RETURNING "t1".*squeal-postgresqlUpdate a row returning .^type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let3 manp :: Manipulation with (Public Schema) '[] '[]: manp = update_ #tab (Set 2 `as` #col1) (#col1 ./= #col2)in printSQL manp:}GUPDATE "tab" AS "tab" SET "col1" = (2 :: int4) WHERE ("col1" <> "col2")squeal-postgresqltable to updatesqueal-postgresql9update expressions, modified values to replace old valuessqueal-postgresqltFROM A table expression allowing columns from other tables to appear in the WHERE condition and update expressions.squeal-postgresql6WHERE condition under which to perform update on a rowsqueal-postgresqlresults to returnsqueal-postgresqltable to updatesqueal-postgresql%modified values to replace old valuessqueal-postgresql0condition under which to perform update on a row1insert statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.7;=>?@AHMSUVX_gkq q squeal-postgresqlA 5 specifies the constraint violation that triggers a .squeal-postgresql specifies an alternative  action. It can be either , or a e clause specifying the exact details of the update action to be performed in case of a conflict. The  and WHERE s in  n have access to the existing row using the table's name, and to rows proposed for insertion using the special  #excluded row.  : simply avoids inserting a row as its alternative action.  g updates the existing row that conflicts with the row proposed for insertion as its alternative action.squeal-postgresqlA > specifies an action to perform upon a constraint violation.  will raise an error.  ! simply avoids inserting a row.  N updates the existing row that conflicts with the row proposed for insertion.squeal-postgresqlA  describes what to  a table.squeal-postgresql describes a single  list of   # s whose 5 must match the tables'.squeal-postgresqlXWhen a table is created, it contains no data. The first thing to do before a database can be of much use is to insert data. Data is conceptually inserted one row at a time. Of course you can also insert more than one row, but there is no way to insert less than one row. Even if you know only some column values, a complete row must be created.jtype CustomersColumns = '["name" ::: 'NoDef :=> 'NotNull 'PGtext, "email" ::: 'NoDef :=> 'NotNull 'PGtext]9type CustomersConstraints = '["uq" ::: 'Unique '["name"]]\type CustomersSchema = '["customers" ::: 'Table (CustomersConstraints :=> CustomersColumns)]:{ let< manp :: Manipulation with (Public CustomersSchema) '[] '[] manp = insertInto #customersQ (Values_ (Set "John Smith" `as` #name :* Set "john@smith.com" `as` #email))$ (OnConflict (OnConstraint #uq)\ (DoUpdate (Set (#excluded ! #email <> "; " <> #customers ! #email) `as` #email) [])) (Returning_ Nil)in printSQL manp:}INSERT INTO "customers" AS "customers" ("name", "email") VALUES ((E'John Smith' :: text), (E'john@smith.com' :: text)) ON CONFLICT ON CONSTRAINT "uq" DO UPDATE SET "email" = ("excluded"."email" || ((E'; ' :: text) || "customers"."email"))squeal-postgresqlLike  but with  and no .[type Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint4, "col2" ::: 'Def :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let3 manp :: Manipulation with (Public Schema) '[] '[] manp =G insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))in printSQL manp:}IINSERT INTO "tab" AS "tab" ("col1", "col2") VALUES ((2 :: int4), DEFAULT)squeal-postgresql a Haskell record in .squeal-postgresql Haskell records in .squeal-postgresql Render a squeal-postgresql Render a . squeal-postgresqlWHERE ssqueal-postgresqlconflict targetsqueal-postgresqlconflict actionsqueal-postgresql row of valuessqueal-postgresqladditional rows of valuessqueal-postgresql row of valuessqueal-postgresqlfrom a table expressionsqueal-postgresqlsubquery to insertsqueal-postgresql row of valuessqueal-postgresqltablesqueal-postgresqlwhat to insertsqueal-postgresqlwhat to do in case of conflictsqueal-postgresqlwhat to returnsqueal-postgresqltablesqueal-postgresqlwhat to insertsqueal-postgresqlrecordsqueal-postgresqlrecordsqueal-postgresqlmore2delete statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.7;=>?@AHMSUVX_gkq squeal-postgresqlDelete rows from a table.^type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]Ytype Schema = '["tab1" ::: 'Table ('[] :=> Columns), "tab2" ::: 'Table ('[] :=> Columns)]:{letk manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]o manp = deleteFrom #tab1 (Using (table #tab2)) (#tab1 ! #col1 .== #tab2 ! #col2) (Returning (#tab1 & DotStar))in printSQL manp:}lDELETE FROM "tab1" AS "tab1" USING "tab2" AS "tab2" WHERE ("tab1"."col1" = "tab2"."col2") RETURNING "tab1".*squeal-postgresqlDelete rows returning .6type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{letD manp :: Manipulation with (Public Schema) '[ 'NotNull 'PGint4] '[]= manp = deleteFrom_ (#tab `as` #t) (#t ! #col1 .== param @1)in printSQL manp:}:DELETE FROM "tab" AS "t" WHERE ("t"."col1" = ($1 :: int4))squeal-postgresqltable to delete fromsqueal-postgresql%condition under which to delete a rowsqueal-postgresqlresults to returnsqueal-postgresqltable to delete fromsqueal-postgresql%condition under which to delete a row3call statements(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.7;=>?@AHMSUVX_gkq squeal-postgresqlprintSQL $ unsafeCall "p" true CALL p(TRUE)squeal-postgresql.Call a user defined procedure of one variable.;type Schema = '[ "p" ::: 'Procedure '[ 'NotNull 'PGint4 ] ]:{let/ p :: Manipulation '[] (Public Schema) '[] '[] p = call #p 1in printSQL p:}CALL "p"((1 :: int4))squeal-postgresql*printSQL $ unsafeCallN "p" (true *: false)CALL p(TRUE, FALSE)squeal-postgresqlCall a user defined procedure.Mtype Schema = '[ "p" ::: 'Procedure '[ 'NotNull 'PGint4, 'NotNull 'PGtext ] ]:{let/ p :: Manipulation '[] (Public Schema) '[] '[] p = callN #p (1 *: "hi")in printSQL p:}&CALL "p"((1 :: int4), (E'hi' :: text))squeal-postgresqlprocedure to callsqueal-postgresql argumentssqueal-postgresqlprocedure to callsqueal-postgresql argumentssqueal-postgresqlprocedure to callsqueal-postgresql argumentssqueal-postgresqlprocedure to callsqueal-postgresql arguments4constraint expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek dsqueal-postgresqlvWhen the data in the referenced columns is changed, certain actions are performed on the data in this table's columns.squeal-postgresqlProduce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows.squeal-postgresqlProduce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as ) except that the check is not deferrable.squeal-postgresqlDelete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.squeal-postgresql&Set the referencing column(s) to null.squeal-postgresql6Set the referencing column(s) to their default values.squeal-postgresql Analagous to  there is also A which is invoked when a referenced column is changed (updated).squeal-postgresql= indicates what to do with rows that reference a deleted row.squeal-postgresqlHA constraint synonym between types involved in a foreign key constraint.squeal-postgresqlData types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one row for each product number. 0s 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-postgresqlA  constraint is the most generic 0s type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.:{type Schema = '[A "tab" ::: 'Table ('[ "inequality" ::: 'Check '["a","b"]] :=> '[( "a" ::: 'NoDef :=> 'NotNull 'PGint4,' "b" ::: 'NoDef :=> 'NotNull 'PGint4 ])]:}:{let7 definition :: Definition (Public '[]) (Public Schema) definition = createTable #tab$ ( (int & notNullable) `as` #a :*# (int & notNullable) `as` #b )4 ( check (#a :* #b) (#a .> #b) `as` #inequality ):}printSQL definitioneCREATE TABLE "tab" ("a" int NOT NULL, "b" int NOT NULL, CONSTRAINT "inequality" CHECK (("a" > "b")));squeal-postgresqlA y constraint ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table.:{type Schema = '[> "tab" ::: 'Table( '[ "uq_a_b" ::: 'Unique '["a","b"]] :=> '[% "a" ::: 'NoDef :=> 'Null 'PGint4,$ "b" ::: 'NoDef :=> 'Null 'PGint4 ])]:}:{let7 definition :: Definition (Public '[]) (Public Schema) definition = createTable #tab! ( (int & nullable) `as` #a :* (int & nullable) `as` #b )& ( unique (#a :* #b) `as` #uq_a_b ):}printSQL definitionWCREATE TABLE "tab" ("a" int NULL, "b" int NULL, CONSTRAINT "uq_a_b" UNIQUE ("a", "b")); squeal-postgresqlA   constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.:{type Schema = '[> "tab" ::: 'Table ('[ "pk_id" ::: 'PrimaryKey '["id"]] :=> '[' "id" ::: 'Def :=> 'NotNull 'PGint4,* "name" ::: 'NoDef :=> 'NotNull 'PGtext ])]:}:{let7 definition :: Definition (Public '[]) (Public Schema) definition = createTable #tab ( serial `as` #id :*' (text & notNullable) `as` #name )" ( primaryKey #id `as` #pk_id ):}printSQL definition^CREATE TABLE "tab" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_id" PRIMARY KEY ("id")); squeal-postgresqlA   specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.:{ type Schema = '[ "users" ::: 'Table (2 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>, '[ "id" ::: 'Def :=> 'NotNull 'PGint40 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]) , "emails" ::: 'Table (. '[ "pk_emails" ::: 'PrimaryKey '["id"]L , "fk_user_id" ::: 'ForeignKey '["user_id"] "public" "users" '["id"] ] :=>, '[ "id" ::: 'Def :=> 'NotNull 'PGint43 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4. , "email" ::: 'NoDef :=> 'Null 'PGtext ]) ]:}:{let2 setup :: Definition (Public '[]) (Public Schema) setup = createTable #users ( serial `as` #id :*( (text & notNullable) `as` #name )* ( primaryKey #id `as` #pk_users ) >>> createTable #emails ( serial `as` #id :*+ (int & notNullable) `as` #user_id :*& (text & nullable) `as` #email )( ( primaryKey #id `as` #pk_emails :*% foreignKey #user_id #users #idA (OnDelete Cascade) (OnUpdate Cascade) `as` #fk_user_id )in printSQL setup:}cCREATE TABLE "users" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_users" PRIMARY KEY ("id"));CREATE TABLE "emails" ("id" serial, "user_id" int NOT NULL, "email" text NULL, CONSTRAINT "pk_emails" PRIMARY KEY ("id"), CONSTRAINT "fk_user_id" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE);A  $ can even be a table self-reference.:{ type Schema = '[ "employees" ::: 'Table (9 '[ "employees_pk" ::: 'PrimaryKey '["id"]_ , "employees_employer_fk" ::: 'ForeignKey '["employer_id"] "public" "employees" '["id"] ] :=>7 '[ "id" ::: 'Def :=> 'NotNull 'PGint47 , "name" ::: 'NoDef :=> 'NotNull 'PGtext7 , "employer_id" ::: 'NoDef :=> 'Null 'PGint4 ]) ]:}:{ let2 setup :: Definition (Public '[]) (Public Schema) setup = createTable #employees ( serial `as` #id :*) (text & notNullable) `as` #name :*/ (integer & nullable) `as` #employer_id )+ ( primaryKey #id `as` #employees_pk :*- foreignKey #employer_id #employees #idL (OnDelete Cascade) (OnUpdate Cascade) `as` #employees_employer_fk )in printSQL setup:} CREATE TABLE "employees" ("id" serial, "name" text NOT NULL, "employer_id" integer NULL, CONSTRAINT "employees_pk" PRIMARY KEY ("id"), CONSTRAINT "employees_employer_fk" FOREIGN KEY ("employer_id") REFERENCES "employees" ("id") ON DELETE CASCADE ON UPDATE CASCADE);squeal-postgresql0specify the subcolumns which are getting checkedsqueal-postgresql a closed  on those subcolumnssqueal-postgresql9specify subcolumns which together are unique for each row squeal-postgresql9specify the subcolumns which together form a primary key. squeal-postgresqlcolumn or columns in the tablesqueal-postgresqlreference tablesqueal-postgresql2reference column or columns in the reference tablesqueal-postgresql$what to do when reference is deletedsqueal-postgresql$what to do when reference is updated    data definition language(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek l#squeal-postgresqlA #B is a statement that changes the schemas of the database, like a 9V, 9T, or 9R command. #s may be composed using the  operator.&squeal-postgresqlA E without input or output can be run as a statement along with other #s, by embedding it using &.&squeal-postgresqlno input or output#$%&#$%&5indexed session monad(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.8=>?@ACHSVXkq ~ 0squeal-postgresql0@ is a class for indexed monad transformers that support running #s using 1# which acts functorially in effect. define id = return () Sdefine (statement1 >>> statement2) = define statement1 & pqThen (define statement2)2squeal-postgresql2& reshuffles the type parameters of an 5, exposing its  instance.5squeal-postgresqlAn  (https://bentnib.org/paramnotions-jfp.pdfAtkey indexed monad is a   /https://ncatlab.org/nlab/show/enriched+categoryenriched category,. An indexed monad transformer transforms a u into an indexed monad, and is a monad transformer when its source and target are the same, enabling use of standard do$ notation for endo-index operations.6squeal-postgresqlindexed analog of 7squeal-postgresqlindexed analog of 8squeal-postgresqlindexed analog of  9squeal-postgresqlindexed analog of flipped  :squeal-postgresqlindexed analog of  ;squeal-postgresqlRun a pure SQL # functorially in effect indexedDefine id = id IindexedDefine (def1 >>> def2) = indexedDefine def1 >>> indexedDefine def2 0123457869:; 57869:23401;6sessions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-./8=>?@ACHSUVXkq  squeal-postgresqlA snapshot of the state of a =/ computation, used in MonadBaseControl Instance=squeal-postgresqlKWe keep track of the schema via an Atkey indexed state monad transformer, =.@squeal-postgresqlRun a = and keep the result and the ".Asqueal-postgresql Execute a =% and discard the result but keep the ".Bsqueal-postgresql Evaluate a = and discard the " but keep the result.Csqueal-postgresqlDo  and  before and after a computation.=>?@ABC=>?@ABC connection pools(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-./4=>?@AHSVX Xsqueal-postgresqlCreate a striped pool of connections. Although the garbage collector will destroy all idle connections when the pool is garbage collected it's recommended to manually  Y when you're done with the pool so that the connections are freed up as soon as possible.Ysqueal-postgresql%Temporarily take a connection from a #B, perform an action with it, and return it to the pool afterwards.'If the pool has an idle connection available, it is used immediately. Otherwise, if the maximum number of connections has not yet been reached, a new connection is created and used. If the maximum number of connections has been reached, this function blocks until a connection becomes available.Zsqueal-postgresqlvDestroy all connections in all stripes in the pool. Note that this will ignore any exceptions in the destroy function.This function is useful when you detect that all connections in the pool are broken. For example after a database has been restarted all connections opened before the restart will be broken. In that case it's better to close those connections so that YU won't take a broken connection from the pool but will open a new connection instead.Another use-case for this function is that when you know you are done with the pool you can destroy all idle connections immediately instead of waiting on the garbage collector to destroy them, thus freeing up those connections sooner.Xsqueal-postgresqlThe 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 .squeal-postgresql[The number of stripes (distinct sub-pools) to maintain. The smallest acceptable value is 1.squeal-postgresqlAmount of time for which an unused connection is kept open. The smallest acceptable value is 0.5 seconds. The elapsed time before destroying a connection may be a little longer than requested, as the reaper thread wakes at 1-second intervals.squeal-postgresqlMaximum number of connections to keep open per stripe. The smallest acceptable value is 1. Requests for connections will block if this limit is reached on a single stripe, even if other stripes have idle connections available.Ysqueal-postgresqlpoolsqueal-postgresqlsessionZsqueal-postgresqlpool#XYZ#XYZ7create and drop views(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek [squeal-postgresqlCreate a view.rtype ABC = '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4]9type BC = '["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4]:{ let definition :: Definition6 '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC)]]J '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc" ::: 'View BC]] definition =; createView #bc (select_ (#b :* #c) (from (table #abc)))in printSQL definition:}FCREATE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";\squeal-postgresqlCreate or replace a view.rtype ABC = '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4]9type BC = '["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4]:{ let definition :: Definition6 '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC)]]J '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc" ::: 'View BC]] definition =D createOrReplaceView #bc (select_ (#b :* #c) (from (table #abc)))in printSQL definition:}QCREATE OR REPLACE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";]squeal-postgresql Drop a view.:{ let definition :: Definition '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])J , "bc" ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]] '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]] definition = dropView #bcin printSQL definition:}DROP VIEW "bc";^squeal-postgresqlDrop a view if it exists.:{ let definition :: Definition '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])J , "bc" ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]] '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]]# definition = dropViewIfExists #bcin printSQL definition:}DROP VIEW IF EXISTS "bc";_squeal-postgresql_, changes the name of a view from the schema.4type DB = '[ "public" ::: '[ "foo" ::: 'View '[] ] ]:{B let def :: Definition DB '["public" ::: '["bar" ::: 'View '[] ] ]$ def = alterViewRename #foo #bar in printSQL def:}!ALTER VIEW "foo" RENAME TO "bar";`squeal-postgresql-This form moves the view into another schema.Btype DB0 = '[ "sch0" ::: '[ "vw" ::: 'View '[] ], "sch1" ::: '[] ]Btype DB1 = '[ "sch0" ::: '[], "sch1" ::: '[ "vw" ::: 'View '[] ] ]:{let def :: Definition DB0 DB10 def = alterViewSetSchema (#sch0 ! #vw) #sch1in printSQL def:})ALTER VIEW "sch0"."vw" SET SCHEMA "sch1";[squeal-postgresqlthe name of the view to addsqueal-postgresqlquery\squeal-postgresqlthe name of the view to addsqueal-postgresqlquery]squeal-postgresqlview to remove^squeal-postgresqlview to remove_squeal-postgresqlview to renamesqueal-postgresqlwhat to rename it`squeal-postgresql view to movesqueal-postgresqlwhere to move it[\]^_`[\]^_`8create and drop types(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek 8v asqueal-postgresql'Enumerated types are created using the a command, for exampleprintSQL $ (createTypeEnum #mood (label @"sad" :* label @"ok" :* label @"happy") :: Definition (Public '[]) '["public" ::: '["mood" ::: 'Typedef ('PGenum '["sad","ok","happy"])]])2CREATE TYPE "mood" AS ENUM ('sad', 'ok', 'happy');bsqueal-postgresqlGEnumerated types can also be generated from a Haskell type, for example:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo Schwarma:{letq createSchwarma :: Definition (Public '[]) '["public" ::: '["schwarma" ::: 'Typedef (PG (Enumerated Schwarma))]]9 createSchwarma = createTypeEnumFrom @Schwarma #schwarmain printSQL createSchwarma:};CREATE TYPE "schwarma" AS ENUM ('Beef', 'Lamb', 'Chicken');csqueal-postgresqlcg creates a composite type. The composite type is specified by a list of attribute names and data types.:{type PGcomplex = 'PGcomposite' '[ "real" ::: 'NotNull 'PGfloat8) , "imaginary" ::: 'NotNull 'PGfloat8 ]:}:{letV setup :: Definition (Public '[]) '["public" ::: '["complex" ::: 'Typedef PGcomplex]]& setup = createTypeComposite #complex1 (float8 `as` #real :* float8 `as` #imaginary)in printSQL setup:}=CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);dsqueal-postgresqlFComposite types can also be generated from a Haskell type, for exampleQdata Complex = Complex {real :: Double, imaginary :: Double} deriving GHC.Genericinstance SOP.Generic Complex$instance SOP.HasDatatypeInfo Complex@type Schema = '["complex" ::: 'Typedef (PG (Composite Complex))]:{let: createComplex :: Definition (Public '[]) (Public Schema); createComplex = createTypeCompositeFrom @Complex #complexin printSQL createComplex:}=CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);esqueal-postgresqlex creates a new domain. A domain is essentially a data type with constraints (restrictions on the allowed set of values).Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same v constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.:{letY createPositive :: Definition (Public '[]) (Public '["positive" ::: 'Typedef 'PGfloat4])< createPositive = createDomain #positive real (#value .> 0)in printSQL createPositive:}ECREATE DOMAIN "positive" AS real CHECK (("value" > (0.0 :: float4)));fsqueal-postgresqlRange types are data types representing a range of values of some element type (called the range's subtype). The subtype must have a total order so that it is well-defined whether element values are within, before, or after a range of values.ORange types are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly. The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.:{leth createSmallIntRange :: Definition (Public '[]) (Public '["int2range" ::: 'Typedef ('PGrange 'PGint2)])7 createSmallIntRange = createTypeRange #int2range int2in printSQL createSmallIntRange:}2CREATE TYPE "int2range" AS RANGE (subtype = int2);gsqueal-postgresql Drop a type.:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo Schwarma~printSQL (dropType #schwarma :: Definition '["public" ::: '["schwarma" ::: 'Typedef (PG (Enumerated Schwarma))]] (Public '[]))DROP TYPE "schwarma";hsqueal-postgresqlDrop a type if it exists.isqueal-postgresqli, changes the name of a type from the schema.;type DB = '[ "public" ::: '[ "foo" ::: 'Typedef 'PGbool ] ]:{I let def :: Definition DB '["public" ::: '["bar" ::: 'Typedef 'PGbool ] ]$ def = alterTypeRename #foo #bar in printSQL def:}!ALTER TYPE "foo" RENAME TO "bar";jsqueal-postgresql-This form moves the type into another schema.Ktype DB0 = '[ "sch0" ::: '[ "ty" ::: 'Typedef 'PGfloat8 ], "sch1" ::: '[] ]Ktype DB1 = '[ "sch0" ::: '[], "sch1" ::: '[ "ty" ::: 'Typedef 'PGfloat8 ] ]:{let def :: Definition DB0 DB10 def = alterTypeSetSchema (#sch0 ! #ty) #sch1in printSQL def:})ALTER TYPE "sch0"."ty" SET SCHEMA "sch1"; asqueal-postgresql(name of the user defined enumerated typesqueal-postgresqllabels of the enumerated typebsqueal-postgresql(name of the user defined enumerated typecsqueal-postgresql'name of the user defined composite typesqueal-postgresql&list of attribute names and data typesdsqueal-postgresql'name of the user defined composite typeesqueal-postgresql domain aliassqueal-postgresqlunderlying typesqueal-postgresqlconstraint on typefsqueal-postgresql range aliassqueal-postgresqlunderlying typegsqueal-postgresqlname of the user defined typehsqueal-postgresqlname of the user defined typeisqueal-postgresqltype to renamesqueal-postgresqlwhat to rename itjsqueal-postgresql type to movesqueal-postgresqlwhere to move it abcdefghij abcdfeghij9create, drop and alter tables(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek Dksqueal-postgresqlAn k8 describes the alteration to perform on a single column.nsqueal-postgresqlAn n is either NULL or has DEFAULT.osqueal-postgresqloS adds a new column, initially filled with whatever default value is given or with NULL.:{ let definition :: DefinitionX '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint40 , "col2" ::: 'Def :=> 'Null 'PGtext ])]]S definition = alterTable #tab (addColumn #col2 (text & nullable & default_ "foo"))in printSQL definition:}GALTER TABLE "tab" ADD COLUMN "col2" text NULL DEFAULT (E'foo' :: text);:{ let definition :: DefinitionX '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint42 , "col2" ::: 'NoDef :=> 'Null 'PGtext ])]]B definition = alterTable #tab (addColumn #col2 (text & nullable))in printSQL definition:}.ALTER TABLE "tab" ADD COLUMN "col2" text NULL;psqueal-postgresqlAn p@ describes the alteration to perform on the columns of a table.ssqueal-postgresqls adds a table to the schema.:set -XOverloadedLabels:{type Table = '[] :=>% '[ "a" ::: 'NoDef :=> 'Null 'PGint4) , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]:}:{letE setup :: Definition (Public '[]) (Public '["tab" ::: 'Table Table]) setup = createTable #tab7 (nullable int `as` #a :* nullable real `as` #b) Nilin printSQL setup:}1CREATE TABLE "tab" ("a" int NULL, "b" real NULL);tsqueal-postgresqlt creates a table if it doesn't exist, but does not add it to the schema. Instead, the schema already has the table so if the table did not yet exist, the schema was wrong. tH fixes this. Interestingly, this property makes it an idempotent in the  of #s.*:set -XOverloadedLabels -XTypeApplications:{type Table = '[] :=>% '[ "a" ::: 'NoDef :=> 'Null 'PGint4) , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]:}/type Schemas = Public '["tab" ::: 'Table Table]:{let% setup :: Definition Schemas Schemas% setup = createTableIfNotExists #tab7 (nullable int `as` #a :* nullable real `as` #b) Nilin printSQL setup:}?CREATE TABLE IF NOT EXISTS "tab" ("a" int NULL, "b" real NULL);usqueal-postgresqlu! removes a table from the schema.:{letS definition :: Definition '["public" ::: '["muh_table" ::: 'Table t]] (Public '[])# definition = dropTable #muh_table:}printSQL definitionDROP TABLE "muh_table";vsqueal-postgresqlDrop a table if it exists.wsqueal-postgresqlw3 changes the definition of a table from the schema.xsqueal-postgresqlw3 changes the definition of a table from the schema.ysqueal-postgresqly- changes the name of a table from the schema.Dtype Schemas = '[ "public" ::: '[ "foo" ::: 'Table ('[] :=> '[]) ] ]:{X let migration :: Definition Schemas '["public" ::: '["bar" ::: 'Table ('[] :=> '[]) ] ]+ migration = alterTableRename #foo #bar in printSQL migration:}"ALTER TABLE "foo" RENAME TO "bar";zsqueal-postgresqlz: changes the name of a table from the schema if it exists.Dtype Schemas = '[ "public" ::: '[ "foo" ::: 'Table ('[] :=> '[]) ] ]:{, let migration :: Definition Schemas Schemas3 migration = alterTableIfExistsRename #goo #gar in printSQL migration:},ALTER TABLE IF EXISTS "goo" RENAME TO "gar";{squeal-postgresql.This form moves the table into another schema.Ntype DB0 = '[ "sch0" ::: '[ "tab" ::: 'Table ('[] :=> '[]) ], "sch1" ::: '[] ]Ntype DB1 = '[ "sch0" ::: '[], "sch1" ::: '[ "tab" ::: 'Table ('[] :=> '[]) ] ]:{let def :: Definition DB0 DB12 def = alterTableSetSchema (#sch0 ! #tab) #sch1in printSQL def:}+ALTER TABLE "sch0"."tab" SET SCHEMA "sch1";|squeal-postgresqlAn | adds a table constraint.:{let definition :: DefinitionZ '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]x '["public" ::: '["tab" ::: 'Table ('["positive" ::: 'Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]Q definition = alterTable #tab (addConstraint #positive (check #col (#col .> 0)))in printSQL definition:}JALTER TABLE "tab" ADD CONSTRAINT "positive" CHECK (("col" > (0 :: int4)));}squeal-postgresqlA } drops a table constraint.:{let definition :: Definitionw '["public" ::: '["tab" ::: 'Table ('["positive" ::: Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]Z '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]9 definition = alterTable #tab (dropConstraint #positive)in printSQL definition:}-ALTER TABLE "tab" DROP CONSTRAINT "positive";~squeal-postgresqlA ~ removes a column. Whatever data was in the column disappears. Table constraints involving the column are dropped, too. However, if the column is referenced by a foreign key constraint of another table, PostgreSQL will not silently drop that constraint.:{ let definition :: Definition. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint42 , "col2" ::: 'NoDef :=> 'Null 'PGtext ])]]X '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]1 definition = alterTable #tab (dropColumn #col2)in printSQL definition:}%ALTER TABLE "tab" DROP COLUMN "col2";squeal-postgresqlA  renames a column.:{let definition :: DefinitionW '["public" ::: '["tab" ::: 'Table ('[] :=> '["foo" ::: 'NoDef :=> 'Null 'PGint4])]]W '["public" ::: '["tab" ::: 'Table ('[] :=> '["bar" ::: 'NoDef :=> 'Null 'PGint4])]]7 definition = alterTable #tab (renameColumn #foo #bar)in printSQL definition:}/ALTER TABLE "tab" RENAME COLUMN "foo" TO "bar";squeal-postgresqlAn  alters a single column.squeal-postgresqlA  sets a new default for a column. Note that this doesn't affect any existing rows in the table, it just changes the default for future insert and update commands.:{let definition :: DefinitionW '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]U '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]]@ definition = alterTable #tab (alterColumn #col (setDefault 5))in printSQL definition:}=ALTER TABLE "tab" ALTER COLUMN "col" SET DEFAULT (5 :: int4);squeal-postgresqlA ( removes any default value for a column.:{let definition :: DefinitionU '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]]W '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]= definition = alterTable #tab (alterColumn #col dropDefault)in printSQL definition:}2ALTER TABLE "tab" ALTER COLUMN "col" DROP DEFAULT;squeal-postgresqlA  adds a NOT NULL constraint to a column. The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.:{let definition :: DefinitionW '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]Z '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]< definition = alterTable #tab (alterColumn #col setNotNull)in printSQL definition:}2ALTER TABLE "tab" ALTER COLUMN "col" SET NOT NULL;squeal-postgresqlA  drops a NOT NULL constraint from a column.:{let definition :: DefinitionZ '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]W '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]= definition = alterTable #tab (alterColumn #col dropNotNull)in printSQL definition:}3ALTER TABLE "tab" ALTER COLUMN "col" DROP NOT NULL;squeal-postgresqlAn  converts a column to a different data type. This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast.:{ let definition :: DefinitionZ '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]] '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGnumeric])]] definition =J alterTable #tab (alterColumn #col (alterType (numeric & notNullable)))in printSQL definition:};ALTER TABLE "tab" ALTER COLUMN "col" TYPE numeric NOT NULL;osqueal-postgresql column to addsqueal-postgresqltype of the new columnssqueal-postgresqlthe name of the table to addsqueal-postgresql%the names and datatype of each columnsqueal-postgresql(constraints that must hold for the tabletsqueal-postgresqlthe name of the table to addsqueal-postgresql%the names and datatype of each columnsqueal-postgresql(constraints that must hold for the tablesqueal-postgresqlthe name of the table to addsqueal-postgresql%the names and datatype of each columnsqueal-postgresql(constraints that must hold for the tableusqueal-postgresqltable to removevsqueal-postgresqltable to removewsqueal-postgresqltable to altersqueal-postgresqlalteration to performxsqueal-postgresqltable to altersqueal-postgresqlalteration to performysqueal-postgresqltable to renamesqueal-postgresqlwhat to rename itzsqueal-postgresqltable to renamesqueal-postgresqlwhat to rename it{squeal-postgresql table to movesqueal-postgresqlwhere to move it|squeal-postgresqlconstraint to add}squeal-postgresqlconstraint to drop~squeal-postgresqlcolumn to removesqueal-postgresqlcolumn to renamesqueal-postgresqlwhat to rename the columnsqueal-postgresqlcolumn to altersqueal-postgresqlalteration to performsqueal-postgresqldefault value to setklmnopqrstuvwxyz{|}~stuvwxyz{pqr|}no~klm migrations(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.7=>?@ACHSUVX_ekq squeal-postgresqlThe - for a Squeal migration.squeal-postgresqlA   can run or possibly rewind a  of s.squeal-postgresqlRun a  of s.squeal-postgresqlA / consists of a name and a migration definition.squeal-postgresqlThe name of a . Each  should be unique.squeal-postgresqlThe migration of a .squeal-postgresqlRun migrations.squeal-postgresqlRun rewindable migrations.squeal-postgresqlRewind migrations.squeal-postgresql Creates a  if it does not already exist.squeal-postgresql Inserts a  into the /, returning the time at which it was inserted.squeal-postgresql Deletes a  from the /, returning the time at which it was inserted.squeal-postgresql Selects a  from the /, returning the time at which it was inserted.squeal-postgresql< creates a simple executable from a connection string and a  of s. squeal-postgresql< creates a simple executable from a connection string and a  of   s. squeal-postgresqlpure rewindable migrationssqueal-postgresqlimpure rewindable migrationssqueal-postgresql pure rewindssqueal-postgresqlimpure rewindssqueal-postgresqlpure migrationssqueal-postgresqlimpure migrationssqueal-postgresqlconnection stringsqueal-postgresql migrationssqueal-postgresqlconnection stringsqueal-postgresql migrations  :create and drop schemas(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek Vsqueal-postgresql enters a new schema into the current database. The schema name must be distinct from the name of any existing schema in the current database.A schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names can duplicate those of other objects existing in other schemas. Named objects are accessed by $es with the schema name as a prefix.:{letW definition :: Definition '["public" ::: '[]] '["public" ::: '[], "my_schema" ::: '[]]& definition = createSchema #my_schemain printSQL definition:}CREATE SCHEMA "my_schema";squeal-postgresql)Create a schema if it does not yet exist.squeal-postgresqlgDrop a schema. Automatically drop objects (tables, functions, etc.) that are contained in the schema.:{leta definition :: Definition '["muh_schema" ::: schema, "public" ::: public] '["public" ::: public], definition = dropSchemaCascade #muh_schema:}printSQL definition!DROP SCHEMA "muh_schema" CASCADE;squeal-postgresqltDrop a schema if it exists. Automatically drop objects (tables, functions, etc.) that are contained in the schema.squeal-postgresql schema aliassqueal-postgresql schema aliassqueal-postgresql schema aliassqueal-postgresql schema alias;create and drop procedures(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek !Asqueal-postgresql Body of a user defined proceduresqueal-postgresqlCreate a procedure.,type Proc = 'Procedure '[ 'NotNull 'PGint4 ]Gtype Thing = 'Table ('[] :=> '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4 ]):{letj definition :: Definition (Public '["things" ::: Thing ]) (Public '["things" ::: Thing, "proc" ::: Proc])0 definition = createProcedure #proc (one int4) & . languageSqlManipulation7 $ [deleteFrom_ #things (#id .== param @1)]in printSQL definition:}sCREATE PROCEDURE "proc" (int4) language sql as $$ DELETE FROM "things" AS "things" WHERE ("id" = ($1 :: int4)); $$;squeal-postgresqloCreate or replace a procedure. It is not possible to change the name or argument types of a procedure this way.,type Proc = 'Procedure '[ 'NotNull 'PGint4 ]Gtype Thing = 'Table ('[] :=> '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4 ]):{letj definition :: Definition (Public '["things" ::: Thing ]) (Public '["things" ::: Thing, "proc" ::: Proc])9 definition = createOrReplaceProcedure #proc (one int4) & . languageSqlManipulation7 $ [deleteFrom_ #things (#id .== param @1)]in printSQL definition:}~CREATE OR REPLACE PROCEDURE "proc" (int4) language sql as $$ DELETE FROM "things" AS "things" WHERE ("id" = ($1 :: int4)); $$;squeal-postgresqlUse a parameterized  as a procedure bodysqueal-postgresqlDrop a procedure.7type Proc = 'Procedure '[ 'Null 'PGint4, 'Null 'PGint4]:{letC definition :: Definition (Public '["proc" ::: Proc]) (Public '[])" definition = dropProcedure #procin printSQL definition:}DROP PROCEDURE "proc";squeal-postgresqlDrop a procedure.8type Proc = 'Procedure '[ 'Null 'PGint4, 'Null 'PGint4 ]:{let4 definition :: Definition (Public '[]) (Public '[])* definition = dropProcedureIfExists #procin printSQL definition:} DROP PROCEDURE IF EXISTS "proc";squeal-postgresqlprocedure aliassqueal-postgresql argumentssqueal-postgresqlprocedure definitionsqueal-postgresqlprocedure aliassqueal-postgresql argumentssqueal-postgresqlprocedure definitionsqueal-postgresqlprocedure bodysqueal-postgresqlprocedure aliassqueal-postgresqlprocedure alias<create and drop indexes(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek Cq squeal-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-postgresqlCreate an index.:{type Table = '[] :=>% '[ "a" ::: 'NoDef :=> 'Null 'PGint4) , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]:}:{ let] setup :: Definition (Public '[]) (Public '["tab" ::: 'Table Table, "ix" ::: 'Index 'Btree]) setup =L createTable #tab (nullable int `as` #a :* nullable real `as` #b) Nil >>>F createIndex #ix #tab btree [#a & AscNullsFirst, #b & AscNullsLast]in printSQL setup:}1CREATE TABLE "tab" ("a" int NULL, "b" real NULL);UCREATE INDEX "ix" ON "tab" USING btree (("a") ASC NULLS FIRST, ("b") ASC NULLS LAST);squeal-postgresql$Create an index if it doesn't exist.squeal-postgresql]B-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-postgresqleSP-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-postgresqlDrop an index.VprintSQL (dropIndex #ix :: Definition (Public '["ix" ::: 'Index 'Btree]) (Public '[]))DROP INDEX "ix";squeal-postgresqlDrop an index if it exists.squeal-postgresql index aliassqueal-postgresql table aliassqueal-postgresql index methodsqueal-postgresqlsorted columnssqueal-postgresql index aliassqueal-postgresql table aliassqueal-postgresql index methodsqueal-postgresqlsorted columns  =create and drop functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek | squeal-postgresqlBody of a user defined functionsqueal-postgresqlCreate a function.Utype Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4)):{let? definition :: Definition (Public '[]) (Public '["fn" ::: Fn])7 definition = createFunction #fn (int4 *: int4) int4 $- languageSqlExpr (param @1 * param @2 + 1)in printSQL definition:}CREATE FUNCTION "fn" (int4, int4) RETURNS int4 language sql as $$ SELECT * FROM (VALUES (((($1 :: int4) * ($2 :: int4)) + (1 :: int4)))) AS t ("ret") $$;squeal-postgresql|Create or replace a function. It is not possible to change the name or argument types or return type of a function this way.Utype Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4)):{ letJ definition :: Definition (Public '["fn" ::: Fn]) (Public '["fn" ::: Fn]) definition = createOrReplaceFunction #fn (int4 *: int4) int4 $Q languageSqlExpr (param @1 @('Null 'PGint4) * param @2 @('Null 'PGint4) + 1)in printSQL definition:}CREATE OR REPLACE FUNCTION "fn" (int4, int4) RETURNS int4 language sql as $$ SELECT * FROM (VALUES (((($1 :: int4) * ($2 :: int4)) + (1 :: int4)))) AS t ("ret") $$;squeal-postgresqlUse a parameterized # as a function bodysqueal-postgresqlUse a parametrized  as a function bodysqueal-postgresqlCreate a set function.Atype Tab = 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])ctype Fn = 'Function ('[ 'Null 'PGint4, 'Null 'PGint4] :=> 'ReturnsTable '["ret" ::: 'Null 'PGint4]):{let[ definition :: Definition (Public '["tab" ::: Tab]) (Public '["tab" ::: Tab, "fn" ::: Fn])F definition = createSetFunction #fn (int4 *: int4) (int4 `as` #ret) $[ languageSqlQuery (select_ ((param @1 * param @2 + #col) `as` #ret) (from (table #tab)))in printSQL definition:}CREATE FUNCTION "fn" (int4, int4) RETURNS TABLE ("ret" int4) language sql as $$ SELECT ((($1 :: int4) * ($2 :: int4)) + "col") AS "ret" FROM "tab" AS "tab" $$;squeal-postgresql!Create or replace a set function.Atype Tab = 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])ctype Fn = 'Function ('[ 'Null 'PGint4, 'Null 'PGint4] :=> 'ReturnsTable '["ret" ::: 'Null 'PGint4]):{leth definition :: Definition (Public '["tab" ::: Tab, "fn" ::: Fn]) (Public '["tab" ::: Tab, "fn" ::: Fn])O definition = createOrReplaceSetFunction #fn (int4 *: int4) (int4 `as` #ret) $[ languageSqlQuery (select_ ((param @1 * param @2 + #col) `as` #ret) (from (table #tab)))in printSQL definition:}CREATE OR REPLACE FUNCTION "fn" (int4, int4) RETURNS TABLE ("ret" int4) language sql as $$ SELECT ((($1 :: int4) * ($2 :: int4)) + "col") AS "ret" FROM "tab" AS "tab" $$;squeal-postgresqlDrop a function.Utype Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4)):{let? definition :: Definition (Public '["fn" ::: Fn]) (Public '[]) definition = dropFunction #fnin printSQL definition:}DROP FUNCTION "fn";squeal-postgresqlDrop a function.Utype Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4)):{let4 definition :: Definition (Public '[]) (Public '[])' definition = dropFunctionIfExists #fnin printSQL definition:}DROP FUNCTION IF EXISTS "fn";squeal-postgresqlfunction aliassqueal-postgresql argumentssqueal-postgresql return typesqueal-postgresqlfunction definitionsqueal-postgresqlfunction aliassqueal-postgresql argumentssqueal-postgresql return typesqueal-postgresqlfunction definitionsqueal-postgresql function bodysqueal-postgresql function bodysqueal-postgresqlfunction aliassqueal-postgresql argumentssqueal-postgresql return typesqueal-postgresqlfunction definitionsqueal-postgresqlfunction aliassqueal-postgresql argumentssqueal-postgresql return typesqueal-postgresqlfunction definitionsqueal-postgresqlfunction aliassqueal-postgresqlfunction alias  >comments(c) Eitan Chatav, 2020eitan@morphism.tech experimentalNone&',-.79;=>?@AHSUVX_ek +squeal-postgresql8When a user views a table in the database (i.e. with d+  table>), it is useful to be able to read a description of the table.squeal-postgresql6When a user views a type in the database (i.e with dT  type=), it is useful to be able to read a description of the type.squeal-postgresql7When a user views a view in the database (i.e. with dv  view=), it is useful to be able to read a description of the view.squeal-postgresql:When a user views an index in the database (i.e. with di+  index>), it is useful to be able to read a description of the index.squeal-postgresql<When a user views a function in the database (i.e. with df+  functionA), it is useful to be able to read a description of the function.squeal-postgresql8When a user views a table in the database (i.e. with d+  tableM), it is useful to be able to view descriptions of the columns in that table.squeal-postgresql:When a user views a schema in the database (i.e. with dn+  schema1), it is useful to be able to read a description.squeal-postgresqltablesqueal-postgresqlcommentsqueal-postgresqltypesqueal-postgresqlcommentsqueal-postgresqlviewsqueal-postgresqlcommentsqueal-postgresqlindexsqueal-postgresqlcommentsqueal-postgresqlfunctionsqueal-postgresqlcommentsqueal-postgresqltablesqueal-postgresqlcolumnsqueal-postgresqlcommentsqueal-postgresqlschemasqueal-postgresqlcomment export module(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone  ! "#$%46789:;<=>?@ABCDEFGMNOPQRSTUVWXYZ[\]^_`abcdefghij      !"#$%&'()*+,-./0123456789:;<=>STXWUV?@ABCDEFGHIJKLMNOPQRYZ[\]ijklmnopqrstuv !"#$%&'()*+,-`abcdefghijklmnopqrstuvwxyz{|}~ !"#$%&'()*+,-./0123456789:>?@ACBDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`auvwx{yz|}~    !"#$%&'()*+,-./0123456789:;<=>cdefghijklmnopqrstuvxyz{|}~   !"#$%&'()*+,-./0123456789:;<ABCDEFGHIJKLMNOPQRSTUVWXYZ[\ijlmknopqrst}~  #$%&0123457869:;=>?@ABCXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~$%4                                                             ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C D E F G H I J K LMNOPQRSTUVVWXYZ[\]^_`abbcdefghijklmnopqrstuvwxyz{|}~                            K M L                                                                       F E B A C D         l       Z      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyuz{|}~ooJcdbN      !"#$rs%&pq'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~      !"#$%&'()*+,-./01223456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwwxyz{|}~_n                     X X           O                                        !!!a!!!!!!!!!!!!!!!!!!"""""#~#########$$$$$$$$$$$$$$$ $ $ $ $ $$$$$$$$$$$$$$$$$$$ $!$"$#$$$%$&$'$($)$*$+%,%-%.&/&0&1&2&v&3&w&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({(|(})~))))))))))))))))))))))))))))))))))))))))))))))))))))****************************************+I+++j+t++k+++++++++++++,,,,,,,\,,,,,,,,,,,,---x-\-u-`----.y.......z....].^.|.... . . . . ......//////////////// /!/"/#/$/%/&/'/(/)/*/+/,/-/.///0/1/2/3/4/5/60m01718191:11;1<11=1>1?1@1A1l11B1C1D1E1F1G223H3I3J3K4L4M4N4O4P4Q4R4S4T4U4V4W4X4Y44Z4[4\4]4^4_4`4a4b4c4d4e4f4g4h4i4j4k4l4m4n4o4p4q4r4s4tuvwxyz{|}~5555555555555666666666666666666666666666   777777888888888899999W9999V99T99R99P99999U9Q9S99999999999999999::::;;;;;;;;;;;;;<<<<<<<< < < < < <<<<<<============= =!="=#>$>%>&>'>(>)>*+,-./0121345645789:;<=>?@45ABC>?D>?E1F<G<H<I<J<KLMNOPQRSwTU4VWXYZ)[\]^_`ab<c`ad<e<f<g<hij6kl9mnopqr0squeal-postgresql-0.7.0.1-33cXMmdyUeW5J6FQU4gmilSqueal.PostgreSQL.Type.AliasSqueal.PostgreSQL.DefinitionSqueal.PostgreSQL.Expression Squeal.PostgreSQL.Session.DecodeSqueal.PostgreSQL.Type.List#Squeal.PostgreSQL.Session.Migration$Squeal.PostgreSQL.Session.ConnectionSqueal.PostgreSQL.Session.Oid#Squeal.PostgreSQL.Session.ExceptionSqueal.PostgreSQL.Session.PoolSqueal.PostgreSQL.RenderSqueal.PostgreSQL.TypeSqueal.PostgreSQL.Type.SchemaSqueal.PostgreSQL.Type.PGSqueal.PostgreSQL.QuerySqueal.PostgreSQL.Query.WithSqueal.PostgreSQL.Query.From$Squeal.PostgreSQL.Expression.DefaultSqueal.PostgreSQL.Query.Values Squeal.PostgreSQL.Query.From.Set!Squeal.PostgreSQL.Expression.Type!Squeal.PostgreSQL.Expression.Time'Squeal.PostgreSQL.Expression.TextSearch!Squeal.PostgreSQL.Expression.Text!Squeal.PostgreSQL.Expression.Sort"Squeal.PostgreSQL.Expression.Range Squeal.PostgreSQL.Session.Encode Squeal.PostgreSQL.Session.Result&Squeal.PostgreSQL.Expression.Parameter!Squeal.PostgreSQL.Expression.Math"Squeal.PostgreSQL.Expression.LogicSqueal.PostgreSQL.Query.Table!Squeal.PostgreSQL.Query.From.Join%Squeal.PostgreSQL.Expression.Subquery!Squeal.PostgreSQL.Expression.Null!Squeal.PostgreSQL.Expression.Json&Squeal.PostgreSQL.Expression.Composite'Squeal.PostgreSQL.Expression.Comparison"Squeal.PostgreSQL.Expression.Array#Squeal.PostgreSQL.Expression.Inline&Squeal.PostgreSQL.Expression.Aggregate#Squeal.PostgreSQL.Expression.WindowSqueal.PostgreSQL.Query.SelectSqueal.PostgreSQL.Manipulation#Squeal.PostgreSQL.Session.StatementSqueal.PostgreSQL.Session.Monad%Squeal.PostgreSQL.Session.Transaction%Squeal.PostgreSQL.Manipulation.Update%Squeal.PostgreSQL.Manipulation.Insert%Squeal.PostgreSQL.Manipulation.Delete#Squeal.PostgreSQL.Manipulation.Call'Squeal.PostgreSQL.Definition.Constraint!Squeal.PostgreSQL.Session.IndexedSqueal.PostgreSQL.Session!Squeal.PostgreSQL.Definition.View!Squeal.PostgreSQL.Definition.Type"Squeal.PostgreSQL.Definition.Table#Squeal.PostgreSQL.Definition.Schema&Squeal.PostgreSQL.Definition.Procedure"Squeal.PostgreSQL.Definition.Index%Squeal.PostgreSQL.Definition.Function$Squeal.PostgreSQL.Definition.CommentDatabase.PostgreSQLLibPQ PGfixarray PGvararrayPGenum PGcompositePGjsonbPGjson Data.Listelem Selection FromClauseTableTypedefView ExpressiongroupByalterTableRename renameColumn alterTable alterColumn dropTable dropColumn createTable addColumnTableExpressionSqueal.PostgreSQL.BinaryRowPGSqueal.PostgreSQL.Manipulations ManipulationrunQueryParamsrunQuery parameterquery JoinLateralcommontableviewSqueal.Expression.ParameterPrelude->.idListselect insertIntoupdate ConditionCommonTableExpressiontsvectortsqueryjsonjsonbOverQuery.<.> StatementMonadPQmanipulateParams queryParamstraversePreparedSqueal.PostgreSQL.Nullnull_ deleteFromRepeatableRead Serializablewith Data.Function&Join Data.MaybefromJust fromMaybetrue WindowArgWindowFunction OnConflictDoUpdateparam manipulate manipulationSqueal.PostgreSQL.StatementvaluesControl.Monad.State.Class MonadState insertInto_update_ deleteFrom_-Squeal.PostgreSQL.Definition.Table.ConstraintcheckControl.CategoryCategorySqueal.PostgreSQLbaseGHC.OverloadedLabelsIsLabel fromLabel>>>+transformers-0.5.6.2-I9SzqoyagSNIbUNkJkeRZ3Control.Monad.Trans.ExceptExceptT.free-categories-0.2.0.2-EJlnT3zVxJH9wrS25PPRjIControl.Category.Free:>>DonePath Data.QuiverdownupIsoQ'sop-core-0.5.0.1-CJJL01S53WkJifrHVaTMB5Data.SOP.BasicFunctorsKunK Data.SOP.NPNPNil:*Control.Monad.Trans.State.Lazy runStateTStateT/postgresql-libpq-0.9.4.2-EUHLwl14mc86KODnPE5PPoDatabase.PostgreSQL.LibPQOid SingleTuple FatalError NonfatalError BadResponseCopyBothCopyInCopyOutTuplesOk CommandOk EmptyQuery ExecStatus"Database.PostgreSQL.LibPQ.Internal Connection,unliftio-pool-0.2.1.1-Gi9BeBhPXkD2qXV2qKnGHp UnliftIO.PoolPool RenderSQL renderSQL parenthesized bracketed<+>commaSeparated doubleQuotedsingleQuotedTextsingleQuotedUtf8escapeQuotedStringescapeQuotedTextrenderCommaSeparatedrenderCommaSeparatedConstraintrenderCommaSeparatedMaybe renderNat renderSymbolprintSQLescapeSquealException SQLExceptionConnectionExceptionDecodingExceptionColumnsException RowsExceptionSQLState sqlExecStatus sqlStateCodesqlErrorMessageSerializationFailureCheckViolationUniqueViolation catchSqueal handleSqueal trySqueal throwSqueal$fExceptionSquealException $fEqSQLState$fShowSQLState$fEqSquealException$fShowSquealExceptionFixCharVarCharOnlyfromOnlyFixArray getFixArrayVarArray getVarArray Enumerated getEnumerated Composite getCompositeJsonbgetJsonbJsongetJsonMoneycentsvarChar getVarCharfixChar getFixChar$fHasDatatypeInfoOnly $fGenericOnly $fEqMoney $fOrdMoney $fShowMoney $fReadMoney$fGenericMoney$fHasDatatypeInfoMoney$fGenericMoney0$fEqJson $fOrdJson $fShowJson $fReadJson $fGenericJson$fHasDatatypeInfoJson$fGenericJson0 $fEqJsonb $fOrdJsonb $fShowJsonb $fReadJsonb$fGenericJsonb$fHasDatatypeInfoJsonb$fGenericJsonb0 $fEqComposite$fOrdComposite$fShowComposite$fReadComposite$fGenericComposite$fHasDatatypeInfoComposite$fGenericComposite0$fEqEnumerated$fOrdEnumerated$fShowEnumerated$fReadEnumerated$fGenericEnumerated$fHasDatatypeInfoEnumerated$fGenericEnumerated0 $fEqVarArray $fOrdVarArray$fShowVarArray$fReadVarArray$fGenericVarArray$fHasDatatypeInfoVarArray$fGenericVarArray0 $fEqFixArray $fOrdFixArray$fShowFixArray$fReadFixArray$fGenericFixArray$fHasDatatypeInfoFixArray$fGenericFixArray0 $fFunctorOnly$fFoldableOnly$fTraversableOnly$fEqOnly $fOrdOnly $fReadOnly $fShowOnly$fGenericOnly0 $fEqVarChar $fOrdVarChar $fReadVarChar $fShowVarChar $fEqFixChar $fOrdFixChar $fReadFixChar $fShowFixCharLengthInElem Additionalalsodisjoin*:one$fAdditionalaNPQualifiedAlias IsQualified!HasAllHasInHasErrHas HasUnique AliasableasAliasedAsAlias GroupedByGrouping UngroupedGrouped::: renderAliased mapAliased$fGroupedBy[]tablecolumn:$fGroupedBy[]tablecolumn:0 $fRenderSQLNP$fRenderSQLAlias$fIsLabelaliasNP$fIsLabelalias1Alias$fIsLabelalias0Aliased$fAliasablealiasexpressionNP!$fAliasablealiasexpressionAliased$fHasErrkkinderralias:field$fHasErrkkinderralias:field1$fHasIn[](,)fields(,)$fHasAllk:fields:$fHasAllkind[]fields[]$fIsQualifiedqualifieralias(,)$fRenderSQLQualifiedAlias$fIsLabela0Aliased$fIsQualifiedq0a0Aliased$fIsLabelaQualifiedAlias$fIsQualifiedqaQualifiedAlias $fEqAlias$fGenericAlias $fOrdAlias $fShowAlias $fNFDataAlias$fEqQualifiedAlias$fGenericQualifiedAlias$fOrdQualifiedAlias$fShowQualifiedAlias$fNFDataQualifiedAlias $fOrdAliased $fEqAliased $fShowAliasedUserTypeUserTypeNamespace UserTypeName Updatable AllUnique IsNotElem PGJsonType PGJsonKeyPGlabel IsPGlabellabelPublic SchemasType SchemaType ReturnsTypeReturns ReturnsTable IndexTypeBtreeHashGistSpgistGinBrin FunctionType SchemumTypeIndexFunction Procedure UnsafeSchemumDropIfConstraintsInvolveConstraintInvolves SetSchemaRenameIfExistsRename AlterIfExistsAlterDropSchemumIfExists DropIfExists DropSchemumDropCreateOrReplaceCreateIfNotExistsCreate NullifyFrom NullifyRow NullifyType NotAllNull AllNotNull SamePGType PGIntegral PGFloatingPGNum TableToRowTableToColumns ColumnsToRowFromTypeRowType TableTypeUniquelyTableConstraintsTableConstraintCheckUnique PrimaryKey ForeignKey ColumnsType ColumnType OptionalityDefNoDef:=>NullTypeNullNotNullPGTypePGboolPGint2PGint4PGint8 PGnumericPGfloat4PGfloat8PGmoneyPGchar PGvarcharPGtextPGbytea PGtimestamp PGtimestamptzPGdatePGtimePGtimetz PGintervalPGuuidPGinet PGtsvector PGtsqueryPGoidPGrange UnsafePGType$fSamePGType(,)(,)$fIsPGlabellabel->$fIsPGlabellabel->0$fRenderSQLPGlabel$fIsPGlabellabelNP$fIsPGlabellabelPGlabel$fIsNotElem(,)Bool(,)True$fIsNotElemkBoolxFalse $fAllUniquea:$fAllUniquea[]FixPGDimPGConstructorNamesOfConstructorNameOfConstructorsOf TupleCodeOfTupleOfTuplePGNullPGRowOfLabelsPGIsPGPG $fIsPGJsonb $fIsPGJson $fIsPGMoney $fIsPGFixChar $fIsPGVarChar $fIsPGValue $fIsPGNetAddr $fIsPGUUID$fIsPGDiffTime $fIsPG(,)$fIsPGTimeOfDay $fIsPGDay $fIsPGUTCTime$fIsPGLocalTime$fIsPGByteString$fIsPGByteString0$fIsPG[] $fIsPGText $fIsPGText0 $fIsPGChar $fIsPGDouble $fIsPGFloat$fIsPGScientific $fIsPGOid $fIsPGInt64 $fIsPGInt32 $fIsPGInt16 $fIsPGBool$fIsPGVarArray$fIsPGVarArray0$fIsPGComposite$fIsPGEnumerated$fIsPGFixArray OidOfField oidOfField OidOfNull oidOfNull OidOfArray oidOfArrayOidOfoidOf$fOidOfdbPGenum$fOidOfdbPGcomposite$fOidOfdbPGrange$fOidOfdbPGrange0$fOidOfdbPGrange1$fOidOfdbPGrange2$fOidOfdbPGrange3$fOidOfdbPGrange4$fOidOfdbPGoid$fOidOfdbPGtsquery$fOidOfdbPGtsvector$fOidOfdbPGjsonb$fOidOfdbPGjson$fOidOfdbPGinet$fOidOfdbPGuuid$fOidOfdbPGinterval$fOidOfdbPGtimetz$fOidOfdbPGtime$fOidOfdbPGdate$fOidOfdbPGtimestamptz$fOidOfdbPGtimestamp$fOidOfdbPGbytea$fOidOfdbPGtext$fOidOfdbPGvarchar$fOidOfdbPGchar$fOidOfdbPGmoney$fOidOfdbPGfloat8$fOidOfdbPGfloat4$fOidOfdbPGnumeric$fOidOfdbPGint8$fOidOfdbPGint4$fOidOfdbPGint2$fOidOfdbPGbool$fOidOfArraydbPGenum$fOidOfArraydbPGcomposite$fOidOfArraydbPGrange$fOidOfArraydbPGrange0$fOidOfArraydbPGrange1$fOidOfArraydbPGrange2$fOidOfArraydbPGrange3$fOidOfArraydbPGrange4$fOidOfArraydbPGoid$fOidOfArraydbPGtsquery$fOidOfArraydbPGtsvector$fOidOfArraydbPGjsonb$fOidOfArraydbPGjson$fOidOfArraydbPGinet$fOidOfArraydbPGuuid$fOidOfArraydbPGinterval$fOidOfArraydbPGtimetz$fOidOfArraydbPGtime$fOidOfArraydbPGdate$fOidOfArraydbPGtimestamptz$fOidOfArraydbPGtimestamp$fOidOfArraydbPGbytea$fOidOfArraydbPGtext$fOidOfArraydbPGvarchar$fOidOfArraydbPGchar$fOidOfArraydbPGmoney$fOidOfArraydbPGfloat8$fOidOfArraydbPGfloat4$fOidOfArraydbPGnumeric$fOidOfArraydbPGint8$fOidOfArraydbPGint4$fOidOfArraydbPGint2$fOidOfArraydbPGbool$fOidOfdbPGfixarray$fOidOfdbPGvararray$fOidOfNulldbnull$fOidOfFielddb(,) connectdbfinishlowerConnectionQuery_ UnsafeQuery renderQueryunionunionAll intersect intersectAllexcept exceptAll$fRenderSQLQuery$fGenericQuery $fShowQuery $fEqQuery $fOrdQuery $fNFDataQueryWith withRecursive $fRenderSQLCommonTableExpression$fAliasablectestatementPath,$fAliasablectestatementCommonTableExpression $fWithQueryUnsafeFromClauserenderFromClausesubquery$fAdditional(,)FromClause$fRenderSQLFromClause$fGenericFromClause$fShowFromClause$fEqFromClause$fOrdFromClause$fNFDataFromClauseOptionalDefaultSet NotDefault mapOptional$fRenderSQLOptional PGIntersect@&&PGSubset@><@ FunctionVarFunN--->Fun--> OperatorDBOperatorExprUnsafeExpressionrenderExpressionunsafeFunctionVarunsafeBinaryOp unsafeLeftOp unsafeRightOpunsafeFunctionfunctionunsafeFunctionN functionN$fMonoidExpression$fMonoidExpression0$fSemigroupExpression$fSemigroupExpression0$fSemigroupExpression1$fSemigroupExpression2$fIsStringExpression$fIsStringExpression0$fIsStringExpression1$fFloatingExpression$fFloatingExpression0$fFloatingExpression1$fFractionalExpression$fFractionalExpression0$fFractionalExpression1$fNumExpression$fNumExpression0$fNumExpression1$fNumExpression2$fNumExpression3$fNumExpression4$fIsPGlabellabelExpression$fIsQualifiedtabcolNP$fIsQualifiedtabcolAliased$fIsQualifiedtabcolNP0$fIsQualifiedtabcolExpression$fIsLabelcolNP$fIsLabelcolAliased$fIsLabelcolNP0$fIsLabelcolExpression$fIsQualifiedtabcolNP1$fIsQualifiedtabcolAliased0$fIsQualifiedtabcolNP2$fIsQualifiedtabcolExpression0$fIsLabelcolNP1$fIsLabelcolAliased0$fIsLabelcolNP2$fIsLabelcolExpression0$fRenderSQLExpression$fPGSubsetPGTypePGrange$fPGSubsetPGTypePGvararray$fPGSubsetPGTypePGtsquery$fPGSubsetPGTypePGjsonb$fPGIntersectPGTypePGrange$fPGIntersectPGTypePGvararray$fGenericExpression$fShowExpression$fEqExpression$fOrdExpression$fNFDataExpressionvalues_SetFunNSetFun--|->-|->unsafeSetFunction setFunctionunsafeSetFunctionN setFunctionNgenerateSeriesgenerateSeriesStepgenerateSeriesTimestamp ColumnTyped columntype NullTypednulltypeColumnTypeExpressionUnsafeColumnTypeExpressionrenderColumnTypeExpression FieldTyped fieldtypePGTypedpgtypeTypeExpressionUnsafeTypeExpressionrenderTypeExpressioncastastype inferredtypetypedef typetabletypeviewboolint2smallintint4intintegerint8bigintnumericfloat4realfloat8doublePrecisionmoneytextchar charactervarcharcharacterVaryingbytea timestamptimestampWithTimeZone timestamptzdatetimetimeWithTimeZonetimetzintervaluuidinetvararrayfixarrayoid int4range int8rangenumrangetsrange tstzrange daterangerecord pgtypeFromnullable notNullabledefault_serial2 smallserialserial4serialserial8 bigserial nulltypeFromcolumntypeFrom$fRenderSQLTypeExpression$fPGTypeddbPGenum$fPGTypeddbPGcomposite$fPGTypeddbPGrange$fPGTypeddbPGrange0$fPGTypeddbPGrange1$fPGTypeddbPGrange2$fPGTypeddbPGrange3$fPGTypeddbPGrange4$fPGTypeddbPGoid$fPGTypeddbPGtsquery$fPGTypeddbPGtsvector$fPGTypeddbPGfixarray$fPGTypeddbPGvararray$fPGTypeddbPGjsonb$fPGTypeddbPGjson$fPGTypeddbPGinet$fPGTypeddbPGuuid$fPGTypeddbPGinterval$fPGTypeddbPGtimetz$fPGTypeddbPGtime$fPGTypeddbPGdate$fPGTypeddbPGtimestamptz$fPGTypeddbPGtimestamp$fPGTypeddbPGbytea$fPGTypeddbPGvarchar$fPGTypeddbPGchar$fPGTypeddbPGtext$fPGTypeddbPGmoney$fPGTypeddbPGfloat8$fPGTypeddbPGfloat4$fPGTypeddbPGnumeric$fPGTypeddbPGint8$fPGTypeddbPGint4$fPGTypeddbPGint2$fPGTypeddbPGbool$fRenderSQLColumnTypeExpression$fNullTypeddbnull$fFieldTypeddb(,)$fColumnTypeddb(,)$fColumnTypeddb(,)0$fGenericTypeExpression$fShowTypeExpression$fEqTypeExpression$fOrdTypeExpression$fNFDataTypeExpression$fGenericColumnTypeExpression$fShowColumnTypeExpression$fEqColumnTypeExpression$fOrdColumnTypeExpression$fNFDataColumnTypeExpressionTimeUnitYearsMonthsWeeksDaysHoursMinutesSeconds Microseconds MillisecondsDecades Centuries MillenniaTimeOp!++!!-!-! currentDate currentTimecurrentTimestamp localTimelocalTimestampnowmakeDatemakeTime makeTimestampmakeTimestamptz interval_$fTimeOpPGTypePGdatePGint4"$fTimeOpPGTypePGintervalPGinterval $fTimeOpPGTypePGtimetzPGinterval$fTimeOpPGTypePGtimePGinterval%$fTimeOpPGTypePGtimestamptzPGinterval#$fTimeOpPGTypePGtimestampPGinterval$fRenderSQLTimeUnit$fHasDatatypeInfoTimeUnit$fGenericTimeUnit $fEqTimeUnit $fOrdTimeUnit$fShowTimeUnit$fReadTimeUnit$fEnumTimeUnit$fGenericTimeUnit0@@.&.|.!<->arrayToTSvectortsvectorLengthnumnodeplainToTSqueryphraseToTSquerywebsearchToTSquery queryTree toTSquery toTSvector setWeightstripjsonToTSvectorjsonbToTSvectortsDeletetsFilter tsHeadlinelowerupper charLengthlikeilikeOrderByorderBySortExpressionAscDesc AscNullsFirst AscNullsLastDescNullsFirst DescNullsLast $fRenderSQL[]$fRenderSQLSortExpression$fShowSortExpressionRangeEmptyNonEmptyBoundInfiniteClosedOpenrange<=..<=<..<<=..<<..<=moreThanatLeastlessThanatMost singletonwhole.<@@>.<<@@>>&<&>-|-@+@*@- lowerBound upperBoundisEmptylowerInclowerInfupperIncupperInf rangeMerge $fIsPGRange $fEqBound $fOrdBound $fShowBound $fReadBound$fGenericBound$fFunctorBound$fFoldableBound$fTraversableBound $fEqRange $fOrdRange $fShowRange $fReadRange$fGenericRange$fFunctorRange$fFoldableRange$fTraversableRange$fGenericRange0$fHasDatatypeInfoRange EncodeParamsrunEncodeParamsToArray arrayPayload arrayDims arrayNullsToFieldtoFieldToParamtoParamToPGtoPG genericParams nilParams.**.aParam appendParams $fToPGdbRange$fToPGdbEnumerated $fToPGdbJsonb $fToPGdbJson $fToPGdbValue$fToPGdbDiffTime$fToPGdbUTCTime$fToPGdbLocalTime $fToPGdb(,)$fToPGdbTimeOfDay $fToPGdbDay$fToPGdbFixChar$fToPGdbVarChar$fToPGdbByteString$fToPGdbByteString0 $fToPGdb[] $fToPGdbText $fToPGdbText0 $fToPGdbChar$fToPGdbNetAddr $fToPGdbUUID $fToPGdbMoney$fToPGdbScientific$fToPGdbDouble $fToPGdbFloat $fToPGdbOid $fToPGdbInt64 $fToPGdbInt32 $fToPGdbInt16 $fToPGdbBool$fToParamdbNullMaybe$fToParamdbNotNullx$fToFielddb(,)(,)$fToPGdbComposite$fToArraydb:tytuple$fToArraydb[]NullMaybe$fToArraydb[]NotNullx$fToPGdbFixArray$fToPGdbVarArray$fToPGdbVarArray0$fContravariantEncodeParams DecodeRow unDecodeRow FromArray fromArray FromField fromField FromValue fromValueFromPGfromPGdevaluerowValue runDecodeRow appendRowsconsRow decodeRow genericRow enumValue $fFromPGRange$fFromPGEnumerated $fFromPGJsonb $fFromPGJson $fFromPGValue$fFromPGDiffTime$fFromPGUTCTime$fFromPGLocalTime $fFromPG(,)$fFromPGTimeOfDay $fFromPGDay$fFromPGFixChar$fFromPGVarChar$fFromPGByteString$fFromPGByteString0 $fFromPG[] $fFromPGText $fFromPGText0 $fFromPGChar$fFromPGNetAddr $fFromPGUUID $fFromPGMoney$fFromPGScientific$fFromPGDouble $fFromPGFloat $fFromPGOid $fFromPGInt64 $fFromPGInt32 $fFromPGInt16 $fFromPGBool$fFromValueNullMaybe$fFromValueNotNully$fFromField(,)(,)$fFromPGComposite$fFromArray:typroduct$fFromArray[]NullMaybe$fFromArray[]NotNully$fFromPGFixArray$fFromPGVarArray$fFromPGVarArray0$fIsLabelfldMaybeT$fIsLabelfldMaybeT0$fIsLabelfldDecodeRow$fIsLabelfldDecodeRow0$fMonadFailDecodeRow$fFunctorDecodeRow$fApplicativeDecodeRow$fAlternativeDecodeRow$fMonadDecodeRow$fMonadPlusDecodeRow$fMonadErrorDecodeRowResultgetRownextRowgetRowsfirstRow liftResultntuplesnfields resultStatus cmdStatus cmdTuplesokResultresultErrorMessageresultErrorCode$fFunctorResult HasParameter$fHasParameternparamsx$fHasParameter1paramsxatan2_quot_rem_truncround_ceiling_falsenot_.&&.||caseWhenThenElse ifThenElseWaitingWaitNoWait SkipLocked LockStrengthUpdate NoKeyUpdateShareKeyShare LockingClauseFor HavingClauseNoHavingHaving GroupByClauseUnsafeGroupByClauserenderGroupByClauseByBy1By2 fromClause whereClause groupByClause havingClause orderByClause limitClause offsetClauselockingClausesfromwhere_havinglimitoffsetlockRows$fIsQualifiedrelcolNP$fIsQualifiedrelcolBy$fIsLabelcolBy $fRenderSQLBy$fRenderSQLGroupByClause$fRenderSQLHavingClause$fRenderSQLLockStrength$fRenderSQLWaiting$fRenderSQLLockingClause$fOrderByTableExpressiongrp$fRenderSQLTableExpression$fGenericGroupByClause$fShowGroupByClause$fEqGroupByClause$fOrdGroupByClause$fNFDataGroupByClause$fEqLockStrength$fOrdLockStrength$fShowLockStrength$fReadLockStrength$fEnumLockStrength$fGenericLockStrength $fEqWaiting $fOrdWaiting $fShowWaiting $fReadWaiting $fEnumWaiting$fGenericWaiting$fGenericTableExpression$fOrdHavingClause$fEqHavingClause$fShowHavingClause$fOrdBy$fEqBy$fShowByJoinItem JoinFunction JoinFunctionNcross crossJoincrossJoinLateralinner innerJoininnerJoinLateral leftOuter leftOuterJoinleftOuterJoinLateral rightOuterrightOuterJoinrightOuterJoinLateral fullOuter fullOuterJoinfullOuterJoinLateral$fRenderSQLJoinItemexistssubAllsubAnyin_notInnotNull unsafeNotNull monoNotNullcoalescefromNullisNull isNotNull matchNullnullIfJsonToRecordFunctionJsonPopulateFunctionJsonBuildObjectjsonBuildObjectjsonbBuildObject.->.->>.#>.#>>.?.?|.?&.-.#-.toJsontoJsonb arrayToJson rowToJsonjsonBuildArrayjsonbBuildArray jsonObject jsonbObject jsonZipObjectjsonbZipObjectjsonArrayLengthjsonbArrayLength jsonTypeof jsonbTypeofjsonStripNullsjsonbStripNullsjsonbSet jsonbInsert jsonbPrettyjsonEach jsonbEach jsonEachText jsonbEachTextjsonObjectKeysjsonbObjectKeysjsonPopulateRecordjsonbPopulateRecordjsonPopulateRecordSetjsonbPopulateRecordSet jsonToRecord jsonbToRecordjsonToRecordSetjsonbToRecordSet$fJsonBuildObject:$fJsonBuildObject[]rowrowStarfield BetweenExpr.==./=.>=.<=greatestleastbetween notBetweenbetweenSymmetricnotBetweenSymmetricisDistinctFromisNotDistinctFromisTrue isNotTrueisFalse isNotFalse isUnknown isNotUnknownarrayarray0array1array2 cardinalityindexindex1index2unnestarrAllarrAny InlineColumn inlineColumn InlineField inlineField InlineParam inlineParamInlineinline inlineFields inlineColumns$fInlineEnumerated $fInlineOid $fInlineMoney $fInlineUUID $fInlineRange$fInlineRange0$fInlineRange1$fInlineRange2$fInlineRange3$fInlineRange4$fInlineLocalTime$fInlineTimeOfDay $fInline(,)$fInlineUTCTime $fInlineDay$fInlineDiffTime$fInlineFixChar$fInlineVarChar $fInlineText $fInlineText0$fInlineScientific$fInlineDouble $fInlineFloat $fInlineInt64 $fInlineInt32 $fInlineInt16 $fInline[] $fInlineChar $fInlineJsonb $fInlineJson $fInlineBool$fInlineParamMaybeNull$fInlineParamxNotNull$fInlineVarArray$fInlineVarArray0$fInlineField(,)(,)$fInlineComposite$fInlineColumn(,)(,)$fInlineColumn(,)(,)0PGAvgPGSum FilterWhere filterWhere AggregateArg AggregateAllAggregateDistinct aggregateArgsaggregateOrderaggregateFilter Aggregate countStarcountsum_arrayAggjsonAggjsonbAggbitAndbitOrboolAndboolOreverymax_min_avgcorrcovarPop covarSampregrAvgXregrAvgY regrCount regrInterceptregrR2 regrSloperegrSxxregrSxyregrSyystddev stddevPop stddevSampvariancevarPopvarSamp DistinctsDistinctAllsAll allNotNulldistinctNotNull$fOrderByAggregateArgUngrouped$fRenderSQLAggregateArg$$fFilterWhere[]AggregateArgUngrouped+$fAggregate[][][][][]AggregateArgExpression--#->-#->WinFun0 windowArgs windowFilterUnsafeWindowFunctionrenderWindowFunctionWindowDefinitionWindowsWindow partitionByunsafeWindowFunction1unsafeWindowFunctionNrank rowNumber denseRank percentRankcumeDistntilelaglead firstValue lastValuenthValue$fRenderSQLWindowDefinition$fOrderByWindowDefinitiongrp$fRenderSQLWindowFunction$fFilterWhere[]WindowArggrp$fRenderSQLWindowArg,$fAggregate[][][][][]WindowArgWindowFunction$fGenericWindowFunction$fShowWindowFunction$fEqWindowFunction$fOrdWindowFunction$fNFDataWindowFunction$fGenericWindowArgStarDotStarAlsoselect_selectDistinctselectDistinct_selectDistinctOnselectDistinctOn_$fIsStringSelection$fRenderSQLSelection$fIsLabelcolSelection$fIsLabelcolSelection0$fIsQualifiedtabcolSelection$fIsQualifiedtabcolSelection0!$fAliasablecolExpressionSelection$fAdditional(,)Selection UsingClauseNoUsingUsingReturningClause Returning Manipulation_UnsafeManipulationrenderManipulation Returning_queryStatement$fWithManipulation$fRenderSQLManipulation$fRenderSQLReturningClause$fGenericManipulation$fShowManipulation$fEqManipulation$fOrdManipulation$fNFDataManipulation GenericRow GenericParams$fRenderSQLStatement$fFunctorStatement$fProfunctorStatement executeParamsexecuteParams_executeexecute_executePreparedexecutePrepared_manipulateParams_ manipulate_ forPreparedtraversePrepared_ forPrepared_$fMonadPQdbContT$fMonadPQdbRWST$fMonadPQdbRWST0$fMonadPQdbExceptT$fMonadPQdbMaybeT$fMonadPQdbWriterT$fMonadPQdbWriterT0$fMonadPQdbStateT$fMonadPQdbStateT0$fMonadPQdbReaderT$fMonadPQdbIdentityTDeferrableMode Deferrable NotDeferrable AccessMode ReadWriteReadOnlyIsolationLevel ReadCommittedReadUncommittedTransactionModeisolationLevel accessModedeferrableModetransactionallytransactionally_transactionallyRetry ephemerally ephemerally_begincommitrollback defaultModelongRunningMode$fRenderSQLIsolationLevel$fRenderSQLAccessMode$fRenderSQLDeferrableMode$fRenderSQLTransactionMode$fShowIsolationLevel$fEqIsolationLevel$fShowAccessMode$fEqAccessMode$fShowDeferrableMode$fEqDeferrableMode$fShowTransactionMode$fEqTransactionModeConflictTarget OnConstraintConflictAction DoNothingConflictClauseOnConflictDoRaise QueryClauseValuesSelectSubqueryValues_ inlineValues_ inlineValues$fRenderSQLQueryClause$fRenderSQLConflictAction$fRenderSQLConflictTarget$fRenderSQLConflictClause unsafeCallcall unsafeCallNcallNReferentialActionNoActionRestrictCascadeSetNull SetDefaultOnUpdateClauseOnUpdateOnDeleteClauseOnDelete ForeignKeyedTableConstraintExpressionUnsafeTableConstraintExpressionrenderTableConstraintExpressionunique primaryKey foreignKey$$fRenderSQLTableConstraintExpression$fRenderSQLReferentialAction$fNFDataReferentialAction$fRenderSQLOnUpdateClause$fNFDataOnUpdateClause$fRenderSQLOnDeleteClause$fNFDataOnDeleteClause"$fGenericTableConstraintExpression$fShowTableConstraintExpression$fEqTableConstraintExpression$fOrdTableConstraintExpression!$fNFDataTableConstraintExpression$fGenericReferentialAction$fShowReferentialAction$fEqReferentialAction$fOrdReferentialAction$fGenericOnUpdateClause$fShowOnUpdateClause$fEqOnUpdateClause$fOrdOnUpdateClause$fGenericOnDeleteClause$fShowOnDeleteClause$fEqOnDeleteClause$fOrdOnDeleteClause DefinitionUnsafeDefinitionrenderDefinition manipulation_$fMonoidDefinition$fSemigroupDefinition$fCategory[]Definition$fRenderSQLDefinition$fGenericDefinition$fShowDefinition$fEqDefinition$fOrdDefinition$fNFDataDefinitionIndexedMonadTransPQdefineIndexed runIndexedIndexedMonadTranspqAppqJoinpqBindpqThen pqAndThen indexedDefine$fCategorykIndexedPQunPQrunPQexecPQevalPQwithConnection $fMonoidPQ $fSemigroupPQ $fMonadMaskPQ$fMonadCatchPQ$fMonadThrowPQ$fMonadTransControlPQ$fMonadBasebPQ$fMonadUnliftIOPQ $fMonadIOPQ $fMMonadPQ$fMonadTransPQ$fMFunctorTYPEPQ $fMonadFailPQ $fMonadPQ$fApplicativePQ $fMonadPQdbPQ$fIndexedMonadTransPQPQ$fIndexedMonadTrans[]PQ $fFunctorPQ$fMonadBaseControlbPQcreateConnectionPoolusingConnectionPooldestroyConnectionPool createViewcreateOrReplaceViewdropViewdropViewIfExistsalterViewRenamealterViewSetSchemacreateTypeEnumcreateTypeEnumFromcreateTypeCompositecreateTypeCompositeFrom createDomaincreateTypeRangedropTypedropTypeIfExistsalterTypeRenamealterTypeSetSchema AlterColumnUnsafeAlterColumnrenderAlterColumn AddColumn AlterTableUnsafeAlterTablerenderAlterTablecreateTableIfNotExistsdropTableIfExistsalterTableIfExistsalterTableIfExistsRenamealterTableSetSchema addConstraintdropConstraint setDefault dropDefault setNotNull dropNotNull alterType$fAddColumn(,)$fAddColumn(,)0$fGenericAlterTable$fShowAlterTable$fEqAlterTable$fOrdAlterTable$fNFDataAlterTable$fGenericAlterColumn$fShowAlterColumn$fEqAlterColumn$fOrdAlterColumn$fNFDataAlterColumnMigrationsTable Migratory runMigrations Migration migrationName migrationDefmigrate migrateUp migrateDown mainMigratemainMigrateIso$fQFunctork2k3k2k3Migration$fMigratory[]IsoQIsoQ$fMigratory[]IsoQIsoQ0$fMigratory[]OpQOpQ$fMigratory[]OpQOpQ0$fMigratory[]DefinitionIndexed$fMigratory[]IndexedIndexed$fHasDatatypeInfoMigrationRow$fGenericMigrationRow$fGenericMigration$fGenericMigrationRow0$fShowMigrationRow createSchemacreateSchemaIfNotExistsdropSchemaCascadedropSchemaCascadeIfExistsProcedureDefinitionUnsafeProcedureDefinitionrenderProcedureDefinitioncreateProcedurecreateOrReplaceProcedurelanguageSqlManipulation dropProceduredropProcedureIfExists$fRenderSQLProcedureDefinition$fEqProcedureDefinition$fShowProcedureDefinition$fGenericProcedureDefinition$fNFDataProcedureDefinition IndexMethodUnsafeIndexMethodrenderIndexMethod createIndexcreateIndexIfNotExistsbtreehashgistspgistginbrin dropIndexdropIndexIfExists$fRenderSQLIndexMethod$fEqIndexMethod$fOrdIndexMethod$fShowIndexMethod$fGenericIndexMethodFunctionDefinitionUnsafeFunctionDefinitionrenderFunctionDefinitioncreateFunctioncreateOrReplaceFunctionlanguageSqlExprlanguageSqlQuerycreateSetFunctioncreateOrReplaceSetFunction dropFunctiondropFunctionIfExists$fRenderSQLFunctionDefinition$fEqFunctionDefinition$fShowFunctionDefinition$fGenericFunctionDefinition$fNFDataFunctionDefinitioncommentOnTable commentOnType commentOnViewcommentOnIndexcommentOnFunctioncommentOnColumncommentOnSchema+bytestring-0.10.12.0-Ev7YoUNkQ8A2myMWtv584QData.ByteString.Internal ByteString#text-1.2.4.0-DmyNLU6tKf8BwnnkjpvnYaData.Text.InternalText GHC.MaybeMaybeNothingghc-prim GHC.TypesNatSymbolGHC.Exception.Type Exception Data.EitherEitherGHC.Base++*generics-sop-0.5.1.0-rTQnEHI8wZGMd5yernVkkGenerics.SOP.UniverseCodeType GHC.TypeLits TypeErrorGenericHasDatatypeInfoJustFunctor Applicative AlternativeMonad MonadPlus mtl-2.2.2-DPcvGa47JlhLWatXOCoVpVControl.Monad.Error.Class MonadErrorControl.Monad.Trans.MaybeMaybeT1postgresql-binary-0.12.3.1-AT2vpYaDtefKMgTsPmV9QgPostgreSQL.Binary.DecodingValue GHC.Exceptionthrow GHC.ClassesminGHC.Num+maybeunsafeAggregateData.Traversable Traversable Data.FoldableFoldable%unliftio-0.2.13-pEVprtDMVqIymIyuP6VyFUnliftIO.Exception onExceptionreturntry<*>join=<<>> Control.Monad<=<PQRundestroyAllResourcesrenderCreationcreateMigrationsinsertMigrationdeleteMigrationselectMigration