h&E             ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~                                                                                                                                                                                                                  ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! " " " " " # # # # # # # # # # # # $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ % % % & & & & & & & & & & & & & & & & & & & & & ' ' ' ' ' ' ' ' ' ' ' ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * + + + + + + + + + + + + + + + + + + + + , , , , , , , , , , , , , , , , , , , - - - - - - - - - - - - - - - - - - - - - - - - - - - - . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / 0 0 0 0 0 0 0 0 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 4 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5              6 6 6 6 6 6 6 6 6 6 6 6 6 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 8 8 8 8 8 8 9999999999:::::::::::::::::::::::::::::::::::::::;;;;<<<<<<<<<<<<<==================>>>>>>>>>>>>>>>>??????? render functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred "/0)$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 experimental Safe-Inferred".g6squeal-postgresqls that can be thrown by Squeal.7squeal-postgresqlSQL exception state8squeal-postgresql@A function connection exception9squeal-postgresql-decoding exception function and error message:squeal-postgresqlunexpected number of columns;squeal-postgresql9too few rows, expected at least and actual number of rows<squeal-postgresqlthe state of LibPQ?squeal-postgresql https://www.postgresql.org/docs/current/static/errcodes-appendix.htmlAsqueal-postgresql,A pattern for deadlock detection exceptions.Bsqueal-postgresql/A pattern for serialization failure exceptions.Csqueal-postgresql4A pattern for check constraint violation exceptions.Dsqueal-postgresql*A pattern for unique violation exceptions.Esqueal-postgresqlCatch 6s.Fsqueal-postgresqlHandle 6s.Gsqueal-postgresql return a 6 or a result.Hsqueal-postgresqlThrow 6s.Esqueal-postgresqlhandlerFsqueal-postgresqlhandler"! 6789:;<=>?@ABCDEFGH6789:;DCBA<=>?@"! EFGH types(c) Eitan Chatav, 2010eitan@morphism.tech experimental Safe-Inferred!")*0189:;<=?8Nsqueal-postgresqlFixed-length, blank padded:kind! PG (FixChar 4)PG (FixChar 4) :: PGType = 'PGchar 4Osqueal-postgresql$Variable-length text type with limit:kind! PG (VarChar 4)PG (VarChar 4) :: PGType= 'PGvarchar 4Psqueal-postgresqlP? is a 1-tuple type, useful for encoding or decoding a singletonSsqueal-postgresqlThe S newtype is an indication that the Haskell type it's applied to should be stored as a  B.9:kind! PG (FixArray ((Double, Double), (Double, Double))) 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-postgresqlDefaultPrettyPrinter provides a default we can use for kinds that don't provide an instance of PrettyPrintInfo4, although that should generally only be accidentalsqueal-postgresql allows us to use the kind of our haystack to come up with nicer errors. It is implemented as an open type family for dependency reasonssqueal-postgresqlPrettyPrintInfo is a data type intended to be used at the type level which describes how to pretty print a haystack in our custom errors. The general intention is we use PrettyPrintHaystack to define a more specific way of pretty printing our error information for each kind that we care aboutsqueal-postgresqlLookupFailedError' is the workhorse behind LookupFailedError, but taking an additional type as the first argument. We can put another type error in there which will only show if LookupFailedError'* is stuck; this allows us to fall back to DefaultPrettyPrinter when a PrettyPrintHaystack instance is missingsqueal-postgresqlLookupFailedError4 reports a nicer error when we fail to look up some needle in some haystacksqueal-postgresqlMismatchError' is the workhorse behind  MismatchError, but taking an additional type as the first argument. We can put another type error in there which will only show if MismatchError'* is stuck; this allows us to fall back to DefaultPrettyPrinter when a PrettyPrintHaystack instance is missingsqueal-postgresql MismatchError reports a nicer error with more context when we successfully do a lookup but find a different field than we expected. As a type family, it ensures that we only do the (expensive) calculation of coming up with our pretty printing information when we actually have a mismatchsqueal-postgresql is like  except it also retains the original list of fields being searched, so that error messages are more useful.squeal-postgresqlHas alias fields field# is a constraint that proves that fields has a field of alias ::: field , inferring field from alias and fields.squeal-postgresqlHasUnique alias fields field# is a constraint that proves that fields is a singleton of alias ::: field.squeal-postgresqlThe $ class provides a way to scrap your  s in an  list of  expressions.squeal-postgresqlThe ) operator is used to name an expression.  is like a demoted version of .8Just "hello" `As` #hi :: Aliased Maybe ("hi" ::: String)As (Just "hello") Aliassqueal-postgresql*es are proxies for a type level string or  and have an  instance so that with -XOverloadedLabels:set -XOverloadedLabels#foobar :: Alias "foobar"Aliassqueal-postgresqlA  constraint indicates that a table qualified column is a member of the auxiliary namespace created by GROUP BY. clauses and thus, may be called in an output O without aggregating.squeal-postgresql( is an auxiliary namespace, created by GROUP BY clauses (P%), and used for typesafe aggregationsqueal-postgresqlno aggregation permittedsqueal-postgresql8aggregation required for any column which is not groupedsqueal-postgresqlThe alias operator  is like a promoted version of 4, a type level pair between an alias and some type.squeal-postgresql=let renderMaybe = fromString . maybe "Nothing" (const "Just")6renderAliased renderMaybe (Just (3::Int) `As` #an_int)"Just AS \"an_int\""squeal-postgresqlMap a function over an  expression.squeal-postgresql$printSQL (#jimbob :: Alias "jimbob")"jimbob"##9 6 Postgres type system(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred !")*/015;=squeal-postgresqlFind fully qualified name with a type error if lookup fails. This is used to find the qualified name of a user defined type.(:kind! FindQualified "my error message:"7FindQualified "my error message:" :: [(k1, [(k2, k3)])]6 -> k3 -> (k1, k2)#= FindQualified "my error message:":kind! FindQualified "couldn't find type:" '[ "foo" ::: '["bar" ::: Double]] DoubleFindQualified "couldn't find type:" '[ "foo" ::: '["bar" ::: Double]] Double :: (Symbol, Symbol)= '("foo", "bar"):kind! FindQualified "couldn't find type:" '[ "foo" ::: '["bar" ::: Double]] BoolFindQualified "couldn't find type:" '[ "foo" ::: '["bar" ::: Double]] Bool :: (Symbol, Symbol)= (TypeError ...)squeal-postgresqlUsed in squeal-postgresqlUsed in squeal-postgresqlFilters schemas down to rows of relations; all composites, tables and views.squeal-postgresqlFilters a schema down to rows of relations; all composites, tables and views.squeal-postgresql4Filters schemas down to labels of all enum typedefs.squeal-postgresql5Filters a schema down to labels of all enum typedefs.squeal-postgresqlUpdatable lists of columnssqueal-postgresql No elem of xs6 appears more than once, in the context of assignment.squeal-postgresqlUtility class for ! to provide nicer error messages.squeal-postgresqlIs a type a valid JSON type?squeal-postgresqlIs a type a valid JSON key?squeal-postgresqlA  unit type with an  instancesqueal-postgresql looks very much like the ' class. Whereas the overloaded label, ! is used for column references, s are used for enum terms. A ' is called with type application like  @"beef".squeal-postgresql2A type family to use for a single schema database.squeal-postgresqlA database contains one or more named schemas, which in turn contain tables. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.1 '[ "id" ::: 'Def :=> 'NotNull 'PGint40 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]) , "emails" ::: 'Table (/ '[ "pk_emails" ::: 'PrimaryKey '["id"] , "fk_user_id" ::: 'ForeignKey '["user_id"] "public" "users" '["id"] ] :=>4 '[ "id" ::: 'Def :=> 'NotNull 'PGint43 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint43 , "email" ::: 'NoDef :=> 'Null 'PGtext ]) ]:}squeal-postgresqlReturn type of a functionsqueal-postgresqlfunctionsqueal-postgresqlset returning functionsqueal-postgresqlPostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN and BRIN. Each index type uses a different algorithm that is best suited to different types of queries.squeal-postgresqlB-trees can handle equality and range queries on data that can be sorted into some ordering.squeal-postgresql9Hash indexes can only handle simple equality comparisons.squeal-postgresqlGiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented.squeal-postgresqlSP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches.squeal-postgresqlGIN indexes are @inverted indexes@ which are appropriate for data values that contain multiple component values, such as arrays.squeal-postgresqlBRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table.squeal-postgresqlUse  to pair the parameter types with the return type of a function.:{$type family Fn :: FunctionType where; Fn = '[ 'NotNull 'PGint4] :=> 'Returns ('NotNull 'PGint4):}squeal-postgresqlA  is a user-created type, like a ,  or .squeal-postgresql Drop all s that involve a columnsqueal-postgresql Check if a  involves a columnsqueal-postgresql checks that a schema may be found as a subset of another in a database, regardless of ordering.squeal-postgresql checks that one 0 is a subset of another, regardless of ordering.:kind! SubsetDB '["a" ::: '["d" ::: 'Typedef 'PGint2, "b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4, "d" ::: 'Typedef 'PGint2]]SubsetDB '["a" ::: '["d" ::: 'Typedef 'PGint2, "b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4, "d" ::: 'Typedef 'PGint2]] :: Bool= 'Truesqueal-postgresql checks that one 1 is a sublist of another, with the same ordering.:kind! SubDB '["a" ::: '["b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4]]SubDB '["a" ::: '["b" ::: 'View '[]]] '["a" ::: '["b" ::: 'View '[], "c" ::: 'Typedef 'PGint4]] :: Bool= 'Truesqueal-postgresql)Move an object from one schema to anothersqueal-postgresql Similar to  but no error on non-existencesqueal-postgresqlRename alias0 alias1 xs replaces the alias alias0 by alias1 in xs and is used in Q and R.squeal-postgresql Similar to  but no error on non-existencesqueal-postgresqlAlter alias x xs& replaces the type associated with an alias in xs with the type x and is used in S and T.squeal-postgresql Similar to  but no error on non-existencesqueal-postgresql Similar to  but no error on non-existencesqueal-postgresql(Drop a particular flavor of schemum typesqueal-postgresql Drop alias xs" removes the type associated with alias in xs and is used in U statements and in  ALTER TABLE V statements.squeal-postgresql Similar to / but used to replace values with the same type.squeal-postgresql Similar to  but no error on pre-existencesqueal-postgresqlCreate alias x xs adds  alias ::: x to the end of xs and is used in :W statements and in  ALTER TABLE :X.squeal-postgresql# is an idempotent that nullifies a  used to nullify the left or right hand side of an outer join in a K.squeal-postgresql# is an idempotent that nullifies a .squeal-postgresql# is an idempotent that nullifies a .squeal-postgresql is a constraint that proves a  has some NOT NULL.squeal-postgresql is a constraint that proves a  has no NULLs.squeal-postgresql&Equality constraint on the underlying  of two columns.squeal-postgresqlIntegral Postgres types.squeal-postgresqlFloating Postgres types.squeal-postgresqlNumeric Postgres types.squeal-postgresqlConvert a table to a row type.squeal-postgresql removes table constraints.squeal-postgresql removes column constraints.squeal-postgresql is a row of s. It can be thought of as a product, or horizontal gluing and is used in Ks and  Ys.squeal-postgresqlA  is a row of 7s. They correspond to Haskell record types by means of Z[ and are used in many places.:{&type family PersonRow :: RowType where PersonRow =) '[ "name" ::: 'NotNull 'PGtext) , "age" ::: 'NotNull 'PGint4) , "dateOfBirth" ::: 'Null 'PGdate ]:}squeal-postgresql encodes a row of constraints on a table as well as the types of its columns.:{)type family UsersTable :: TableType where UsersTable =/ '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>1 '[ "id" ::: 'Def :=> 'NotNull 'PGint41 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]:}squeal-postgresqlA + must reference columns that either are a  or form a  constraint.squeal-postgresqlA  is a row of s.:{6type family UsersConstraints :: TableConstraints where< UsersConstraints = '[ "pk_users" ::: 'PrimaryKey '["id"] ]:}squeal-postgresql encodes various forms of data constraints of columns in a table. s give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.squeal-postgresql is a row of s.:{-type family UsersColumns :: ColumnsType where UsersColumns =- '[ "name" ::: 'NoDef :=> 'NotNull 'PGtext- , "id" ::: 'Def :=> 'NotNull 'PGint4 ]:}squeal-postgresql encodes the allowance of DEFAULT and NULL and the base  for a column. :set -XTypeFamilies -XTypeInTypeimport GHC.TypeLitstype family IdColumn :: ColumnType where IdColumn = 'Def :=> 'NotNull 'PGint4type family EmailColumn :: ColumnType where EmailColumn = 'NoDef :=> 'Null 'PGtextsqueal-postgresql encodes the availability of DEFAULT for inserts and updates. A column can be assigned a default value. A data \] command can also request explicitly that a column be set to its default value, without having to know what that value is.squeal-postgresqlDEFAULT% is available for inserts and updatessqueal-postgresqlDEFAULT' is unavailable for inserts and updatessqueal-postgresqlThe constraint operator,  is a type level pair between a "constraint" and some type, for use in pairing an  with a  to produce a  or a  and a  to produce a .squeal-postgresql: encodes the potential presence or definite absence of a NULL allowing operations which are sensitive to such to be well typed.:kind 'Null 'PGint4'Null 'PGint4 :: NullType:kind 'NotNull ('PGvarchar 50)$'NotNull ('PGvarchar 50) :: NullTypesqueal-postgresqlNULL may be presentsqueal-postgresqlNULL is absentsqueal-postgresql. is the promoted datakind of PostgreSQL types. :kind 'PGbool'PGbool :: PGTypesqueal-postgresqllogical Boolean (true/false)squeal-postgresqlsigned two-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned eight-byte integersqueal-postgresql arbitrary precision numeric typesqueal-postgresql0single precision floating-point number (4 bytes)squeal-postgresql0double precision floating-point number (8 bytes)squeal-postgresqlcurrency amountsqueal-postgresqlfixed-length character stringsqueal-postgresql variable-length character stringsqueal-postgresql variable-length character stringsqueal-postgresqlbinary data ("byte array")squeal-postgresqldate and time (no time zone)squeal-postgresql"date and time, including time zonesqueal-postgresql calendar date (year, month, day)squeal-postgresqltime of day (no time zone)squeal-postgresql time of day, including time zonesqueal-postgresql time spansqueal-postgresqluniversally unique identifiersqueal-postgresqlIPv4 or IPv6 host addresssqueal-postgresqltextual JSON datasqueal-postgresqlbinary JSON data, decomposedsqueal-postgresqlvariable length arraysqueal-postgresqlfixed length arraysqueal-postgresqlenumerated (enum) types are data types that comprise a static, ordered set of values.squeal-postgresqla composite type represents the structure of a row or record; it is essentially just a list of field names and their data types.squeal-postgresqlA tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word.squeal-postgresql;A tsquery value stores lexemes that are to be searched for.squeal-postgresqlObject identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.squeal-postgresqlRange types are data types representing a range of values of some element type (called the range's subtype).squeal-postgresql0an escape hatch for unsupported PostgreSQL types74embedding of Haskell types into Postgres type system(c) Eitan Chatav, 2010eitan@morphism.tech experimental Safe-Inferred!")*0189:;<=?~1squeal-postgresql extracts  of the base type of nested homogeneous tuples, up to a depth of 10 for each dimension.squeal-postgresql turns Haskell nested homogeneous tuples into a list of lengths, up to a depth of 10 for each dimension.squeal-postgresql,Calculate the names of nullary constructors.squeal-postgresqlCalculates the name of a nullary constructor, otherwise generates a type error.squeal-postgresql&Calculates constructors of a datatype.squeal-postgresql takes the  of a haskell > and if it's a simple product returns it, otherwise giving a .squeal-postgresql turns a list of Haskell s into a list of s.squeal-postgresql turns a Haskell tuple type (including record types) into the corresponding list of s.#:kind! TuplePG (Double, Maybe Char)*TuplePG (Double, Maybe Char) :: [NullType]+= '[ 'NotNull 'PGfloat8, 'Null ('PGchar 1)]squeal-postgresql turns a Haskell type into a .:kind! NullPG DoubleNullPG Double :: NullType= 'NotNull 'PGfloat8:kind! NullPG (Maybe Double)!NullPG (Maybe Double) :: NullType= 'Null 'PGfloat8squeal-postgresql applies  to the fields of a list.squeal-postgresql turns a Haskell  into a . may be applied to normal Haskell record types provided they have  and  instances;data Person = Person { name :: Strict.Text, age :: Int32 } deriving GHC.Genericinstance SOP.Generic Person#instance SOP.HasDatatypeInfo Person:kind! RowPG Person$RowPG Person :: [(Symbol, NullType)]<= '["name" ::: 'NotNull 'PGtext, "age" ::: 'NotNull 'PGint4]squeal-postgresqlThe  type family calculates the constructors of a Haskell enum type.:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo Schwarma:kind! LabelsPG Schwarma+LabelsPG Schwarma :: [Type.ConstructorName]= '["Beef", "Lamb", "Chicken"]squeal-postgresqlThe  type family embeds a subset of Haskell types as Postgres types. As an open type family,  is extensible.:kind! PG LocalTimePG LocalTime :: PGType= 'PGtimestampThe preferred way to generate s of your own type is through generalized newtype deriving or via deriving.newtype UserId = UserId {getUserId :: UUID} deriving newtype IsPG:kind! PG UserIdPG UserId :: PGType = 'PGuuid:{data Answer = Yes | No deriving stock GHC.Generic6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)% deriving IsPG via Enumerated Answer:}:kind! PG AnswerPG Answer :: PGType= 'PGenum '["Yes", "No"]:{ '["col" ::: 'NoDef :=> 'Null 'PGint2])]]:set -XTypeApplications:set -XOverloadedStringsconn <- connectdb @DB "host=localhost port=5432 dbname=exampledb user=postgres password=postgres"Note that, for now, squeal doesn't offer any protection from connecting with the wrong schema!squeal-postgresql$Closes the connection to the server.squeal-postgresqlSafely  to a smaller schema.squeal-postgresqlconninfostructured query language(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;?Ӛsqueal-postgresqlThe  type is parameterized by a db 4, against which the query is type-checked, an input params Haskell , and an ouput row Haskell .A  can be run using 7^, or if  params = () using 7_. Generally, params will be a Haskell tuple or record whose entries may be referenced using positional `s and row will be a Haskell record, whose entries will be targeted using overloaded labels.( is a type family which resolves into a , so don't be fooled by the input params and output row Haskell 1s, which are converted into appropriate Postgres [] params and  rows. Use -a4 to fix actual Haskell input params and output rows.*:set -XDeriveAnyClass -XDerivingStrategiestype Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint8, "col2" ::: 'Def :=> 'NotNull 'PGtext]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{6data Row = Row { col1 :: Maybe Int64, col2 :: String } deriving stock (GHC.Generic)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):}:{let1 qry :: Query_ (Public Schema) (Int64, Bool) Row qry = select Star (from (table #tab) & where_ (#col1 .> param @1 .&& just_ (param @2)))5 stmt :: Statement (Public Schema) (Int64, Bool) Row stmt = query qry:} :type qryqry :: Query '[] '[]< '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]]- '[ 'NotNull 'PGint8, 'NotNull 'PGbool]? '["col1" ::: 'Null 'PGint8, "col2" ::: 'NotNull 'PGtext] :type stmtstmt :: Statement< '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]] (Int64, Bool) Rowsqueal-postgresqlThe process of retrieving or the command to retrieve data from a database is called a . The general  type is parameterized bylat :: FromType - scope for !b and subquery expressions,with :: FromType - scope for all c table expressions,db :: SchemasType - scope for all ds and es,params :: [NullType] - scope for all f`s,row :: RowType - return type of the .Let's see some  examples. simple query:type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let qry :: Query lat with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]' qry = select Star (from (table #tab))in printSQL qry:}SELECT * FROM "tab" AS "tab"restricted query::{ let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry =< select_ ((#col1 + #col2) `as` #col1 :* #col1 `as` #col2) ( from (table #tab)! & where_ (#col1 .> #col2) & where_ (#col2 .> 0) )in printSQL qry:}SELECT ("col1" + "col2") AS "col1", "col1" AS "col2" FROM "tab" AS "tab" WHERE (("col1" > "col2") AND ("col2" > (0 :: int4))) subquery::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (subquery (select Star (from (table #tab)) `as` #sub)))in printSQL qry:}5SELECT * FROM (SELECT * FROM "tab" AS "tab") AS "sub"limits and offsets::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (table #tab) & limit 100 & offset 2 & limit 50 & offset 2)in printSQL qry:}.SELECT * FROM "tab" AS "tab" LIMIT 50 OFFSET 4parameterized query::{let qry :: Query '[] with (Public Schema) '[ 'NotNull 'PGint4] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (table #tab) & where_ (#col1 .> param @1))in printSQL qry:}:SELECT * FROM "tab" AS "tab" WHERE ("col1" > ($1 :: int4))aggregation query::{ let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint8, "col2" ::: 'NotNull 'PGint4] qry = select_ ((fromNull 0 (sum_ (All #col2))) `as` #col1 :* #col1 `as` #col2)& ( from (table (#tab `as` #table1)) & groupBy #col1- & having (sum_ (Distinct #col2) .> 1) )in printSQL qry:}SELECT COALESCE(sum(ALL "col2"), (0 :: int8)) AS "col1", "col1" AS "col2" FROM "tab" AS "table1" GROUP BY "col1" HAVING (sum(DISTINCT "col2") > (1 :: int8)) sorted query::{let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]? qry = select Star (from (table #tab) & orderBy [#col1 & Asc])in printSQL qry:}0SELECT * FROM "tab" AS "tab" ORDER BY "col1" ASCjoins::{type OrdersColumns =1 '[ "id" ::: 'NoDef :=> 'NotNull 'PGint44 , "price" ::: 'NoDef :=> 'NotNull 'PGfloat42 , "customer_id" ::: 'NoDef :=> 'NotNull 'PGint45 , "shipper_id" ::: 'NoDef :=> 'NotNull 'PGint4 ]:}:{type OrdersConstraints =& '["pk_orders" ::: PrimaryKey '["id"] ,"fk_customers" ::: ForeignKey '["customer_id"] "public" "customers" '["id"] ,"fk_shippers" ::: ForeignKey '["shipper_id"] "public" "shippers" '["id"] ]:}type NamesColumns = '["id" ::: 'NoDef :=> 'NotNull 'PGint4, "name" ::: 'NoDef :=> 'NotNull 'PGtext]type CustomersConstraints = '["pk_customers" ::: PrimaryKey '["id"]]type ShippersConstraints = '["pk_shippers" ::: PrimaryKey '["id"]]:{type OrdersSchema = '[ "orders" ::: 'Table (OrdersConstraints :=> OrdersColumns) , "customers" ::: 'Table (CustomersConstraints :=> NamesColumns) , "shippers" ::: 'Table (ShippersConstraints :=> NamesColumns) ]:}:{type OrderRow =# '[ "price" ::: 'NotNull 'PGfloat4( , "customerName" ::: 'NotNull 'PGtext' , "shipperName" ::: 'NotNull 'PGtext ]:}:{let= qry :: Query lat with (Public OrdersSchema) params OrderRow qry = select_ ( #o ! #price `as` #price :*& #c ! #name `as` #customerName :*$ #s ! #name `as` #shipperName )# ( from (table (#orders `as` #o). & innerJoin (table (#customers `as` #c))( (#o ! #customer_id .== #c ! #id)- & innerJoin (table (#shippers `as` #s))* (#o ! #shipper_id .== #s ! #id)) )in printSQL qry:}SELECT "o"."price" AS "price", "c"."name" AS "customerName", "s"."name" AS "shipperName" FROM "orders" AS "o" INNER JOIN "customers" AS "c" ON ("o"."customer_id" = "c"."id") INNER JOIN "shippers" AS "s" ON ("o"."shipper_id" = "s"."id") self-join::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select (#t1 & DotStar) (from (table (#tab `as` #t1) & crossJoin (table (#tab `as` #t2))))in printSQL qry:}9SELECT "t1".* FROM "tab" AS "t1" CROSS JOIN "tab" AS "t2"value queries::{let qry :: Query lat with db params '["col1" ::: 'NotNull 'PGtext, "col2" ::: 'NotNull 'PGbool] qry = values* ("true" `as` #col1 :* true `as` #col2), ["false" `as` #col1 :* false `as` #col2]in printSQL qry:}SELECT * FROM (VALUES ((E'true' :: text), TRUE), ((E'false' :: text), FALSE)) AS t ("col1", "col2")set operations::{let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = select Star (from (table #tab)) `unionAll` select Star (from (table #tab))in printSQL qry:}(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab") with query::{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with (2 select Star (from (table #tab)) `as` #cte1 :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"window functions::{let qry :: Query '[] with (Public Schema) db '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint8] qry = select (#col1 & Also (rank `as` #col2 `Over` (partitionBy #col1 & orderBy [#col2 & Asc]))) (from (table #tab))in printSQL qry:}SELECT "col1" AS "col1", rank() OVER (PARTITION BY "col1" ORDER BY "col2" ASC) AS "col2" FROM "tab" AS "tab"correlated subqueries::{ let qry :: Query '[] with (Public Schema) params '["col1" ::: 'NotNull 'PGint4] qry =. select #col1 (from (table (#tab `as` #t1)) & where_ (exists (/ select Star (from (table (#tab `as` #t2))0 & where_ (#t2 ! #col2 .== #t1 ! #col1)))))in printSQL qry:}SELECT "col1" AS "col1" FROM "tab" AS "t1" WHERE EXISTS (SELECT * FROM "tab" AS "t2" WHERE ("t2"."col2" = "t1"."col1"))squeal-postgresqlThe results of two queries can be combined using the set operation  . Duplicate rows are eliminated.squeal-postgresqlThe results of two queries can be combined using the set operation 2, the disjoint union. Duplicate rows are retained.squeal-postgresqlThe results of two queries can be combined using the set operation 2, the intersection. Duplicate rows are eliminated.squeal-postgresqlThe results of two queries can be combined using the set operation 0, the intersection. Duplicate rows are retained.squeal-postgresqlThe results of two queries can be combined using the set operation 4, the set difference. Duplicate rows are eliminated.squeal-postgresqlThe results of two queries can be combined using the set operation 2, the set difference. Duplicate rows are retained.  with statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;? squeal-postgresqlA  is an auxiliary statement in a  clause.squeal-postgresqlWhether the contents of the WITH clause are materialized. If a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. Note: Use of  or  requires PostgreSQL version 12 or higher. For earlier versions, use : which in those earlier versions of PostgreSQL behaves as . PostgreSQL 12 both changes the default behavior as well as adds options for customizing the materialization behavior.squeal-postgresqlBy default, folding happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. Note: this is the behavior in PostgreSQL 12+. In PostgreSQL 11 and earlier, all CTEs are materialized.squeal-postgresqlYou can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query. Requires PostgreSQL 12+.squeal-postgresqlor by specifying NOT MATERIALIZED to force it to be merged into the parent query. Requires PostgreSQL 12+.squeal-postgresql provides a way to write auxiliary statements for use in a larger query. These statements, referred to as s, can be thought of as defining temporary tables that exist just for one query. can be used for a  . Multiple #s can be chained together with the   constructor   , and each  is constructed via overloaded .type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with (2 select Star (from (table #tab)) `as` #cte1 :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2")You can use data-modifying statements in . This allows you to perform several different operations in the same query. An example is:type ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]:{let manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[] manp = with (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del) (insertInto_ #products_deleted (Subquery (select Star (from (common #del)))))in printSQL manp:}WITH "del" AS (DELETE FROM "products" AS "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" AS "products_deleted" SELECT * FROM "del" AS "del"squeal-postgresqlA   can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:import Data.Monoid (Sum (..))import Data.Int (Int64):{ let) sum100 :: Statement db () (Sum Int64) sum100 = query $ withRecursive, ( values_ ((1 & astype int) `as` #n) `unionAll`$ select_ ((#n + 1) `as` #n)= (from (common #t) & where_ (#n .< 100)) `as` #t ) ( select_5 (fromNull 0 (sum_ (All #n)) `as` #getSum). (from (common #t) & groupBy Nil) ) in printSQL sum100:}WITH RECURSIVE "t" AS ((SELECT * FROM (VALUES (((1 :: int4) :: int))) AS t ("n")) UNION ALL (SELECT ("n" + (1 :: int4)) AS "n" FROM "t" AS "t" WHERE ("n" < (100 :: int4)))) SELECT COALESCE(sum(ALL "n"), (0 :: int8)) AS "getSum" FROM "t" AS "t"The general form of a recursive WITH query is always a non-recursive term, then  (or ), then a recursive term, where only the recursive term can contain a reference to the query's own output.squeal-postgresql-Force separate calculation of the WITH query.type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with ( materialized (select Star (from (table #tab)) `as` #cte1) :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS MATERIALIZED (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"Note: if the last CTE has  or  you must add `:>> Done`.!Requires PostgreSQL 12 or higher.squeal-postgresql8Force the WITH query to be merged into the parent query.type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{ let qry :: Query lat with (Public Schema) params '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] qry = with (2 select Star (from (table #tab)) `as` #cte1 :>> notMaterialized (select Star (from (common #cte1)) `as` #cte2) :>> Done) ) (select Star (from (common #cte2)))in printSQL qry:}WITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS NOT MATERIALIZED (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"Note: if the last CTE has  or ' you must add `:>> Done` to finish the  .!Requires PostgreSQL 12 or higher.squeal-postgresqlaliased statementsqueal-postgresql!materialization of the CTE outputsqueal-postgresqlcommon table expressionssqueal-postgresql larger querysqueal-postgresqlrecursive querysqueal-postgresql larger querysqueal-postgresqlCTEsqueal-postgresqlCTE   from clauses(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;?squeal-postgresqlA ? can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these.squeal-postgresqlA real  is a table from the database.squeal-postgresql derives a table from a ?. The subquery may not reference columns provided by preceding  items. Use !b> if the subquery must reference columns provided by preceding  items.squeal-postgresql derives a table from a .squeal-postgresql0 derives a table from a common table expression.squeal-postgresql(renamable) table aliassqueal-postgresqlaliased squeal-postgresql(renamable) view aliassqueal-postgresql)(renamable) common table expression aliasoptional expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred")*01squeal-postgresql is either  or 2ting of a value, parameterized by an appropriate .squeal-postgresqlUse the  value for a column.squeal-postgresql a value for a column.squeal-postgresql pattern analagous to .squeal-postgresqlMap a function over an  expression. expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;?squeal-postgresqlIntersection operatorsqueal-postgresqlContained by operatorssqueal-postgresqlA  RankNType for functions with a variable-length list of homogeneous arguments and at least 1 more argument.squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType for functions with a fixed-length list of heterogeneous arguments. Use the . operator to end your argument lists, like so.printSQL (unsafeFunctionN "fun" (true :* false :* localTime *: true))!fun(TRUE, FALSE, LOCALTIME, TRUE)squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType for functions with a single argument. These could be either function calls or unary operators. This is a subtype of the usual Haskell function type gh9, indeed a subcategory as it is closed under the usual gi and gj.squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType for binary operators.squeal-postgresqlAn  is a closed  . It is a F RankNType but don't be scared. Think of it as an expression which sees no namespaces, so you can't use parameters or alias references. It can be used as a simple piece of more complex s.squeal-postgresql;s are used in a variety of contexts, such as in the target k of the l" command, as new column values in ,m or ,n, or in search os in a number of commands.The expression syntax allows the calculation of values from primitive expression using arithmetic, logical, and other operations.The type parameters of  arelat ::  , the from+ clauses of any lat queries in which the % is a correlated subquery expression;with ::  , the ps that are in scope for the ;grp ::  , the  of the from clause which may limit which columns may be referenced by alias;db ::  ;, the schemas of your database that are in scope for the ;from ::  , the from clause which the ) may use to reference columns by alias;ty ::  , the type of the .squeal-postgresql;printSQL (unsafeFunctionVar "greatest" [true, null_] false)greatest(TRUE, NULL, FALSE)squeal-postgresql)printSQL $ unsafeBinaryOp "OR" true false(TRUE OR FALSE)squeal-postgresql"printSQL $ unsafeLeftOp "NOT" true (NOT TRUE)squeal-postgresql-printSQL $ true & unsafeRightOp "IS NOT TRUE"(TRUE IS NOT TRUE)squeal-postgresql"printSQL $ unsafeFunction "f" truef(TRUE)squeal-postgresql1Call a user defined function of a single variable>type Fn = '[ 'Null 'PGint4] :=> 'Returns ('NotNull 'PGnumeric)&type Schema = '["fn" ::: 'Function Fn]:{let fn :: Fun (Public Schema) ('Null 'PGint4) ('NotNull 'PGnumeric) fn = function #fnin printSQL (fn 1):}"fn"((1 :: int4))squeal-postgresqlprintSQL $ unsafeFunctionN "f" (currentTime :* localTimestamp :* false *: inline 'a')9f(CURRENT_TIME, LOCALTIMESTAMP, FALSE, (E'a' :: char(1)))squeal-postgresql*Call a user defined multivariable functiontype Fn = '[ 'Null 'PGint4, 'Null 'PGbool] :=> 'Returns ('NotNull 'PGnumeric)&type Schema = '["fn" ::: 'Function Fn]:{let fn :: FunN (Public Schema) '[ 'Null 'PGint4, 'Null 'PGbool] ('NotNull 'PGnumeric) fn = functionN #fnin printSQL (fn (1 *: true)):}"fn"((1 :: int4), TRUE)squeal-postgresqlinputssqueal-postgresqlmust have at least 1 inputsqueal-postgresqloutputsqueal-postgresqlinputssqueal-postgresqloutputsqueal-postgresqlinputsqueal-postgresqloutputsqueal-postgresql left inputsqueal-postgresql right inputsqueal-postgresqloutputsqueal-postgresqlcannot reference aliasessqueal-postgresql function namesqueal-postgresqlfunction alias44values statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;?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]let query = values (1 `as` #a :* "one" `as` #b) [] :: Query lat with db '[] RowprintSQL querySELECT * FROM (VALUES ((1 :: int4), (E'one' :: text))) AS t ("a", "b")squeal-postgresql computes a row value or set of row values specified by value expressions.squeal-postgresqlWhen more than one row is specified, all the rows must must have the same number of elementssqueal-postgresqlone row of valuesset returning functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;? squeal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlLike + but depends on the schemas of the databasesqueal-postgresqlA  RankNType4 for set returning functions with multiple argument.squeal-postgresqlA  RankNType- for set returning functions with 1 argument.squeal-postgresql>Escape hatch for a set returning function of a single variablesqueal-postgresql?Call a user defined set returning function of a single variabletype Fn = '[ 'Null 'PGbool] :=> 'ReturnsTable '["ret" ::: 'NotNull 'PGnumeric]&type Schema = '["fn" ::: 'Function Fn]:{let fn :: SetFun (Public Schema) ('Null 'PGbool) ("fn" ::: '["ret" ::: 'NotNull 'PGnumeric]) fn = setFunction #fnin printSQL (fn true):} "fn"(TRUE)squeal-postgresql7Escape hatch for a multivariable set returning functionsqueal-postgresql8Call a user defined multivariable set returning functiontype Fn = '[ 'Null 'PGbool, 'Null 'PGtext] :=> 'ReturnsTable '["ret" ::: 'NotNull 'PGnumeric]&type Schema = '["fn" ::: 'Function Fn]:{ let fn :: SetFunN (Public Schema)$ '[ 'Null 'PGbool, 'Null 'PGtext]/ ("fn" ::: '["ret" ::: 'NotNull 'PGnumeric]) fn = setFunctionN #fnin printSQL (fn (true *: "hi")):}"fn"(TRUE, (E'hi' :: text))squeal-postgresql generateSeries (start :* stop)"Generate a series of values, from start to stop with a step size of one,printSQL (generateSeries @'PGint4 (1 *: 10))*generate_series((1 :: int4), (10 :: int4))squeal-postgresql *generateSeriesStep (start :* stop *: step)"Generate a series of values, from start to stop with a step size of step6printSQL (generateSeriesStep @'PGint8 (2 :* 100 *: 2))8generate_series((2 :: int8), (100 :: int8), (2 :: int8))squeal-postgresql /generateSeriesTimestamp (start :* stop *: step)&Generate a series of timestamps, from start to stop with a step size of step:{let start = now" stop = now !+ interval_ 10 Years step = interval_ 1 Months=in printSQL (generateSeriesTimestamp (start :* stop *: step)):}generate_series(now(), (now() + (INTERVAL '10.000 years')), (INTERVAL '1.000 months')) squeal-postgresqlinputsqueal-postgresqloutputsqueal-postgresqlinputsqueal-postgresqloutputsqueal-postgresqloutputsqueal-postgresqlset returning functionsqueal-postgresqlfunction aliassqueal-postgresqlset returning functionsqueal-postgresqlfunction aliassqueal-postgresqlset returning functionsqueal-postgresqlset returning functionsqueal-postgresqlset returning function  type expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred")*1;?*bsqueal-postgresqlLike PGTyped but also accounts for null.squeal-postgresqlLike PGTyped but also accounts for null.squeal-postgresqls are used in W commands.squeal-postgresqlLift  to a fieldsqueal-postgresql is a demoted version of a squeal-postgresqls are used in s and W commands.squeal-postgresqlprintSQL $ true & cast int4(TRUE :: int4)squeal-postgresqlA safe version of * which just matches a value with its type.printSQL (1 & astype int)((1 :: int4) :: int)squeal-postgresql" will add a type annotation to an = which can be useful for fixing the storage type of a value.printSQL (inferredtype true)(TRUE :: bool)squeal-postgresqlThe composite type corresponding to a relation can be expressed by its alias. A relation is either a composite type, a table or a view. It subsumes  and  and partly overlaps .squeal-postgresql3An enumerated type can be expressed by its alias.  is subsumed by .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. It is subsumed by squeal-postgresql&The composite type corresponding to a > definition can be expressed by its alias. It is subsumed by .squeal-postgresqllogical Boolean (true/false)squeal-postgresqlsigned two-byte integersqueal-postgresqlsigned two-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned four-byte integersqueal-postgresqlsigned eight-byte integersqueal-postgresqlsigned eight-byte integersqueal-postgresql arbitrary precision numeric typesqueal-postgresql0single precision floating-point number (4 bytes)squeal-postgresql0single precision floating-point number (4 bytes)squeal-postgresql0double precision floating-point number (8 bytes)squeal-postgresql0double precision floating-point number (8 bytes)squeal-postgresqlcurrency amountsqueal-postgresql variable-length character stringsqueal-postgresqlfixed-length character stringsqueal-postgresqlfixed-length character stringsqueal-postgresql variable-length character stringsqueal-postgresql variable-length character stringsqueal-postgresqlbinary data ("byte array")squeal-postgresqldate and time (no time zone)squeal-postgresql"date and time, including time zonesqueal-postgresql"date and time, including time zonesqueal-postgresql calendar date (year, month, day)squeal-postgresqltime of day (no time zone)squeal-postgresql time of day, including time zonesqueal-postgresql time of day, including time zonesqueal-postgresql time spansqueal-postgresqluniversally unique identifiersqueal-postgresqlIPv4 or IPv6 host addresssqueal-postgresqltextual JSON datasqueal-postgresqlbinary JSON data, decomposedsqueal-postgresqlvariable length arraysqueal-postgresqlfixed length arrayrenderSQL (fixarray @'[2] json) "json[2]"squeal-postgresqltext search querysqueal-postgresqltext search documentsqueal-postgresqlObject identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables.squeal-postgresqlRange of integersqueal-postgresqlRange of bigintsqueal-postgresqlRange of numericsqueal-postgresql$Range of timestamp without time zonesqueal-postgresql!Range of timestamp with time zonesqueal-postgresql Range of datesqueal-postgresqlAnonymous composite recordsqueal-postgresqlSpecify  from a Haskell type.printSQL $ pgtypeFrom @StringtextprintSQL $ pgtypeFrom @Doublefloat8squeal-postgresqlused in W0 commands as a column constraint to note that NULL may be present in a columnsqueal-postgresqlused in W- commands as a column constraint to ensure NULL is not present in a columnsqueal-postgresqlused in W3 commands as a column constraint to give a defaultsqueal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresql Specify null  from a Haskell type.'printSQL $ nulltypeFrom @(Maybe String)textprintSQL $ nulltypeFrom @Doublefloat8squeal-postgresqlSpecify  from a Haskell type.)printSQL $ columntypeFrom @(Maybe String) text NULL!printSQL $ columntypeFrom @Doublefloat8 NOT NULL squeal-postgresqltype to cast assqueal-postgresqlvalue to convertsqueal-postgresqltype to specify assqueal-postgresqlvaluesqueal-postgresqlvaluesqueal-postgresql type aliassqueal-postgresql type aliassqueal-postgresql type aliassqueal-postgresql table aliassqueal-postgresql view aliassqueal-postgresqltypesqueal-postgresqltypesqueal-postgresql default valuesqueal-postgresql column type!date/time functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"01;9squeal-postgresqlA  to use in  construction.squeal-postgresql&Affine space operations on time types.squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !+ 365)(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) + (365 :: int4))squeal-postgresql+printSQL (365 +! makeDate (1984 :* 7 *: 3))((365 :: int4) + make_date((1984 :: int4), (7 :: int4), (3 :: int4)))squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !- 365)(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) - (365 :: int4))squeal-postgresql4printSQL (makeDate (1984 :* 7 *: 3) !-! currentDate)(make_date((1984 :: int4), (7 :: int4), (3 :: int4)) - CURRENT_DATE)squeal-postgresql&Calculate the return time type of the  .squeal-postgresqlprintSQL currentDate CURRENT_DATEsqueal-postgresqlprintSQL currentTime CURRENT_TIMEsqueal-postgresqlprintSQL currentTimestampCURRENT_TIMESTAMPsqueal-postgresqlprintSQL localTime LOCALTIMEsqueal-postgresqlprintSQL localTimestampLOCALTIMESTAMPsqueal-postgresql%Current date and time (equivalent to ) printSQL nownow()squeal-postgresql+Create date from year, month and day fields$printSQL (makeDate (1984 :* 7 *: 3))3make_date((1984 :: int4), (7 :: int4), (3 :: int4))squeal-postgresql0Create time from hour, minute and seconds fields%printSQL (makeTime (8 :* 15 *: 23.5))6make_time((8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresqlCreate timestamp from year, month, day, hour, minute and seconds fields=printSQL (makeTimestamp (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))make_timestamp((2013 :: int4), (7 :: int4), (15 :: int4), (8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresqlCreate timestamp with time zone from year, month, day, hour, minute and seconds fields; the current time zone is used?printSQL (makeTimestamptz (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))make_timestamptz((2013 :: int4), (7 :: int4), (15 :: int4), (8 :: int4), (15 :: int4), (23.5 :: float8))squeal-postgresql1Truncate a timestamp with the specified precisionprintSQL $ dateTrunc Quarter (makeTimestamp (2010 :* 5 :* 6 :* 14 :* 45 *: 11.4))date_trunc('quarter', make_timestamp((2010 :: int4), (5 :: int4), (6 :: int4), (14 :: int4), (45 :: int4), (11.4 :: float8)))squeal-postgresqlConvert a timestamp, timestamp with time zone, or time of day with timezone to a different timezone using an interval offset or specific timezone denoted by text. When using the interval offset, the interval duration must be less than one day or 24 hours.printSQL $ (makeTimestamp (2009 :* 7 :* 22 :* 19 :* 45 *: 11.4)) `atTimeZone` (interval_ 8 Hours)(make_timestamp((2009 :: int4), (7 :: int4), (22 :: int4), (19 :: int4), (45 :: int4), (11.4 :: float8)) AT TIME ZONE (INTERVAL '8.000 hours')):{ let" timezone :: Expr (null 'PGtext) timezone = "EST" in printSQL $ (makeTimestamptz (2015 :* 9 :* 15 :* 4 :* 45 *: 11.4)) `atTimeZone` timezone:}(make_timestamptz((2015 :: int4), (9 :: int4), (15 :: int4), (4 :: int4), (45 :: int4), (11.4 :: float8)) AT TIME ZONE (E'EST' :: text))squeal-postgresqlprintSQL $ interval_ 7 Days(INTERVAL '7.000 days')!!6666#text search functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1Dsqueal-postgresqlq matches tsquery ?squeal-postgresqlAND r s togethersqueal-postgresqlOR r s togethersqueal-postgresql negate a rsqueal-postgresqlr followed by rsqueal-postgresqlconvert array of lexemes to qsqueal-postgresqlnumber of lexemes in qsqueal-postgresql$number of lexemes plus operators in rsqueal-postgresqlproduce r ignoring punctuationsqueal-postgresqlproduce r2 that searches for a phrase, ignoring punctuationsqueal-postgresqlproduce r from a web search style querysqueal-postgresqlget indexable part of a rsqueal-postgresqlnormalize words and convert to rsqueal-postgresqlreduce document text to qsqueal-postgresql!assign weight to each element of qsqueal-postgresql"remove positions and weights from qsqueal-postgresql#jsonToTSvector (document *: filter)> reduce each value in the document, specified by filter to a q, and then concatenate those in document order to produce a single q. filter is a s array, that enumerates what kind of elements need to be included into the resulting q. Possible values for filter are "string" (to include all string values), "numeric" (to include all numeric values in the string format), "boolean" (to include all Boolean values in the string format "true"/"false"), "key" (to include all keys) or "all" (to include all above). These values can be combined together to include, e.g. all string and numeric values.squeal-postgresql$jsonbToTSvector (document *: filter)> reduce each value in the document, specified by filter to a q, and then concatenate those in document order to produce a single q. filter is a t array, that enumerates what kind of elements need to be included into the resulting q. Possible values for filter are "string" (to include all string values), "numeric" (to include all numeric values in the string format), "boolean" (to include all Boolean values in the string format "true"/"false"), "key" (to include all keys) or "all" (to include all above). These values can be combined together to include, e.g. all string and numeric values.squeal-postgresqlremove given lexeme from qsqueal-postgresql-select only elements with given weights from qsqueal-postgresql display a r matchtext functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1Jsqueal-postgresqlprintSQL $ lower "ARRRGGG"lower((E'ARRRGGG' :: text))squeal-postgresqlprintSQL $ upper "eeee"upper((E'eeee' :: text))squeal-postgresqlprintSQL $ charLength "four"char_length((E'four' :: text))squeal-postgresqlThe  expression returns true if the string matches the supplied pattern. If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case  acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.printSQL $ "abc" `like` "a%"'((E'abc' :: text) LIKE (E'a%' :: text))squeal-postgresqlThe key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale.printSQL $ "abc" `ilike` "a%"(((E'abc' :: text) ILIKE (E'a%' :: text))squeal-postgresql9Determines the location of the substring match using the  function. Returns the 1-based index of the first match, if no match exists the function returns (0).+printSQL $ strpos ("string" *: "substring")3strpos((E'string' :: text), (E'substring' :: text))squeal-postgresqlOver the string in the first argument, replace all occurrences of the second argument with the third and return the modified string./printSQL $ replace ("string" :* "from" *: "to")replace((E'string' :: text), (E'from' :: text), (E'to' :: text))sort expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred ")*15Psqueal-postgresqlThe $ clause causes the result rows of a Y) to be sorted according to the specified (s). If two rows are equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.You can also control the order in which rows are processed by window functions using  within u.squeal-postgresqls are used by & to optionally sort the results of a v.  or  set the sort direction of a  result column to ascending or descending. Ascending order puts smaller values first, where "smaller" is defined in terms of the &w operator. Similarly, descending order is determined with the &x operator. , ,  and  options are used to determine whether nulls appear before or after non-null values in the sort ordering of a  result column.squeal-postgresqlsortssqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort bysqueal-postgresqlsort by  range types and functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"189:;=?Z#squeal-postgresqlA = datatype that comprises connected subsets of the real line.squeal-postgresql The type of  for a .squeal-postgresql unboundedsqueal-postgresql inclusivesqueal-postgresql exclusivesqueal-postgresql Construct a (printSQL $ range tstzrange (atLeast now)tstzrange(now(), NULL, '[)')(printSQL $ range numrange (0 <=..< 2*pi);numrange((0.0 :: numeric), ((2.0 :: numeric) * pi()), '[)') printSQL $ range int4range Empty('empty' :: int4range)squeal-postgresqlFinite  constructorsqueal-postgresqlFinite  constructorsqueal-postgresqlFinite  constructorsqueal-postgresqlFinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlHalf-infinite  constructorsqueal-postgresqlA point on the linesqueal-postgresqlThe  linesqueal-postgresqlrange is contained bysqueal-postgresqlcontains rangesqueal-postgresql?strictly left of, return false when an empty range is involvedsqueal-postgresqlstrictly right of, return false when an empty range is involvedsqueal-postgresqldoes not extend to the right of, return false when an empty range is involvedsqueal-postgresqldoes not extend to the left of, return false when an empty range is involvedsqueal-postgresql x) .* (\(_,y,_) -> y) *. (\(_,_,z) -> z)in runReaderT (runEncodeParams encode (Nothing, "foo", 'z')) conn:}2K Nothing :* K (Just "foo") :* K (Just "z") :* Nil finish connsqueal-postgresqlEncode 1 parameter.conn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let7 encode :: EncodeParams '[] '[ 'NotNull 'PGint4] Int32 encode = aParam0in runReaderT (runEncodeParams encode 1776) conn:}"K (Just "\NUL\NUL\ACK\240") :* Nil finish connsqueal-postgresqlAppend parameter encodings.conn <- connectdb @'[] "host=localhost port=5432 dbname=exampledb user=postgres password=postgres":{let encode :: EncodeParams '[]* '[ 'NotNull 'PGint4, 'NotNull 'PGint2] (Int32, Int16) encode = contramap fst aParam `appendParams` contramap snd aParam5in runReaderT (runEncodeParams encode (1776, 2)) conn:}9K (Just "\NUL\NUL\ACK\240") :* K (Just "\NUL\STX") :* Nil finish connsqueal-postgresql%:set -XLambdaCase -XFlexibleInstances:{ &data Dir = North | South | East | Westinstance IsPG Dir where; type PG Dir = 'PGenum '["north", "south", "east", "west"]instance ToPG db Dir where toPG = enumParam $ \case North -> label @"north" South -> label @"south" East -> label @"east" West -> label @"west":}squeal-postgresql':set -XTypeFamilies -XFlexibleInstances:{ data Complex = Complex { real :: Double , imaginary :: Double }instance IsPG Complex where# type PG Complex = 'PGcomposite '[ "re" ::: 'NotNull 'PGfloat8, "im" ::: 'NotNull 'PGfloat8]instance ToPG db Complex where7 toPG = rowParam $ real `as` #re #. imaginary `as` #im:}squeal-postgresql"Cons a row parameter encoding for . squeal-postgresql!End a row parameter encoding for . squeal-postgresqlimport GHC.Generics as GHC:{(data L = L {frst :: Int16, scnd :: 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)instance IsPG (L,R) where! type PG (L,R) = 'PGcomposite '[ "frst" ::: 'NotNull 'PGint2,$ "scnd" ::: 'NotNull ('PGchar 1), "thrd" ::: 'NotNull 'PGbool, "frth" ::: 'NotNull 'PGbool]instance ToPG db (L,R) where toPG = rowParam $" contramap fst genericRowParams `appendParams`" contramap snd genericRowParams:}squeal-postgresqlheadsqueal-postgresqltailsqueal-postgresqlsecond to lastsqueal-postgresqllastsqueal-postgresqla single parametersqueal-postgresqlleftsqueal-postgresqlrightsqueal-postgresqlmatch cases with enum ssqueal-postgresqluse  and # to define a row parameter encodingsqueal-postgresqlheadsqueal-postgresqltailsqueal-postgresqlsecond to lastsqueal-postgresqllast5858decoding of result values(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"01?\squeal-postgresqlAssistant class for ;, this class forgets the name of a field while decoding it.squeal-postgresqlA  constraint to ensure that a Haskell type is a record type, has a , and all its fields and can be decoded from corresponding Postgres fields.squeal-postgresqlRow decoder for  records.$import qualified GHC.Generics as GHC$import qualified Generics.SOP as SOPdata Two = Two {frst :: Int16, scnd :: String} deriving (Show, GHC.Generic, SOP.Generic, SOP.HasDatatypeInfo):{let decode :: DecodeRow '[ "frst" ::: 'NotNull 'PGint2, "scnd" ::: 'NotNull 'PGtext] Two decode = genericRowin runDecodeRow decode (SOP.K (Just "\NUL\STX") :* SOP.K (Just "two") :* Nil):}$Right (Two {frst = 2, scnd = "two"})squeal-postgresql& describes a decoding of a PostgreSQL  into a Haskell .' has an interface given by the classes , , , , ,  , and .:set -XOverloadedLabels:{let decode :: DecodeRow '[ "fst" ::: 'NotNull 'PGint2, "snd" ::: 'NotNull ('PGchar 1)] (Int16, Char) decode = (,) <$> #fst <*> #sndin runDecodeRow decode (SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* Nil):} Right (1,'a')There is also an  instance for  )s, useful for decoding outer joined rows.:{let decode :: DecodeRow< '[ "fst" ::: 'Null 'PGint2, "snd" ::: 'Null ('PGchar 1)] (Maybe (Int16, Char)), decode = runMaybeT $ (,) <$> #fst <*> #sndin runDecodeRow decode (SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* Nil):}Right (Just (1,'a'))squeal-postgresqlA * constraint gives a decoding to a Haskell  from the binary format of a PostgreSQL fixed-length array. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint lifts the  parser to a decoding of a (Symbol, NullityType) to a  , decoding s to (s. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint lifts the  parser to a decoding of a  NullityType to a  , decoding s to (s. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint gives a parser from the binary format of a PostgreSQL  into a Haskell .squeal-postgresql:set -XMultiParamTypeClasses -XGeneralizedNewtypeDeriving -XDerivingStrategies -XDerivingVia -XUndecidableInstancesimport GHC.Generics as GHC:{*newtype UserId = UserId { getId :: Int64 }! deriving newtype (IsPG, FromPG):}:{data Complex = Complex { real :: Double , imaginary :: Double } deriving stock GHC.Generic8 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)1 deriving (IsPG, FromPG) via Composite Complex:}:{,data Direction = North | South | East | West deriving stock GHC.Generic6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)2 deriving (IsPG, FromPG) via Enumerated Direction:}squeal-postgresql Converts a  type from postgresql-binary for use in the  method of .squeal-postgresql:set -XTypeFamilies:{data Complex = Complex { real :: Double , imaginary :: Double }instance IsPG Complex where# type PG Complex = 'PGcomposite '[ "re" ::: 'NotNull 'PGfloat8, "im" ::: 'NotNull 'PGfloat8]instance FromPG Complex where fromPG = rowValue $ do re <- #re im <- #im. return Complex {real = re, imaginary = im}:}squeal-postgresqlRun a .squeal-postgresql2Append two row decoders with a combining function.import GHC.Generics as GHC:{ &data L = L {fst :: Int16, snd :: Char}$ deriving stock (GHC.Generic, Show)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)'data R = R {thrd :: Bool, frth :: Bool}$ deriving stock (GHC.Generic, Show)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo) type Row = '[ "fst" ::: 'NotNull 'PGint2,! "snd" ::: 'NotNull ('PGchar 1), "thrd" ::: 'NotNull 'PGbool, "frth" ::: 'NotNull 'PGbool]:}:{ let decode :: DecodeRow Row (L,R)/ decode = appendRows (,) genericRow genericRow row4 = SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* SOP.K (Just "\NUL") :* SOP.K (Just "\NUL") :* Nilin runDecodeRow decode row4:}=Right (L {fst = 1, snd = 'a'},R {thrd = False, frth = False})squeal-postgresql:Cons a column and a row decoder with a combining function.:{let decode :: DecodeRow '["fst" ::: 'NotNull 'PGtext, "snd" ::: 'NotNull 'PGint2, "thrd" ::: 'NotNull ('PGchar 1)] (String, (Int16, Char))4 decode = consRow (,) #fst (consRow (,) #snd #thrd)in runDecodeRow decode (SOP.K (Just "hi") :* SOP.K (Just "\NUL\SOH") :* SOP.K (Just "a") :* Nil):}Right ("hi",(1,'a'))squeal-postgresqlSmart constructor for a .squeal-postgresql Positionally . More general than =, which matches records both positionally and by field name, 1 matches records _or_ tuples purely positionally.$import qualified GHC.Generics as GHC$import qualified Generics.SOP as SOP:{let decode :: DecodeRow '[ "foo" ::: 'NotNull 'PGint2, "bar" ::: 'NotNull 'PGtext] (Int16, String) decode = genericProductRowin runDecodeRow decode (SOP.K (Just "\NUL\STX") :* SOP.K (Just "two") :* Nil):}Right (2,"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-postgresqlfieldssqueal-postgresqlcombining functionsqueal-postgresql left decodersqueal-postgresql right decodersqueal-postgresqlcombining functionsqueal-postgresql alias of headsqueal-postgresql tail decodersqueal-postgresqllabelsresults(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred ")*@squeal-postgresqlA $ operation extracts values from the  of a .y4 operation. There is no need to define instances of . An instance of  implies an instance of . However, the constraint  does not imply the constraint .squeal-postgresql5Get a row corresponding to a given row number from a <, throwing an exception if the row number is out of bounds.squeal-postgresqlGet all rows from a .squeal-postgresql%Get the first row if possible from a .squeal-postgresql8Returns the number of rows (tuples) in the query result.squeal-postgresql;Returns the number of columns (fields) in the query result.squeal-postgresqlReturns the command status tag from the SQL command that generated the . Commonly this is just the name of the command, but it might include additional data such as the number of rows processed.squeal-postgresqlReturns the number of rows affected by the SQL command. This function returns  the number of rows affected by the SQL statement that generated the . This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement,or an EXECUTE of a prepared query that contains an INSERT, UPDATE, or DELETE statement. If the command that generated the PGresult was anything else,  returns .squeal-postgresql)Returns the result status of the command.squeal-postgresql Check if a 's status is either   or  otherwise  a 7.squeal-postgresqlReturns the error message most recently generated by an operation on the connection.squeal-postgresqlReturns the error code most recently generated by an operation on the connection. https://www.postgresql.org/docs/current/static/errcodes-appendix.htmlsqueal-postgresqls are generated by executing 7zs in a .y.They contain an underlying  and a .squeal-postgresql:Intended to be used for unfolding in streaming libraries, 8 takes a total number of rows (which can be found with  ) and a 2 and given a row number if it's too large returns >, otherwise returning the row along with the next row number.squeal-postgresqlLifts actions on results from LibPQ.squeal-postgresqltotal number of rowssqueal-postgresqlresultsqueal-postgresql row numberout-of-line parameters(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred")*1squeal-postgresqlParamTypeMismatchError reports a nicer error with more context when we successfully do a lookup but find a different field than we expected, or when we find ourself out of boundssqueal-postgresqlParamOutOfBoundsError reports a nicer error with more context when we try to do an out-of-bounds lookup successfully do a lookup but find a different field than we expected, or when we find ourself out of boundssqueal-postgresql HasParameter' is an implementation detail of ? allowing us to include the full parameter list in our errors.squeal-postgresqlA  constraint is used to indicate a value that is supplied externally to a SQL statement. 7{, 7| and 7} support specifying data values separately from the SQL command string, in which case 3s are used to refer to the out-of-line data values.squeal-postgresql takes a  using type application and a .printSQL (parameter @1 int4) ($1 :: int4)squeal-postgresql takes a 7 using type application and for basic types, infers a .$printSQL (param @1 @('Null 'PGint4)) ($1 :: int4)squeal-postgresqlparammath functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1squeal-postgresql:{let% expression :: Expr (null 'PGfloat4) expression = atan2_ (pi *: 2)in printSQL expression:}atan2(pi(), (2.0 :: float4))squeal-postgresql&integer division, truncates the result:{let expression :: Expression grp lat with db params from (null 'PGint2) expression = 5 `quot_` 2in printSQL expression:}((5 :: int2) / (2 :: int2))squeal-postgresqlremainder upon integer division:{let expression :: Expression grp lat with db params from (null 'PGint2) expression = 5 `rem_` 2in printSQL expression:}((5 :: int2) % (2 :: int2))squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGfloat4) expression = trunc piin printSQL expression:} trunc(pi())squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGfloat4) expression = round_ piin printSQL expression:} round(pi())squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGfloat4) expression = ceiling_ piin printSQL expression:} ceiling(pi())!logical expressions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1squeal-postgresqlA  is an , which can evaluate to  ,   or ~1. This is because SQL uses a three valued logic. squeal-postgresql printSQL trueTRUE squeal-postgresqlprintSQL falseFALSE squeal-postgresqlprintSQL $ not_ true (NOT TRUE) squeal-postgresqlprintSQL $ true .&& false(TRUE AND FALSE) squeal-postgresqlprintSQL $ true .|| false(TRUE OR FALSE) squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGint2)9 expression = caseWhenThenElse [(true, 1), (false, 2)] 3in printSQL expression:}CASE WHEN TRUE THEN (1 :: int2) WHEN FALSE THEN (2 :: int2) ELSE (3 :: int2) END squeal-postgresql:{let expression :: Expression grp lat with db params from (null 'PGint2)" expression = ifThenElse true 1 0in printSQL expression:}4CASE WHEN TRUE THEN (1 :: int2) ELSE (0 :: int2) END squeal-postgresqlwhens and thenssqueal-postgresqlelse squeal-postgresqlthensqueal-postgresqlelse   3 2 intermediate table expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;? squeal-postgresqlTo prevent the operation from  3 for other transactions to commit, use either the   or   option. squeal-postgresql%wait for other transactions to commit squeal-postgresql%reports an error, rather than waiting squeal-postgresql?any selected rows that cannot be immediately locked are skipped squeal-postgresqlRow-level locks, which are listed as below with the contexts in which they are used automatically by PostgreSQL. Note that a transaction can hold conflicting locks on the same row, even in different subtransactions; but other than that, two transactions can never hold conflicting locks on the same row. Row-level locks do not affect data querying; they block only writers and lockers to the same row. Row-level locks are released at transaction end or during savepoint rollback. squeal-postgresql   " causes the rows retrieved by the +l statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt 1n, 3, +l    , +l    , +l     or +l     of these rows will be blocked until the current transaction ends; conversely, +l     will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a 0 or 0 transaction, however, an error will be thrown if a row to be locked has changed since the transaction started.The    # lock mode is also acquired by any 3 a row, and also by an   that modifies the values on certain columns. Currently, the set of columns considered for the 1n case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future. squeal-postgresqlBehaves similarly to    , except that the lock acquired is weaker: this lock will not block +l     commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any 1n that does not acquire a     lock. squeal-postgresqlBehaves similarly to    $, except that the lock is weaker: +l     is blocked, but not +l    . A key-shared lock blocks other transactions from performing 3 or any 1n. that changes the key values, but not other  , and neither does it prevent +l    , +l    , or +l    . squeal-postgresqlIf specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the +l3 are simply read as usual. A locking clause with a  table list affects all tables used in the statement. If a locking clause is applied to a  or $, it affects all tables used in the  or ). However, these clauses do not apply to  queries referenced by the primary query. If you want row locking to occur within a  query, specify a   within the  query. squeal-postgresqlA  ; is used to eliminate groups that are not of interest. An    may only use   while a    must use  % whose conditions are combined with  . squeal-postgresqlA   indicates the  of a  . squeal-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  " that is optionally followed by a  ,  ,  ,  ,     and  . Trivial table expressions simply refer to a table on disk, a so-called base table, but more complex expressions can be used to modify or combine base tables in various ways. squeal-postgresqlA table reference that can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these. squeal-postgresql)optional search coditions, combined with  . After the processing of the   is done, each row of the derived virtual table is checked against the search condition. If the result of the condition is true, the row is kept in the output table, otherwise it is discarded. The search condition typically references at least one column of the table generated in the  ; this is not required, but otherwise the WHERE clause will be fairly useless. squeal-postgresqlThe   is used to group together those rows in a table that have the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups. squeal-postgresql"If a table has been grouped using  0, but only certain groups are of interest, the   can be used, much like a  ;, to eliminate groups from the result. Expressions in the   can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function). squeal-postgresqlThe  . is for optional sorting. When more than one  is specified, the later (right) values are used to sort rows that are equal according to the earlier (left) values. squeal-postgresqlThe   is combined with  to give a limit count if nonempty. If a limit count is given, no more than that many rows will be returned (but possibly fewer, if the query itself yields fewer rows). squeal-postgresqlThe   is combined with  to give an offset count if nonempty. The offset count says to skip that many rows before beginning to return rows. The rows are skipped before the limit count is applied. squeal-postgresql ) can be added to a table expression with  . squeal-postgresqlA   generates a   from a table reference that can be a table name, or a derived table such as a subquery, a JOIN construct, or complex combinations of these. A   may be transformed by  ,  ,  , ,   and   , using the  operator to match the left-to-right sequencing of their placement in SQL. squeal-postgresqlA   is an endomorphism of  (s which adds a search condition to the  . squeal-postgresqlA   is a transformation of  s which switches its  from  to . Use  groupBy Nil/ to perform a "grand total" aggregation query. squeal-postgresqlA   is an endomorphism of  (s which adds a search condition to the  . squeal-postgresqlA   is an endomorphism of  s which adds to the  . squeal-postgresqlAn   is an endomorphism of  s which adds to the  . squeal-postgresqlAdd a   to a   . Multiple  s can be written if it is necessary to specify different locking behavior for different tables. If the same table is mentioned (or implicitly affected) by more than one locking clause, then it is processed as if it was only specified by the strongest one. Similarly, a table is processed as   if that is specified in any of the clauses affecting it. Otherwise, it is processed as   if that is specified in any of the clauses affecting it. Further, a  / cannot be added to a grouped table expression. squeal-postgresql Render a  . squeal-postgresql Render a   squeal-postgresql lock strengthsqueal-postgresql table listsqueal-postgresql wait or not squeal-postgresqltable reference squeal-postgresqlfiltering condition squeal-postgresqlgrouped columns squeal-postgresqlhaving condition squeal-postgresqllimit parameter squeal-postgresqloffset parameter squeal-postgresqlrow-level lock% % ! Squeal joins(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/015;?* squeal-postgresqlA   is the right hand side of a  ,  ,  ,  ,   join of s. squeal-postgresqlleft & cross (Join right).. For every possible combination of rows from left and right (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in left followed by all columns in right. If the tables have n and m/ rows respectively, the joined table will have n * m rows. squeal-postgresqlleft & crossJoin right.. For every possible combination of rows from left and right (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in left followed by all columns in right. If the tables have n and m/ rows respectively, the joined table will have n * m rows. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresqlleft & inner (Join right) on&. The joined table is filtered by the on condition. squeal-postgresqlleft & innerJoin right on&. The joined table is filtered by the on condition. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql left & leftOuter (Join right) on;. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right7, a joined row is added with null values in columns of right. Thus, the joined table always has at least one row for each row in left. squeal-postgresqlleft & leftOuterJoin right on;. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right7, a joined row is added with null values in columns of right. Thus, the joined table always has at least one row for each row in left. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql!left & rightOuter (Join right) on;. First, an inner join is performed. Then, for each row in right that does not satisfy the on condition with any row in left7, a joined row is added with null values in columns of left. This is the converse of a left join: the result table will always have a row for each row in right. squeal-postgresqlleft & rightOuterJoin right on;. First, an inner join is performed. Then, for each row in right that does not satisfy the on condition with any row in left7, a joined row is added with null values in columns of left. This is the converse of a left join: the result table will always have a row for each row in right. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql left & fullOuter (Join right) on;. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right7, a joined row is added with null values in columns of right. Also, for each row of right: that does not satisfy the join condition with any row in left2, a joined row with null values in the columns of left is added. squeal-postgresqlleft & fullOuterJoin right on;. First, an inner join is performed. Then, for each row in left that does not satisfy the on condition with any row in right7, a joined row is added with null values in columns of right. Also, for each row of right: that does not satisfy the join condition with any row in left2, a joined row with null values in the columns of left is added. squeal-postgresqlLike   with a 8 but allowed to reference columns provided by preceding  items. squeal-postgresql A standard . It is not allowed to reference columns provided by preceding  items. squeal-postgresqlSubqueries can be preceded by  . This allows them to reference columns provided by preceding  items. squeal-postgresql+Set returning functions can be preceded by  . This allows them to reference columns provided by preceding  items.squeal-postgresqlargument squeal-postgresql;Set returning multi-argument functions can be preceded by  . This allows them to reference columns provided by preceding  items.squeal-postgresql arguments squeal-postgresqlrightsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlrightsqueal-postgresqlON conditionsqueal-postgresqlleft squeal-postgresqlright subquerysqueal-postgresqlON conditionsqueal-postgresqlleft  "subquery expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1 squeal-postgresqlThe argument of   is an arbitrary subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of   is  1; if the subquery returns no rows, the result of   is  .The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion. squeal-postgresqlThe right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given 3, which must yield a Boolean result. The result of   is   if all rows yield true (including the case where the subquery returns no rows). The result is   if any   result is found. The result is #0 if no comparison with a subquery row returns  &, and at least one comparison returns #.7printSQL $ subAll true (.==) (values_ (true `as` #foo))9(TRUE = ALL (SELECT * FROM (VALUES (TRUE)) AS t ("foo"))) squeal-postgresqlThe right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given 3, which must yield a Boolean result. The result of   is   if any  # result is obtained. The result is   if no true result is found (including the case where the subquery returns no rows).;printSQL $ subAny "foo" like (values_ ("foobar" `as` #foo))((E'foo' :: text) LIKE ANY (SELECT * FROM (VALUES ((E'foobar' :: text))) AS t ("foo"))) squeal-postgresqlThe result is   if the left-hand expression's result is equal to any of the right-hand expressions.*printSQL $ true `in_` [true, false, null_]TRUE IN (TRUE, FALSE, NULL) squeal-postgresqlThe result is   if the left-hand expression's result is not equal to any of the right-hand expressions.&printSQL $ true `notIn` [false, null_]TRUE NOT IN (FALSE, NULL) squeal-postgresqlsubquery squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquery squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquery squeal-postgresql expression squeal-postgresql expression  #null expressions and handlers(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred "1 squeal-postgresql(Make the return type of the type family  if both arguments are, or  otherwise. squeal-postgresql analagous to printSQL null_NULL squeal-postgresql analagous to printSQL $ just_ trueTRUE squeal-postgresql analagous to  squeal-postgresql Analagous to  inverse to  , useful when you know an  is ., because, for instance, you've filtered out NULL values in a column. squeal-postgresqlSome expressions are null polymorphic which may raise inference issues. Use   to fix their nullity as . squeal-postgresql+return the leftmost value which is not NULL'printSQL $ coalesce [null_, true] falseCOALESCE(NULL, TRUE, FALSE) squeal-postgresql analagous to  using COALESCEprintSQL $ fromNull true null_COALESCE(NULL, TRUE) squeal-postgresqlprintSQL $ null_ & isNull NULL IS NULL squeal-postgresqlprintSQL $ null_ & isNotNullNULL IS NOT NULL squeal-postgresql analagous to  using IS NULL$printSQL $ matchNull true not_ null_4CASE WHEN NULL IS NULL THEN TRUE ELSE (NOT NULL) END squeal-postgresqlright inverse to  ", if its arguments are equal then   gives NULL.:set -XTypeApplications%printSQL (nullIf (false *: param @1))NULLIF(FALSE, ($1 :: bool)) squeal-postgresqlnull polymorphic squeal-postgresqlwhat to convert NULL to squeal-postgresqlwhat to convert NULL tosqueal-postgresqlfunction to perform when NULL is absent $&json and jsonb functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred")*01/ squeal-postgresqlBuild rows from Json types. squeal-postgresqlBuild rows from Json types. squeal-postgresqlBuilds a possibly-heterogeneously-typed JSON object out of a variadic argument list. The elements of the argument list must alternate between text and values. squeal-postgresql8Get JSON value (object field or array element) at a key. squeal-postgresqlGet JSON value (object field or array element) at a key, as text. squeal-postgresql#Get JSON value at a specified path. squeal-postgresql+Get JSON value at a specified path as text. squeal-postgresql?Does the string exist as a top-level key within the JSON value? squeal-postgresql6Do any of these array strings exist as top-level keys? squeal-postgresql6Do all of these array strings exist as top-level keys? squeal-postgresqlDelete a key or keys from a JSON object, or remove an array element.If the right operand is text : Delete key / value pair or string element from left operand. Key / value pairs are matched based on their key value, text[] : Delete multiple key / value pairs or string elements from left operand. Key / value pairs are matched based on their key value, integer : Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array. squeal-postgresqlDelete the field or element with specified path (for JSON arrays, negative integers count from the end) squeal-postgresqlReturns the value as json. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json value. squeal-postgresqlReturns the value as jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid jsonb value. squeal-postgresqlReturns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays. squeal-postgresql!Returns the row as a JSON object. squeal-postgresqlBuilds a possibly-heterogeneously-typed JSON array out of a variadic argument list. squeal-postgresqlBuilds a possibly-heterogeneously-typed (binary) JSON array out of a variadic argument list. squeal-postgresqlBuilds a JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair. squeal-postgresqlBuilds a binary JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair. squeal-postgresqlThis is an alternate form of   that takes two arrays; one for keys and one for values, that are zipped pairwise to create a JSON object. squeal-postgresqlThis is an alternate form of   that takes two arrays; one for keys and one for values, that are zipped pairwise to create a binary JSON object. squeal-postgresql;Returns the number of elements in the outermost JSON array. squeal-postgresqlReturns the number of elements in the outermost binary JSON array. squeal-postgresqlReturns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null. squeal-postgresqlReturns the type of the outermost binary JSON value as a text string. Possible types are object, array, string, number, boolean, and null. squeal-postgresqlReturns its argument with all object fields that have null values omitted. Other null values are untouched. squeal-postgresqlReturns its argument with all object fields that have null values omitted. Other null values are untouched. squeal-postgresql . jsonbSet target path new_value create_missing?Returns target with the section designated by path replaced by  new_value , or with  new_value added if create_missing is  and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays. squeal-postgresql / jsonbInsert target path new_value insert_afterReturns target with  new_value inserted. If target section designated by path is in a JSONB array,  new_value- will be inserted before target or after if  insert_after is =. If target section designated by path is in JSONB object,  new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays. squeal-postgresql+Returns its argument as indented JSON text. squeal-postgresqlExpands the outermost JSON object into a set of key/value pairs.printSQL (select Star (from (jsonEach (inline (Json (object ["a" .= "foo"]))))))0SELECT * FROM json_each(('{"a":"foo"}' :: json)) squeal-postgresqlExpands the outermost binary JSON object into a set of key/value pairs.printSQL (select Star (from (jsonbEach (inline (Jsonb (object ["a" .= "foo"]))))))2SELECT * FROM jsonb_each(('{"a":"foo"}' :: jsonb)) squeal-postgresqlExpands the outermost JSON object into a set of key/value pairs.printSQL (select Star (from (jsonEachText (inline (Json (object ["a" .= "foo"]))))))5SELECT * FROM json_each_text(('{"a":"foo"}' :: json)) squeal-postgresql.Returns a set of text values from a JSON arrayprintSQL (select Star (from (jsonArrayElementsText (inline (Json (toJSON ["monkey", "pony", "bear"] ))))))SELECT * FROM json_array_elements_text(('["monkey","pony","bear"]' :: json)) squeal-postgresqlExpands the outermost binary JSON object into a set of key/value pairs.printSQL (select Star (from (jsonbEachText (inline (Jsonb (object ["a" .= "foo"]))))))7SELECT * FROM jsonb_each_text(('{"a":"foo"}' :: jsonb)) squeal-postgresql1Returns set of keys in the outermost JSON object.printSQL (jsonObjectKeys (inline (Json (object ["a" .= "foo"])))))json_object_keys(('{"a":"foo"}' :: json)) squeal-postgresql1Returns set of keys in the outermost JSON object.printSQL (jsonbObjectKeys (inline (Jsonb (object ["a" .= "foo"]))))+jsonb_object_keys(('{"a":"foo"}' :: jsonb)) squeal-postgresql5Returns a set of text values from a binary JSON arrayprintSQL (select Star (from (jsonbArrayElementsText (inline (Jsonb (toJSON ["red", "green", "cyan"] ))))))SELECT * FROM jsonb_array_elements_text(('["red","green","cyan"]' :: jsonb)) squeal-postgresqlExpands the JSON expression to a row whose columns match the record type defined by the given table. squeal-postgresqlExpands 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-postgresqlBuilds an arbitrary set of records from a JSON array of objects. squeal-postgresqlBuilds an arbitrary set of records from a binary JSON array of objects. squeal-postgresql json typesqueal-postgresqlrow type squeal-postgresqlrow typesqueal-postgresql json type1 1 8 8 8 8 9 9 9 6 6%composite functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1 squeal-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 ]:}let 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]:}let 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 values squeal-postgresqlintermediate table squeal-postgresqlrow typesqueal-postgresql field name  &"comparison functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred !"01)i squeal-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 FALSE squeal-postgresql+printSQL $ true `notBetween` (null_, false)TRUE NOT BETWEEN NULL AND FALSE squeal-postgresql,between, after sorting the comparison values1printSQL $ true `betweenSymmetric` (null_, false)%TRUE BETWEEN SYMMETRIC NULL AND FALSE squeal-postgresql0not between, after sorting the comparison values4printSQL $ true `notBetweenSymmetric` (null_, false))TRUE NOT BETWEEN SYMMETRIC NULL AND FALSE squeal-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-postgresqlbounds   4 4 4 4 4 4'array functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1; 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.printSQL $ array2 ((null_ :* false *: true) *: (false :* null_ *: true))/ARRAY[[NULL, FALSE, TRUE], [FALSE, NULL, TRUE]]: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.printSQL $ 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   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   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 ). 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"])((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   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   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 ). 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"])((E'hi' :: text) LIKE ANY (ARRAY[(E'bi' :: text), (E'hi' :: text)])) squeal-postgresqlarray elements squeal-postgresqlarray elements squeal-postgresqlmatrix elements squeal-postgresqlindex squeal-postgresql vector index squeal-postgresql matrix index squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlarray squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlarray (inline expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"1? squeal-postgresqlLifts   to a column entry squeal-postgresql'Haskell record field as a inline column squeal-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-postgresqlrecord squeal-postgresqlrecord )!aggregate functions and arguments(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred"015;d0 squeal-postgresqlA type family that calculates   type of a . squeal-postgresqlA type family that calculates    of a given argument . squeal-postgresqlPermits filtering *s and  s squeal-postgresqlIf  8 is specified, then only the input rows for which the  evaluates to true are fed to the aggregate function; other rows are discarded. squeal-postgresql s are used for the input of   s. squeal-postgresql squeal-postgresql squeal-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:{let expression :: Expression ('Grouped bys) '[] with db params from ('NotNull 'PGint8) expression = countStarin printSQL expression:}count(*) squeal-postgresql:{let 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:{let 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 array squeal-postgresql!aggregates values as a JSON array squeal-postgresql=the bitwise AND of all non-null input values, or null if none:{let 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 '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) Row 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) Row squeal-postgresqlA   is a statement which may modify data in the database, but does not alter its schemas. Examples are 2ms, 1ns and 3s. 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 f`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 = insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))in printSQL manp:}INSERT 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)]:{let manp :: Manipulation with (Public Schema) '[ 'NotNull 'PGint4] '[] manp = insertInto_ #tab $ Values_7 (Default `as` #col1 :* Set (param @1) `as` #col2)in printSQL manp:}INSERT 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)]:{data Row = Row { col1 :: Optional SOP.I ('Def :=> Int32), col2 :: Int32 } deriving stock (GHC.Generic)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):}:{let 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):}INSERT INTO "tab" AS "tab" ("col1", "col2") VALUES (DEFAULT, (2 :: int4)), ((3 :: int4), (4 :: int4))returning insert::{let manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4] manp = insertInto #tab (Values_ (Set 2 `as` #col1 :* Set 3 `as` #col2))) OnConflictDoRaise (Returning #col1)in printSQL manp:}INSERT INTO "tab" AS "tab" ("col1", "col2") VALUES ((2 :: int4), (3 :: int4)) RETURNING "col1" AS "col1"upsert:type 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 #customers (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) '[] '[] 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:}UPDATE "tab" AS "tab" SET "col1" = (2 :: int4) WHERE ("col1" <> "col2")delete::{let manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] 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 = 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:type ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]:{let manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[] manp = with (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del) (insertInto_ #products_deleted (Subquery (select Star (from (common #del)))))in printSQL manp:}WITH "del" AS (DELETE FROM "products" AS "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" AS "products_deleted" SELECT * FROM "del" AS "del" squeal-postgresql  a  squeal-postgresql Convert a  into a  . squeal-postgresql what to use squeal-postgresql row of values squeal-postgresql to embed as a  - statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred )*189:;9 squeal-postgresql. and . create a   statement. A   statement is a server-side object that can be used to optimize performance. When . or . is executed, the specified  $ is parsed, analyzed, and rewritten. When the  % command is subsequently issued, the   statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.  statements only last for the duration of the current database session.  1 statements can be manually cleaned up using the   command. squeal-postgresqlexecute a prepared statement squeal-postgresql&manually clean up a prepared statement squeal-postgresqlA   consists of a ] or a   that can be run in a .y. squeal-postgresql-Constructor for a data manipulation language  squeal-postgresql,Constructor for a structured query language  squeal-postgresql2Smart constructor for a structured query language  squeal-postgresql3Smart constructor for a data manipulation language   squeal-postgresqlencoding of parameterssqueal-postgresqldecoding of returned rowssqueal-postgresql2m, 1n, or 3, ... squeal-postgresqlencoding of parameterssqueal-postgresqldecoding of returned rowssqueal-postgresql+l, , ... squeal-postgresql+l, , ... squeal-postgresql2m, 1n, or 3, ... . session monad(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred)*018< squeal-postgresql  is an mtl style constraint, similar to  , for using @A 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` #getSumin 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` #fromOnlyin 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"in withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ execute_ silence:} squeal-postgresql  creates a   statement. When   is executed, the specified  $ is parsed, analyzed, and rewritten.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` #getSumin withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do prepared <- prepare sumOf( result <- runPrepared prepared (2,2) deallocate prepared firstRow result:}Just (Sum {getSum = 4}) squeal-postgresql  creates a   statement. When   is executed, the specified  $ is parsed, analyzed, and rewritten.)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 session :: PQ DB DB IO () session = do" prepared <- prepare_ insertion runPrepared prepared (2,2) deallocate prepared( teardown :: Definition DB (Public '[]) teardown = dropTable #tabin withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ define setup & pqThen session & pqThen (define teardown):} squeal-postgresql  a statement=transforming its inputs and outputs with an optic, run the   statementdeallocate the   statement:type preparedFor traverse'preparedFor traverse'& :: (MonadPQ db pq, Traversable f) =>1 Statement db a b -> f a -> pq (f (Result b)) squeal-postgresql  runs a   on a  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` #getSumin withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do8 results <- executePrepared sumOf [(2,2),(3,3),(4,4)] traverse firstRow results:}[Just (Sum {getSum = 4}),Just (Sum {getSum = 6}),Just (Sum {getSum = 8})] squeal-postgresql  runs a returning-free   on 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):{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 ( do result <- manipulateParams insertAdd (2::Int32,2::Int32)/ Just (Only answer) <- firstRow result* liftIO $ print (answer :: Int32) ) & pqThen (define teardown):}4 squeal-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):}4 squeal-postgresql  runs a  1, for a returning-free, parameter-free statement.:{let# silence :: Manipulation_ db () () silence = UnsafeManipulation "Set client_min_messages TO WARNING"in 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` #getSumin 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):}4 squeal-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` #getSumin withConnection "host=localhost port=5432 dbname=exampledb user=postgres password=postgres" $ do! result <- runQuery twoPlusTwo& Just (Sum four) <- firstRow result" liftIO $ print (four :: Int32):}4 squeal-postgresql  runs a   on 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 ( do results <- traversePrepared insertAdd [(2::Int32,2::Int32),(3,3),(4,4)]. answers <- traverse firstRow results 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 ( do results <- forPrepared [(2::Int32,2::Int32),(3,3),(4,4)] insertAdd. answers <- traverse firstRow results liftIO $ print [answer :: Int32 | Just (Only answer) <- answers] ) & pqThen (define teardown):}[4,6,8] squeal-postgresql  runs a returning-free   on 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 setup & 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 setup & pqThen (forPrepared_ [(2::Int32,2::Int32),(3,3),(4,4)] insertion) & pqThen (define teardown):} squeal-postgresqlquery or manipulationsqueal-postgresql parameters squeal-postgresqlquery or manipulationsqueal-postgresql parameters squeal-postgresqlquery or manipulation squeal-postgresqlquery or manipulation squeal-postgresqlquery or manipulation squeal-postgresqlquery or manipulation squeal-postgresqltransform the input and outputsqueal-postgresqlquery or manipulation squeal-postgresqlquery or manipulationsqueal-postgresqllist of parameters squeal-postgresqlquery or manipulationsqueal-postgresqllist of parameters squeal-postgresql2m, 1n, or 3 , and friends squeal-postgresql2, 1, or 3 , and friends squeal-postgresql+l and friends squeal-postgresql+l and friends squeal-postgresql2m, 1n, or 3 , and friends squeal-postgresql2m, 1n, or 3 , and friends squeal-postgresql2, 1, or 3 , and friends squeal-postgresql2, 1, or 3 , and friends  /#unsafe transaction control language(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!"01` squeal-postgresqlThe   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   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-postgresqlThe 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 FROM 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 EXECUTE 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-postgresqlThe SQL standard defines four levels of transaction isolation. The most strict is  , 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 read: 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-postgresqlDirty read is not possible. Nonrepeatable read is possible. Phantom read is possible. Serialization anomaly is possible. squeal-postgresqlDirty 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 or deadlock detection, then   and restart the transaction,&if it raises any other exception then   and rethrow the exception, otherwise   and  the result. squeal-postgresql  in  . 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 , used in a transaction block, allows a form of nested transactions, creating a savepoint, then running a transaction, rolling back to the savepoint if it returned , then releasing the savepoint and returning transaction's result.Make sure to run   in a transaction block, not directly or you will provoke a SQL exception. squeal-postgresql  with a    ,     and    . squeal-postgresql  with a    ,     and    8, appropriate for short-lived queries or manipulations. 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 transaction squeal-postgresqlrun inside a transaction squeal-postgresqlrun inside a transaction squeal-postgresqlrun inside a transaction squeal-postgresql#run inside an ephemeral transaction squeal-postgresql#run inside an ephemeral transaction squeal-postgresqlsavepoint name  0transaction control language(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred ; squeal-postgresqlA type of "safe"  s, do-blocks that permit only database operations, pure functions, and synchronous exception handling forbidding arbitrary  operations.To permit arbitrary ,import qualified Squeal.PostgreSQL.Session.Transaction.Unsafe as Unsafe Then use the Unsafe' qualified form of the functions below.A safe   can be run in two ways,1) it can be run directly in 1 because as a universally quantified type, Transaction db x. permits interpretation in "subtypes" like .(MonadPQ db m, MonadIO m, MonadCatch m) => m x or  PQ db db IO x22) it can be run in a transaction block, using  ,  , or  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 or deadloack detection, then   and restart the transaction,&if it raises any other exception then   and rethrow the exception, otherwise   and  the result. squeal-postgresql  in  . squeal-postgresqlRun a computation  ; Like   but always  , useful in testing. squeal-postgresqlRun a computation   in  . squeal-postgresql , used in a transaction block, allows a form of nested transactions, creating a savepoint, then running a transaction, rolling back to the savepoint if it returned , then releasing the savepoint and returning transaction's result.Make sure to run   in a transaction block, not directly or you will provoke a SQL exception. squeal-postgresqlrun inside a transaction squeal-postgresqlrun inside a transaction squeal-postgresqlrun inside a transaction squeal-postgresqlrun inside a transaction squeal-postgresql#run inside an ephemeral transaction squeal-postgresql#run inside an ephemeral transaction squeal-postgresqlsavepoint name  1update statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*01;?# squeal-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]type 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:}UPDATE "tab" AS "tab" SET "col1" = (2 :: int4) WHERE ("col1" <> "col2") squeal-postgresqltable to updatesqueal-postgresql9update expressions, modified values to replace old valuessqueal-postgresqlFROM 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 return squeal-postgresqltable to updatesqueal-postgresql%modified values to replace old valuessqueal-postgresql0condition under which to perform update on a row  2insert statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*01;?! squeal-postgresqlA  5 specifies the constraint violation that triggers a  . squeal-postgresql  specifies an alternative   action. It can be either  , or a   clause specifying the exact details of the update action to be performed in case of a conflict. The  and WHERE s in     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.     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.     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  must match the tables'. squeal-postgresqlWhen 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.type 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 #customers (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 = insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))in printSQL manp:}INSERT 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 s squeal-postgresqlconflict targetsqueal-postgresqlconflict action squeal-postgresql row of valuessqueal-postgresqladditional rows of values squeal-postgresql row of valuessqueal-postgresqlfrom a table expression squeal-postgresqlsubquery to insert squeal-postgresql row of values squeal-postgresqltablesqueal-postgresqlwhat to insertsqueal-postgresqlwhat to do in case of conflictsqueal-postgresqlwhat to return squeal-postgresqltablesqueal-postgresqlwhat to insert squeal-postgresqlrecord squeal-postgresqlrecordsqueal-postgresqlmore  3delete statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*01;?'B squeal-postgresqlDelete rows from a table.type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]type Schema = '["tab1" ::: 'Table ('[] :=> Columns), "tab2" ::: 'Table ('[] :=> Columns)]:{let manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4] manp = deleteFrom #tab1 (Using (table #tab2)) (#tab1 ! #col1 .== #tab2 ! #col2) (Returning (#tab1 & DotStar))in printSQL manp:}DELETE 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)]:{let 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 return squeal-postgresqltable to delete fromsqueal-postgresql%condition under which to delete a row  4call statements(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*01;?+ 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.type 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 arguments squeal-postgresqlprocedure to callsqueal-postgresql arguments squeal-postgresqlprocedure to callsqueal-postgresql arguments squeal-postgresqlprocedure to callsqueal-postgresql arguments  5constraint expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/01;=?I squeal-postgresqlWhen 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   which is invoked when a referenced column is changed (updated). squeal-postgresql = indicates what to do with rows that reference a deleted row. squeal-postgresqlA 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. s give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition. squeal-postgresqlA   constraint is the most generic  type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.:{type Schema = '[ "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 definitionCREATE TABLE "tab" ("a" int NOT NULL, "b" int NOT NULL, CONSTRAINT "inequality" CHECK (("a" > "b"))); squeal-postgresqlA   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 definitionCREATE 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 definitionCREATE 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"] , "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 #id (OnDelete Cascade) (OnUpdate Cascade) `as` #fk_user_id )in printSQL setup:}CREATE 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 #id (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 subcolumns squeal-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 experimental Safe-Inferred!")*/01;=?L squeal-postgresqlA   is a statement that changes the schemas of the database, like a :W, :U, or :S command.  s may be composed using the  operator. squeal-postgresqlA   without input or output can be run as a statement along with other  s, by embedding it using  . squeal-postgresqlno input or output   6indexed session monad(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred01<Qq squeal-postgresql  is a class for indexed monad transformers that support running  s using  # which acts functorially in effect. define id = return () define (statement1 >>> statement2) = define statement1 & pqThen (define statement2) squeal-postgresql & reshuffles the type parameters of an  , exposing its  instance. squeal-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  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. squeal-postgresqlindexed analog of  squeal-postgresqlindexed analog of  squeal-postgresqlindexed analog of  squeal-postgresqlindexed analog of flipped  squeal-postgresqlindexed analog of  squeal-postgresqlRun a pure SQL   functorially in effect indexedDefine id = id indexedDefine (def1 >>> def2) = indexedDefine def1 >>> indexedDefine def2 7sessions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!"012<TVsqueal-postgresqlA snapshot of the state of a  / computation, used in MonadBaseControl Instance squeal-postgresqlWe keep track of the schema via an Atkey indexed state monad transformer,  . squeal-postgresqlRun a   and keep the result and the . squeal-postgresql Execute a  % and discard the result but keep the . squeal-postgresql Evaluate a   and discard the  but keep the result. squeal-postgresqlDo  and  before and after a computation.  connection pools(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!0128_g squeal-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   when you're done with the pool so that the connections are freed up as soon as possible. squeal-postgresql%Temporarily take a connection from a #, 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. squeal-postgresqlDestroy 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   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. squeal-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-postgresqlThe 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. squeal-postgresqlpoolsqueal-postgresqlsession squeal-postgresqlpool# # 8create and drop views(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/01;=?l squeal-postgresqlCreate a view.type 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)]] '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc" ::: 'View BC]] definition =; createView #bc (select_ (#b :* #c) (from (table #abc)))in printSQL definition:}CREATE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc"; squeal-postgresqlCreate or replace a view.type 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)]] '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc" ::: 'View BC]] definition = createOrReplaceView #bc (select_ (#b :* #c) (from (table #abc)))in printSQL definition:}CREATE 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]) , "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]) , "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 '[] ] ]:{ 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.type DB0 = '[ "sch0" ::: '[ "vw" ::: 'View '[] ], "sch1" ::: '[] ]type 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  9create and drop types(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/01;=? squeal-postgresql'Enumerated types are created using the   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');squeal-postgresqlEnumerated 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:{let createSchwarma :: Definition (Public '[]) '["public" ::: '["schwarma" ::: 'Typedef (PG (Enumerated Schwarma))]]9 createSchwarma = createTypeEnumFrom @Schwarma #schwarmain printSQL createSchwarma:};CREATE TYPE "schwarma" AS ENUM ('Beef', 'Lamb', 'Chicken');squeal-postgresql 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 ]:}:{let 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);squeal-postgresqlComposite types can also be generated from a Haskell type, for exampledata Complex = Complex {real :: Double, imaginary :: Double} deriving GHC.Genericinstance SOP.Generic Complex$instance SOP.HasDatatypeInfo Complextype 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);squeal-postgresql 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  constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.:{let createPositive :: Definition (Public '[]) (Public '["positive" ::: 'Typedef 'PGfloat4])< createPositive = createDomain #positive real (#value .> 0)in printSQL createPositive:}CREATE DOMAIN "positive" AS real CHECK (("value" > (0.0 :: float4)));squeal-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.Range 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.:{let createSmallIntRange :: Definition (Public '[]) (Public '["int2range" ::: 'Typedef ('PGrange 'PGint2)])7 createSmallIntRange = createTypeRange #int2range int2in printSQL createSmallIntRange:}2CREATE TYPE "int2range" AS RANGE (subtype = int2);squeal-postgresql Drop a type.:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo SchwarmaprintSQL (dropType #schwarma :: Definition '["public" ::: '["schwarma" ::: 'Typedef (PG (Enumerated Schwarma))]] (Public '[]))DROP TYPE "schwarma";squeal-postgresqlDrop a type if it exists.squeal-postgresql, changes the name of a type from the schema.;type DB = '[ "public" ::: '[ "foo" ::: 'Typedef 'PGbool ] ]:{ let def :: Definition DB '["public" ::: '["bar" ::: 'Typedef 'PGbool ] ]$ def = alterTypeRename #foo #bar in printSQL def:}!ALTER TYPE "foo" RENAME TO "bar";squeal-postgresql-This form moves the type into another schema.type DB0 = '[ "sch0" ::: '[ "ty" ::: 'Typedef 'PGfloat8 ], "sch1" ::: '[] ]type 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"; squeal-postgresql(name of the user defined enumerated typesqueal-postgresqllabels of the enumerated typesqueal-postgresql(name of the user defined enumerated typesqueal-postgresql'name of the user defined composite typesqueal-postgresql&list of attribute names and data typessqueal-postgresql'name of the user defined composite typesqueal-postgresql domain aliassqueal-postgresqlunderlying typesqueal-postgresqlconstraint on typesqueal-postgresql range aliassqueal-postgresqlunderlying typesqueal-postgresqlname of the user defined typesqueal-postgresqlname of the user defined typesqueal-postgresqltype to renamesqueal-postgresqlwhat to rename itsqueal-postgresql type to movesqueal-postgresqlwhere to move it  :create, drop and alter tables(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/01;=?"squeal-postgresqlAn 8 describes the alteration to perform on a single column.squeal-postgresqlAn  is either NULL or has DEFAULT.squeal-postgresql adds a new column, initially filled with whatever default value is given or with NULL.:{ let definition :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint40 , "col2" ::: 'Def :=> 'Null 'PGtext ])]] definition = alterTable #tab (addColumn #col2 (text & nullable & default_ "foo"))in printSQL definition:}ALTER TABLE "tab" ADD COLUMN "col2" text NULL DEFAULT (E'foo' :: text);:{ let definition :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint42 , "col2" ::: 'NoDef :=> 'Null 'PGtext ])]] definition = alterTable #tab (addColumn #col2 (text & nullable))in printSQL definition:}.ALTER TABLE "tab" ADD COLUMN "col2" text NULL;squeal-postgresqlAn  describes the alteration to perform on the columns of a table.squeal-postgresql adds a table to the schema.:set -XOverloadedLabels:{type Table = '[] :=>% '[ "a" ::: 'NoDef :=> 'Null 'PGint4) , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]:}:{let 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);squeal-postgresql 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.  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);squeal-postgresql! removes a table from the schema.:{let definition :: Definition '["public" ::: '["muh_table" ::: 'Table t]] (Public '[])# definition = dropTable #muh_table:}printSQL definitionDROP TABLE "muh_table";squeal-postgresqlDrop a table if it exists.squeal-postgresql3 changes the definition of a table from the schema.squeal-postgresql3 changes the definition of a table from the schema.squeal-postgresql- changes the name of a table from the schema.type Schemas = '[ "public" ::: '[ "foo" ::: 'Table ('[] :=> '[]) ] ]:{ let migration :: Definition Schemas '["public" ::: '["bar" ::: 'Table ('[] :=> '[]) ] ]+ migration = alterTableRename #foo #bar in printSQL migration:}"ALTER TABLE "foo" RENAME TO "bar";squeal-postgresql: changes the name of a table from the schema if it exists.type 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.type DB0 = '[ "sch0" ::: '[ "tab" ::: 'Table ('[] :=> '[]) ], "sch1" ::: '[] ]type 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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]] '["public" ::: '["tab" ::: 'Table ('["positive" ::: 'Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]] definition = alterTable #tab (addConstraint #positive (check #col (#col .> 0)))in printSQL definition:}ALTER TABLE "tab" ADD CONSTRAINT "positive" CHECK (("col" > (0 :: int4)));squeal-postgresqlA  drops a table constraint.:{let definition :: Definition '["public" ::: '["tab" ::: 'Table ('["positive" ::: Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]] '["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 ])]] '["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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["foo" ::: 'NoDef :=> 'Null 'PGint4])]] '["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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]] '["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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]] '["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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]] '["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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]] '["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 :: Definition '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]] '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGnumeric])]] definition = alterTable #tab (alterColumn #col (alterType (numeric & notNullable)))in printSQL definition:};ALTER TABLE "tab" ALTER COLUMN "col" TYPE numeric NOT NULL;squeal-postgresql column to addsqueal-postgresqltype of the new columnsqueal-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 tablesqueal-postgresqlthe name of the table to addsqueal-postgresql%the names and datatype of each columnsqueal-postgresql(constraints that must hold for the tablesqueal-postgresqltable to removesqueal-postgresqltable to removesqueal-postgresqltable to altersqueal-postgresqlalteration to performsqueal-postgresqltable to altersqueal-postgresqlalteration to performsqueal-postgresqltable to renamesqueal-postgresqlwhat to rename itsqueal-postgresqltable to renamesqueal-postgresqlwhat to rename itsqueal-postgresql table to movesqueal-postgresqlwhere to move itsqueal-postgresqlconstraint to addsqueal-postgresqlconstraint to dropsqueal-postgresqlcolumn to removesqueal-postgresqlcolumn to renamesqueal-postgresqlwhat to rename the columnsqueal-postgresqlcolumn to altersqueal-postgresqlalteration to performsqueal-postgresqldefault value to set migrations(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred")*01;bsqueal-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 experimental Safe-Inferred!")*/01;=?tsqueal-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.:{let 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-postgresqlDrop a schema. Automatically drop objects (tables, functions, etc.) that are contained in the schema.:{let definition :: Definition '["muh_schema" ::: schema, "public" ::: public] '["public" ::: public], definition = dropSchemaCascade #muh_schema:}printSQL definition!DROP SCHEMA "muh_schema" CASCADE;squeal-postgresqlDrop 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 experimental Safe-Inferred!")*/01;=? squeal-postgresql Body of a user defined proceduresqueal-postgresqlCreate a procedure.,type Proc = 'Procedure '[ 'NotNull 'PGint4 ]type Thing = 'Table ('[] :=> '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4 ]):{let 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:}CREATE PROCEDURE "proc" (int4) language sql as $$ DELETE FROM "things" AS "things" WHERE ("id" = ($1 :: int4)); $$;squeal-postgresqlCreate 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 ]type Thing = 'Table ('[] :=> '[ "id" ::: 'NoDef :=> 'NotNull 'PGint4 ]):{let 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]:{let 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 experimental Safe-Inferred!")*/01;=?~ 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 = createTable #tab (nullable int `as` #a :* nullable real `as` #b) Nil >>> createIndex #ix #tab btree [#a & AscNullsFirst, #b & AscNullsLast]in printSQL setup:}1CREATE TABLE "tab" ("a" int NULL, "b" real NULL);CREATE 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-postgresqlB-trees can handle equality and range queries on data that can be sorted into some ordering.squeal-postgresql9Hash indexes can only handle simple equality comparisons.squeal-postgresqlGiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented.squeal-postgresqlSP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches.squeal-postgresqlGIN indexes are @inverted indexes@ which are appropriate for data values that contain multiple component values, such as arrays.squeal-postgresqlBRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table.squeal-postgresqlDrop an index.printSQL (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 columnssqueal-postgresql index aliassqueal-postgresql index alias  >create and drop functions(c) Eitan Chatav, 2019eitan@morphism.tech experimental Safe-Inferred!")*/01;=?Q squeal-postgresqlBody of a user defined functionsqueal-postgresqlCreate a function.type 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-postgresqlCreate or replace a function. It is not possible to change the name or argument types or return type of a function this way.type Fn = 'Function ( '[ 'Null 'PGint4, 'Null 'PGint4] :=> 'Returns ( 'Null 'PGint4)):{ let definition :: Definition (Public '["fn" ::: Fn]) (Public '["fn" ::: Fn]) definition = createOrReplaceFunction #fn (int4 *: int4) int4 $ 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.type Tab = 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])type Fn = 'Function ('[ 'Null 'PGint4, 'Null 'PGint4] :=> 'ReturnsTable '["ret" ::: 'Null 'PGint4]):{let definition :: Definition (Public '["tab" ::: Tab]) (Public '["tab" ::: Tab, "fn" ::: Fn]) 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.type Tab = 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])type Fn = 'Function ('[ 'Null 'PGint4, 'Null 'PGint4] :=> 'ReturnsTable '["ret" ::: 'Null 'PGint4]):{let definition :: Definition (Public '["tab" ::: Tab, "fn" ::: Fn]) (Public '["tab" ::: Tab, "fn" ::: Fn]) 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.type 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.type 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 experimental Safe-Inferred!")*/01;=?/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-postgresqlABCDEFGHNOPQRSTUVWXYZ[\]^_`abcdefghijk $%4                                                                                                                                                                                                 L N M                                                                          G F C B D E                  [vppKdecOstqr                                                                                                                      `          o                         Y Y P  ! !!b! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! " " " " " # ## # # # # # # # # # $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ % % % & & & & &w& &x& & & & & & & & & & & & & & ' ' ' ' ' ' ' ' ' ' ' ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) * * * *** * ** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * +J+ + +k+u+ +l+ + + + + + + + + + + + + , , , , , , ,], , , , , , , , , , , , - - - - -z-]-v-a-- - - - - - - - - - - - - - - - - - - .y. . . . ... . . .{. .. .^._.}. . . . . . . . . . . . . . / / / / / / / /// / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / 0 0 0 0 0 0 0 0 1n12 2 2 2 22 2 22 2 2 2 2 2m22 2 2 2 2 2 334 4 4 4 5 5 5 5 5 5 5 5 5 5 5 5 5 5 55 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 555555556666666666666777777777777777777777777777777   8888889999999999:::::X::::W::U::S::Q:::::V:R:T:::::::::::::::::;;;;<<<<<<<<<<<<<==================>>>>>>>>>>>>>>>>??????? ))7:0squeal-postgresql-0.9.1.2-J1rCWbmzAtA8Fs0e0vpWDkSqueal.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.Unsafe%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.<.>MonadPQ StatementmanipulateParams queryParamstraversePreparedSqueal.PostgreSQL.Nullnull_ deleteFromRepeatableRead Serializablewith Data.Function&Join Data.MaybefromJust fromMaybetrue WindowArgWindowFunction OnConflictDoUpdateparam manipulate manipulationprepareprepare_Squeal.PostgreSQL.StatementvaluesControl.Monad.State.Class MonadState insertInto_update_ deleteFrom_-Squeal.PostgreSQL.Definition.Table.ConstraintcheckControl.CategoryCategorySqueal.PostgreSQLbaseGHC.OverloadedLabels fromLabelIsLabel>>>transformers-0.5.6.2 Control.Monad.Trans.State.Strict runStateTStateTControl.Monad.Trans.ExceptExceptT.free-categories-0.2.0.2-7tyX36BqQxg7OOSFiFUMK0Control.Category.Free:>>DonePath Data.QuiverdownupIsoQ'sop-core-0.5.0.2-8cmRYB37llUAjnR98I5kI0Data.SOP.BasicFunctorsKunK Data.SOP.NPNPNil:*/postgresql-libpq-0.10.0.0-8IQkyqviYNxGQqodinQ5sDatabase.PostgreSQL.LibPQ.OidOid"Database.PostgreSQL.LibPQ.Internal ConnectionDatabase.PostgreSQL.LibPQ.Enums SingleTuple FatalError NonfatalError BadResponseCopyBothCopyInCopyOutTuplesOk CommandOk EmptyQuery ExecStatus,resource-pool-0.4.0.0-LWrqQCwN7fyFQod2WX3E9XData.Pool.InternalPool RenderSQL renderSQL parenthesized bracketed<+>commaSeparated doubleQuotedsingleQuotedTextsingleQuotedUtf8escapeQuotedStringescapeQuotedTextrenderCommaSeparatedrenderCommaSeparatedConstraintrenderCommaSeparatedMaybe renderNat renderSymbolprintSQLescapeSquealException SQLExceptionConnectionExceptionDecodingExceptionColumnsException RowsExceptionSQLState sqlExecStatus sqlStateCodesqlErrorMessageDeadlockDetectedSerializationFailureCheckViolationUniqueViolation catchSqueal handleSqueal trySqueal throwSqueal$fExceptionSquealException$fEqSquealException$fShowSquealException $fEqSQLState$fShowSQLStateFixCharVarCharOnlyfromOnlyFixArray getFixArrayVarArray getVarArray Enumerated getEnumerated Composite getCompositeJsonbgetJsonbJsongetJsonMoneycentsvarChar getVarCharfixChar getFixChar$fHasDatatypeInfoOnly $fGenericOnly $fEqFixChar $fOrdFixChar $fReadFixChar $fShowFixChar $fEqVarChar $fOrdVarChar $fReadVarChar $fShowVarChar $fFunctorOnly$fFoldableOnly$fTraversableOnly$fEqOnly $fOrdOnly $fReadOnly $fShowOnly$fGenericOnly0 $fEqFixArray $fOrdFixArray$fShowFixArray$fReadFixArray$fGenericFixArray$fHasDatatypeInfoFixArray$fGenericFixArray0 $fEqVarArray $fOrdVarArray$fShowVarArray$fReadVarArray$fGenericVarArray$fHasDatatypeInfoVarArray$fGenericVarArray0$fEqEnumerated$fOrdEnumerated$fShowEnumerated$fReadEnumerated$fGenericEnumerated$fHasDatatypeInfoEnumerated$fGenericEnumerated0 $fEqComposite$fOrdComposite$fShowComposite$fReadComposite$fGenericComposite$fHasDatatypeInfoComposite$fGenericComposite0 $fEqJsonb $fOrdJsonb $fShowJsonb $fReadJsonb$fGenericJsonb$fHasDatatypeInfoJsonb$fGenericJsonb0$fEqJson $fOrdJson $fShowJson $fReadJson $fGenericJson$fHasDatatypeInfoJson$fGenericJson0 $fEqMoney $fOrdMoney $fShowMoney $fReadMoney$fGenericMoney$fHasDatatypeInfoMoney$fGenericMoney0MapFst FoldMerge MergeHelperMergeTwos MergeSortSort SubsetListSubListLengthInElem Additionalalsodisjoin*:one$fAdditionalaNPQualifiedAlias IsQualified!HasAllHasInDefaultPrettyPrinterPrettyPrintHaystackPrettyPrintInfo _needleName _haystackName_haystackPrettyPrintLookupFailedError'LookupFailedErrorMismatchError' MismatchErrorHasErrHas HasUnique AliasableasAliasedAsAlias GroupedByGrouping UngroupedGrouped::: renderAliased mapAliased$fGroupedBy[]tablecolumn:$fGroupedBy[]tablecolumn:0 $fRenderSQLNP$fRenderSQLAlias$fIsLabelaliasNP$fIsLabelalias1Alias$fIsLabelalias0Aliased$fAliasablealiasexpressionNP!$fAliasablealiasexpressionAliased $fHasErrkindallFieldsalias:field$fHaskindalias[]field$fHaskindalias:field$fHaskindalias:field1!$fHasErrkindallFieldsalias[]field$fHasErrkallFieldsalias:field1$fHasIn[](,)fields(,)$fHasAllk:fields:$fHasAllkind[]fields[]$fIsQualifiedqualifieralias(,)$fRenderSQLQualifiedAlias$fIsLabela0Aliased$fIsQualifiedq0a0Aliased$fIsLabelaQualifiedAlias$fIsQualifiedqaQualifiedAlias$fEqQualifiedAlias$fGenericQualifiedAlias$fOrdQualifiedAlias$fShowQualifiedAlias$fNFDataQualifiedAlias $fEqAlias$fGenericAlias $fOrdAlias $fShowAlias $fNFDataAlias $fOrdAliased $fEqAliased $fShowAliased FindQualified FindNamespaceFindName DbRelationsSchemaRelationsDbEnums SchemaEnums Updatable AllUnique IsNotElem PGJsonType PGJsonKeyPGlabel IsPGlabellabelPublic SchemasTypeIntersperseNewlinesFilterNonEmptyFieldIfNonEmptyPrettyPrintPartitionedSchema SchemaUnsafesSchemaProceduresSchemaFunctions SchemaIndexes SchemaTypes SchemaViews SchemaTablesPartitionSchema'PartitionSchemaPartitionedSchema_tables_views_types_indexes _functions _procedures_unsafes SchemaType ReturnsTypeReturns ReturnsTable IndexTypeBtreeHashGistSpgistGinBrin FunctionType SchemumTypeIndexFunction Procedure UnsafeSchemumDropIfConstraintsInvolveConstraintInvolvesElemDBSubsetDBSubDB 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(,)(,)$fIsPGlabellabelFUN$fIsPGlabellabelFUN0$fRenderSQLPGlabel$fIsPGlabellabel0NS$fIsPGlabellabel0NS0$fIsPGlabellabelNP$fIsPGlabellabelPGlabel$fIsNotElem(,)Bool(,)True$fIsNotElemkBoolxFalse $fAllUniquea:$fAllUniquea[]FixPGDimPGConstructorNamesOfConstructorNameOfConstructorsOf TupleCodeOfTupleOfTuplePGNullPGRowOfLabelsPGIsPGPG $fIsPGJsonb $fIsPGJson $fIsPGMoney$fIsPGConstant$fIsPGK $fIsPGConst $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 $fNFDataQueryMaterializationDefaultMaterialization MaterializedNotMaterializedWith withRecursive materializednotMaterialized$fRenderSQLMaterialization $fHasDatatypeInfoMaterialization$fGenericMaterialization $fRenderSQLCommonTableExpression$fAliasablectestatementPath,$fAliasablectestatementCommonTableExpression $fWithQuery$fEqMaterialization$fOrdMaterialization$fShowMaterialization$fReadMaterialization$fEnumMaterialization$fGenericMaterialization0UnsafeFromClauserenderFromClausesubquery$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 inferredtypetyperowtypeenumtypedef 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$fGenericColumnTypeExpression$fShowColumnTypeExpression$fEqColumnTypeExpression$fOrdColumnTypeExpression$fNFDataColumnTypeExpression$fGenericTypeExpression$fShowTypeExpression$fEqTypeExpression$fOrdTypeExpression$fNFDataTypeExpressionTimeUnitYearsQuarterMonthsWeeksDaysHoursMinutesSeconds Microseconds MillisecondsDecades Centuries MillenniaTimeOp!++!!-!-! PGAtTimeZone currentDate currentTimecurrentTimestamp localTimelocalTimestampnowmakeDatemakeTime makeTimestampmakeTimestamptz dateTrunc atTimeZone interval_$fTimeOpPGTypePGdatePGint4"$fTimeOpPGTypePGintervalPGinterval $fTimeOpPGTypePGtimetzPGinterval$fTimeOpPGTypePGtimePGinterval%$fTimeOpPGTypePGtimestamptzPGinterval#$fTimeOpPGTypePGtimestampPGinterval$fRenderSQLTimeUnit$fHasDatatypeInfoTimeUnit$fGenericTimeUnit $fEqTimeUnit $fOrdTimeUnit$fShowTimeUnit$fReadTimeUnit$fEnumTimeUnit$fGenericTimeUnit0@@.&.|.!<->arrayToTSvectortsvectorLengthnumnodeplainToTSqueryphraseToTSquerywebsearchToTSquery queryTree toTSquery toTSvector setWeightstripjsonToTSvectorjsonbToTSvectortsDeletetsFilter tsHeadlinelowerupper charLengthlikeilikestrposreplaceOrderByorderBySortExpressionAscDesc AscNullsFirst AscNullsLastDescNullsFirst DescNullsLast $fRenderSQL[]$fRenderSQLSortExpression$fShowSortExpressionRangeEmptyNonEmptyBoundInfiniteClosedOpenrange<=..<=<..<<=..<<..<=moreThanatLeastlessThanatMost singletonwhole.<@@>.<<@@>>&<&>-|-@+@*@- lowerBound upperBoundisEmptylowerInclowerInfupperIncupperInf rangeMerge $fIsPGRange $fEqRange $fOrdRange $fShowRange $fReadRange$fGenericRange$fFunctorRange$fFoldableRange$fTraversableRange$fGenericRange0$fHasDatatypeInfoRange $fEqBound $fOrdBound $fShowBound $fReadBound$fGenericBound$fFunctorBound$fFoldableBound$fTraversableBound GenericParams genericParams EncodeParamsrunEncodeParamsToArray arrayPayload arrayDims arrayNullsToFieldtoFieldToParamtoParamToPGtoPG nilParams.**.aParam appendParams enumParamrowParam.##.genericRowParams $fToPGdbRange$fToPGdbEnumerated $fToPGdbJsonb $fToPGdbJson $fToPGdbValue$fToPGdbDiffTime$fToPGdbUTCTime$fToPGdbLocalTime $fToPGdb(,)$fToPGdbTimeOfDay $fToPGdbDay$fToPGdbConstant $fToPGdbK $fToPGdbConst$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$fIsLabelfldEncodeParams$fContravariantEncodeParams$fGenericParamsdbparamsxxsFromAliasedValuefromAliasedValue GenericRow genericRow DecodeRow unDecodeRow FromArray fromArray FromField fromField FromValue fromValueFromPGfromPGdevaluerowValue runDecodeRow appendRowsconsRow decodeRowgenericProductRow enumValue $fFromPGRange$fFromPGEnumerated $fFromPGJsonb $fFromPGJson $fFromPGValue$fFromPGDiffTime$fFromPGUTCTime$fFromPGLocalTime $fFromPG(,)$fFromPGTimeOfDay $fFromPGDay$fFromPGConstant $fFromPGK $fFromPGConst$fFromPGFixChar$fFromPGVarChar$fFromPGByteString$fFromPGByteString0 $fFromPG[] $fFromPGText $fFromPGText0 $fFromPGChar$fFromPGNetAddr $fFromPGUUID $fFromPGMoney$fFromPGScientific$fFromPGDouble $fFromPGFloat $fFromPGOid $fFromPGInt64 $fFromPGInt32 $fFromPGInt16 $fFromPGBool$fFromValueNullMaybe$fFromValueNotNully$fFromField(,)(,)$fFromArray:typroduct$fFromArray[]NullMaybe$fFromArray[]NotNully$fFromPGFixArray$fFromPGVarArray$fFromPGVarArray0$fIsLabelfldMaybeT$fIsLabelfldMaybeT0$fIsLabelfldDecodeRow$fIsLabelfldDecodeRow0$fMonadFailDecodeRow$fGenericRowrowyys$fFromPGComposite$fFromAliasedValue(,)y$fFunctorDecodeRow$fApplicativeDecodeRow$fAlternativeDecodeRow$fMonadDecodeRow$fMonadPlusDecodeRow$fMonadErrorTextDecodeRow MonadResultgetRowgetRowsfirstRowntuplesnfields cmdStatus cmdTuples resultStatusokResultresultErrorMessageresultErrorCodeResultnextRow liftResult$fFunctorResult$fMonadResultioParamTypeMismatchErrorParamOutOfBoundsError HasParameter' HasParameter$fHasParameter0paramsx$fHasParameterixparamsx+$fHasParameter'originalIxallParamsixparamsx*$fHasParameter'originalIxallParams1paramsxatan2_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$fGenericTableExpression $fEqWaiting $fOrdWaiting $fShowWaiting $fReadWaiting $fEnumWaiting$fGenericWaiting$fEqLockStrength$fOrdLockStrength$fShowLockStrength$fReadLockStrength$fEnumLockStrength$fGenericLockStrength$fGenericGroupByClause$fShowGroupByClause$fEqGroupByClause$fOrdGroupByClause$fNFDataGroupByClause$fOrdHavingClause$fEqHavingClause$fShowHavingClause$fOrdBy$fEqBy$fShowByJoinItem JoinFunction JoinFunctionNcross crossJoincrossJoinLateralinner innerJoininnerJoinLateral leftOuter leftOuterJoinleftOuterJoinLateral rightOuterrightOuterJoinrightOuterJoinLateral fullOuter fullOuterJoinfullOuterJoinLateral$fRenderSQLJoinItemexistssubAllsubAnyin_notInCombineNullityjust_notNull unsafeNotNull monoNotNullcoalescefromNullisNull isNotNull matchNullnullIfJsonToRecordFunctionJsonPopulateFunctionJsonBuildObjectjsonBuildObjectjsonbBuildObject.->.->>.#>.#>>.?.?|.?&.-.#-.toJsontoJsonb arrayToJson rowToJsonjsonBuildArrayjsonbBuildArray jsonObject jsonbObject jsonZipObjectjsonbZipObjectjsonArrayLengthjsonbArrayLength jsonTypeof jsonbTypeofjsonStripNullsjsonbStripNullsjsonbSet jsonbInsert jsonbPrettyjsonEach jsonbEach jsonEachTextjsonArrayElementsText jsonbEachTextjsonObjectKeysjsonbObjectKeysjsonbArrayElementsTextjsonPopulateRecordjsonbPopulateRecordjsonPopulateRecordSetjsonbPopulateRecordSet 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$fInlineConstant $fInlineK $fInlineConst$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$fIsQualifiedtabcolAggregateArg$fIsLabelcolAggregateArg$$fFilterWhere[]AggregateArgUngrouped $fAggregate[][][][][]aExpression+$fAggregate[][][][][]AggregateArgExpression--#->-#->WinFun0 windowArgs windowFilterUnsafeWindowFunctionrenderWindowFunctionWindowDefinitionWindowsWindow partitionByunsafeWindowFunction1unsafeWindowFunctionNrank rowNumber denseRank percentRankcumeDistntilelaglead firstValue lastValuenthValue$fRenderSQLWindowDefinition$fOrderByWindowDefinitiongrp$fRenderSQLWindowFunction$fFilterWhere[]WindowArggrp$fRenderSQLWindowArg$fIsQualifiedtabcolWindowArg$fIsLabelcolWindowArg$fIsQualifiedtabcolWindowArg0$fIsLabelcolWindowArg0,$fAggregate[][][][][]WindowArgWindowFunction$fGenericWindowArg$fGenericWindowFunction$fShowWindowFunction$fEqWindowFunction$fOrdWindowFunction$fNFDataWindowFunctionStarDotStarAlsoselect_selectDistinctselectDistinct_selectDistinctOnselectDistinctOn_$fIsStringSelection$fRenderSQLSelection$fIsLabelcolSelection$fIsLabelcolSelection0$fIsQualifiedtabcolSelection$fIsQualifiedtabcolSelection0!$fAliasablecolExpressionSelection$fAdditional(,)Selection UsingClauseNoUsingUsingReturningClause Returning Manipulation_UnsafeManipulationrenderManipulation Returning_queryStatement$fWithManipulation$fRenderSQLManipulation$fRenderSQLReturningClause$fGenericManipulation$fShowManipulation$fEqManipulation$fOrdManipulation$fNFDataManipulationPrepared runPrepared deallocate$fRenderSQLStatement$fFunctorStatement$fProfunctorStatement$fTraversingPrepared$fArrowPlusPrepared$fArrowZeroPrepared$fArrowLoopPrepared$fArrowChoicePrepared$fArrowPrepared$fCategoryTYPEPrepared$fCostrongPrepared$fChoicePrepared$fStrongPrepared$fProfunctorPrepared$fAlternativePrepared$fApplicativePrepared$fFunctorPrepared$fGenericPrepared$fGeneric1TYPEPrepared executeParamsexecuteParams_executeexecute_ preparedForexecutePreparedexecutePrepared_manipulateParams_ manipulate_ forPreparedtraversePrepared_ forPrepared_$fMonadPQdbContT$fMonadPQdbRWST$fMonadPQdbRWST0$fMonadPQdbExceptT$fMonadPQdbMaybeT$fMonadPQdbWriterT$fMonadPQdbWriterT0$fMonadPQdbStateT$fMonadPQdbStateT0$fMonadPQdbReaderT$fMonadPQdbIdentityTDeferrableMode Deferrable NotDeferrable AccessMode ReadWriteReadOnlyIsolationLevel ReadCommittedReadUncommittedTransactionModeisolationLevel accessModedeferrableModetransactionallytransactionally_transactionallyRetrytransactionallyRetry_ ephemerally ephemerally_begincommitrollback withSavepoint defaultMode retryModelongRunningMode$fRenderSQLIsolationLevel$fRenderSQLAccessMode$fRenderSQLDeferrableMode$fRenderSQLTransactionMode$fShowTransactionMode$fEqTransactionMode$fShowDeferrableMode$fEqDeferrableMode$fShowAccessMode$fEqAccessMode$fShowIsolationLevel$fEqIsolationLevel TransactionConflictTarget OnConstraintConflictAction DoNothingConflictClauseOnConflictDoRaise QueryClauseValuesSelectSubqueryValues_ inlineValues_ inlineValues$fRenderSQLQueryClause$fRenderSQLConflictAction$fRenderSQLConflictTarget$fRenderSQLConflictClause unsafeCallcall unsafeCallNcallNReferentialActionNoActionRestrictCascadeSetNull SetDefaultOnUpdateClauseOnUpdateOnDeleteClauseOnDelete ForeignKeyedTableConstraintExpressionUnsafeTableConstraintExpressionrenderTableConstraintExpressionunique primaryKey foreignKey$$fRenderSQLTableConstraintExpression$fRenderSQLReferentialAction$fNFDataReferentialAction$fRenderSQLOnUpdateClause$fNFDataOnUpdateClause$fRenderSQLOnDeleteClause$fNFDataOnDeleteClause$fGenericOnDeleteClause$fShowOnDeleteClause$fEqOnDeleteClause$fOrdOnDeleteClause$fGenericOnUpdateClause$fShowOnUpdateClause$fEqOnUpdateClause$fOrdOnUpdateClause$fGenericReferentialAction$fShowReferentialAction$fEqReferentialAction$fOrdReferentialAction"$fGenericTableConstraintExpression$fShowTableConstraintExpression$fEqTableConstraintExpression$fOrdTableConstraintExpression!$fNFDataTableConstraintExpression DefinitionUnsafeDefinitionrenderDefinition manipulation_$fMonoidDefinition$fSemigroupDefinition$fCategory[]Definition$fRenderSQLDefinition$fGenericDefinition$fShowDefinition$fEqDefinition$fOrdDefinition$fNFDataDefinitionIndexedMonadTransPQdefineIndexed runIndexedIndexedMonadTranspqAppqJoinpqBindpqThen pqAndThen indexedDefine$fCategorykIndexedPQunPQrunPQexecPQevalPQwithConnection $fMonadPlusPQ$fAlternativePQ $fMonadFixPQ $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$fGenericAlterColumn$fShowAlterColumn$fEqAlterColumn$fOrdAlterColumn$fNFDataAlterColumn$fGenericAlterTable$fShowAlterTable$fEqAlterTable$fOrdAlterTable$fNFDataAlterTableMigrationsTable Migratory runMigrations Migration migrationName migrationDefmigrate migrateUp migrateDown mainMigratemainMigrateIso$fQFunctork2k3k2k3Migration$fMigratory[]IsoQIsoQ$fMigratory[]IsoQIsoQ0$fMigratory[]OpQOpQ$fMigratory[]OpQOpQ0$fMigratory[]DefinitionIndexed$fMigratory[]IndexedIndexed$fHasDatatypeInfoMigrationRow$fGenericMigrationRow$fGenericMigrationRow0$fShowMigrationRow$fGenericMigration createSchemacreateSchemaIfNotExistsdropSchemaCascadedropSchemaCascadeIfExistsProcedureDefinitionUnsafeProcedureDefinitionrenderProcedureDefinitioncreateProcedurecreateOrReplaceProcedurelanguageSqlManipulation dropProceduredropProcedureIfExists$fRenderSQLProcedureDefinition$fEqProcedureDefinition$fShowProcedureDefinition$fGenericProcedureDefinition$fNFDataProcedureDefinition IndexMethodUnsafeIndexMethodrenderIndexMethod createIndexcreateIndexIfNotExistsbtreehashgistspgistginbrin dropIndexdropIndexIfExists$fRenderSQLIndexMethod$fEqIndexMethod$fOrdIndexMethod$fShowIndexMethod$fGenericIndexMethodFunctionDefinitionUnsafeFunctionDefinitionrenderFunctionDefinitioncreateFunctioncreateOrReplaceFunctionlanguageSqlExprlanguageSqlQuerycreateSetFunctioncreateOrReplaceSetFunction dropFunctiondropFunctionIfExists$fRenderSQLFunctionDefinition$fEqFunctionDefinition$fShowFunctionDefinition$fGenericFunctionDefinition$fNFDataFunctionDefinitioncommentOnTable commentOnType commentOnViewcommentOnIndexcommentOnFunctioncommentOnColumncommentOnSchemabytestring-0.11.3.1Data.ByteString.Internal ByteString text-1.2.5.0Data.Text.InternalText GHC.MaybeMaybeNothing GHC.TypeNatsNatghc-prim GHC.TypesSymbolGHC.Exception.Type Exception Data.EitherEitherGHC.Base+++generics-sop-0.5.1.3-2er88speLM5IKwfAzZ47ywGenerics.SOP.UniverseCodeType GHC.TypeLits TypeErrorGenericHasDatatypeInfoJustFunctor Applicative AlternativeMonad MonadPlus mtl-2.2.2Control.Monad.Error.Class MonadErrorControl.Monad.Trans.MaybeMaybeT/postgresql-binary-0.13.1-LNq5fF3jyLC9VR6EAiyCFQPostgreSQL.Binary.DecodingValueControl.Monad.IO.ClassMonadIODatabase.PostgreSQL.LibPQ GHC.Exceptionthrow GHC.ClassesminGHC.Num+maybeimpossibleAggregateErrorunsafeAggregateData.Traversable Traversable Data.FoldableFoldableexceptions-0.10.4Control.Monad.Catch onExceptionreturntryLeftIO<*>join=<<>> Control.Monad<=<PQRunrenderCreationcreateMigrationsinsertMigrationdeleteMigrationselectMigration