!TB P      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~                  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFG 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 { | } ~                                                                                                 !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ Rendering helper functions(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone ,->FHSX_kN squeal-postgresqlA class for rendering SQLsqueal-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-postgresql6Comma separate the renderings of a heterogeneous list.#squeal-postgresql6Comma separate the renderings of a heterogeneous list.$squeal-postgresqlComma separate the / renderings of a heterogeneous list, dropping s.%squeal-postgresqlRender a promoted .&squeal-postgresqlRender a promoted .'squeal-postgresql Print SQL.(squeal-postgresql(! a character to prevent injection !"#$%&'('( !"#$%&7 List related types and functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.>@AHSUVX_kq^ )squeal-postgresqlCalculate the ) of a type level list(:kind! Length '[Char,String,Bool,Double](Length '[Char,String,Bool,Double] :: Nat= 4*squeal-postgresqlIn x xs" is a constraint that proves that x is in xs.+squeal-postgresqlElem is a promoted  !.,squeal-postgresqlAn ,) is a type-aligned list or free category./squeal-postgresqlThe /L class is for appending type-level list parameterized constructors such as , ", and #.1squeal-postgresql1 is simply promoted  and is used in JOINs in #s.2squeal-postgresql2( is a utility function for splitting an  list into pieces.3squeal-postgresql3 turns an , into a standard list.4squeal-postgresql4* applies a function to each element of an ,.5squeal-postgresqlA 5 step.6squeal-postgresql A useful operator for ending an # list of length at least 2 without . )*+,-./0123456 612/0,-.534+*).769 Aliases(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.179=>?@ACHSUVX_kq:squeal-postgresql:=es enables multi-schema support by allowing a reference to a $, % or &> 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-postgresqlHas alias fields field# is a constraint that proves that fields has a field of alias ::: field , inferring field from alias and fields.Asqueal-postgresqlHasUnique alias fields field# is a constraint that proves that fields is a singleton of alias ::: field.Bsqueal-postgresqlThe B$ class provides a way to scrap your  s in an  list of D expressions.Dsqueal-postgresqlThe E) operator is used to name an expression. E is like a demoted version of L.8Just "hello" `As` #hi :: Aliased Maybe ("hi" ::: String)As (Just "hello") AliasFsqueal-postgresqlF*es are proxies for a type level string or  and have an  instance so that with -XOverloadedLabels:set -XOverloadedLabels#foobar :: Alias "foobar"AliasHsqueal-postgresqlA Hf 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 ' without aggregating.Isqueal-postgresqlI( is an auxiliary namespace, created by GROUP BY clauses ((%), and used for typesafe aggregationJsqueal-postgresqlno aggregation permittedKsqueal-postgresql8aggregation required for any column which is not groupedLsqueal-postgresqlThe alias operator L is like a promoted version of E4, a type level pair between an alias and some type.Msqueal-postgresql=let renderMaybe = fromString . maybe "Nothing" (const "Just")6renderAliased renderMaybe (Just (3::Int) `As` #an_int)"Just AS \"an_int\""Qsqueal-postgresql$printSQL (#jimbob :: Alias "jimbob")"jimbob":;<=>?@ABCDEFGHIJKLMLFGDEBCM@A>?:;<=IJKH=9 L6-Embedding of PostgreSQL type and alias system(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.179=>?@ACHSUVX_kq.Josqueal-postgresql No elem of xs6 appears more than once, in the context of assignment.psqueal-postgresqlUtility class for o! to provide nicer error messages.qsqueal-postgresqlIs a type a valid JSON type?rsqueal-postgresqlIs a type a valid JSON key?ssqueal-postgresqlA s unit type with an u instanceusqueal-postgresqlu looks very much like the ' class. Whereas the overloaded label, ! is used for column references, vs are used for enum terms. A v7 is called with type application like `label @"beef"`.wsqueal-postgresql2A type family to use for a single schema database.xsqueal-postgresqlA database contains one or more named schemas, which in turn contain tables. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.<There are several reasons why one might want to use schemas:LTo allow many users to use one database without interfering with each other.NTo organize database objects into logical groups to make them more manageable.sThird-party applications can be put into separate schemas so they do not collide with the names of other objects.ysqueal-postgresqlEThe schema of a database consists of a list of aliased, user-defined zs.:{&type family Schema :: SchemaType where Schema = '[ "users" ::: 'Table (3 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>1 '[ "id" ::: 'Def :=> 'NotNull 'PGint40 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]) , "emails" ::: 'Table (/ '[ "pk_emails" ::: 'PrimaryKey '["id"]C , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"] ] :=>4 '[ "id" ::: 'Def :=> 'NotNull 'PGint43 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint43 , "email" ::: 'NoDef :=> 'Null 'PGtext ]) ]:}zsqueal-postgresqlA z" is a user-defined type, either a {, | or }.~squeal-postgresql Drop all s that involve a columnsqueal-postgresql Check if a  involves a columnsqueal-postgresqlRename alias0 alias1 xs replaces the alias alias0 by alias1 in xs and is used in ) and *.squeal-postgresqlAlter alias x xs& replaces the type associated with an alias in xs with the type x and is used in + and ,.squeal-postgresql Drop alias xs" removes the type associated with alias in xs and is used in - statements and in  ALTER TABLE . statements.squeal-postgresqlCreate alias x xs adds  alias ::: x to the end of xs and is used in / statements and in  ALTER TABLE 0.squeal-postgresql# is an idempotent that nullifies a E used to nullify the left or right hand side of an outer join in a #.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-postgresql forgets about NULL and any column constraints.squeal-postgresqlIntegral Postgres types.squeal-postgresqlFloating Postgres types.squeal-postgresqlNumeric Postgres types.squeal-postgresqlConvert a table to a row type.squeal-postgresql removes table constraints.squeal-postgresql removes column constraints.squeal-postgresql is a row of Js. It can be thought of as a product, or horizontal gluing and is used in #s and 1s.squeal-postgresqlA  is a row of 6. They correspond to Haskell record types by means of 2 and are used in many places.:{&type family PersonRow :: RowType where PersonRow =) '[ "name" ::: 'NotNull 'PGtext) , "age" ::: 'NotNull 'PGint4) , "dateOfBirth" ::: 'Null 'PGdate ]:}squeal-postgresqlN 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-postgresqlD 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.TypeLitsMtype family IdColumn :: ColumnType where IdColumn = 'Def :=> 'NotNull 'PGint4Rtype family EmailColumn :: ColumnType where EmailColumn = 'NoDef :=> 'Null 'PGtextsqueal-postgresql encodes the availability of DEFAULTM for inserts and updates. A column can be assigned a default value. A data 34| 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, S is a type level pair between a "constraint" and some type, for use in pairing a  with a  to produce a  or a  and a  to produce a .squeal-postgresql: encodes the potential presence or definite absence of a NULLB allowing operations which are sensitive to such to be well typed.:kind 'Null 'PGint4'Null 'PGint4 :: NullityType:kind 'NotNull ('PGvarchar 50)''NotNull ('PGvarchar 50) :: NullityTypesqueal-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-postgresqlUenumerated (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 forsqueal-postgresql0an escape hatch for unsupported PostgreSQL typesSopqrstuvwxyz{|}~Sz{|}yxwuvst~poqr7 6Embedding of Haskell types into Postgres's type system(c) Eitan Chatav, 2010eitan@morphism.tech experimentalNone&'-.45678=>?@ACHUVX_kP24squeal-postgresqlThe V newtype is an indication that the Haskell type it's applied to should be stored as a .9:kind! PG (FixArray ((Double, Double), (Double, Double)))<PG (FixArray ((Double, Double), (Double, Double))) :: PGType*= 'PGfixarray '[2, 2] ('NotNull 'PGfloat8)squeal-postgresqlThe V newtype is an indication that the Haskell type it's applied to should be stored as a .$:kind! PG (VarArray (Vector Double))'PG (VarArray (Vector Double)) :: PGType"= 'PGvararray ('NotNull 'PGfloat8)squeal-postgresqlThe V newtype is an indication that the Haskell type it's applied to should be stored as a .squeal-postgresqlThe V newtype is an indication that the Haskell type it's applied to should be stored as a .squeal-postgresqlThe V newtype is an indication that the Haskell type it's applied to should be stored as a .squeal-postgresqlThe V newtype is an indication that the Haskell type it's applied to should be stored as a .squeal-postgresqlThe G newtype stores a monetary value in terms of the number of cents, i.e.  $2,000.20 would be expressed as Money { cents = 200020 }.import Control.Monad (void)&import Control.Monad.IO.Class (liftIO)import Squeal.PostgreSQL:{let< roundTrip :: Query_ (Public '[]) (Only Money) (Only Money)9 roundTrip = values_ $ parameter @1 money `as` #fromOnly:}let input = Only (Money 20020):{?withConnection "host=localhost port=5432 dbname=exampledb" $ do* result <- runQueryParams roundTrip input Just output <- firstRow result" liftIO . print $ input == output:}Truesqueal-postgresql extracts / of the base type of nested homogeneous tuples.squeal-postgresql@ turns Haskell nested homogeneous tuples into a list of lengths.squeal-postgresql,Calculate the names of nullary constructors.squeal-postgresqlPCalculates 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-postgresqlT turns a Haskell tuple type (including record types) into the corresponding list of s.#:kind! TuplePG (Double, Maybe Char)-TuplePG (Double, Maybe Char) :: [NullityType]+= '[ 'NotNull 'PGfloat8, 'Null ('PGchar 1)]squeal-postgresql turns a Haskell type into a .:kind! NullPG DoubleNullPG Double :: NullityType= 'NotNull 'PGfloat8:kind! NullPG (Maybe Double)$NullPG (Maybe Double) :: NullityType= 'Null 'PGfloat8squeal-postgresql applies  to the fields of a list.squeal-postgresql turns a Haskell  into a .B may be applied to normal Haskell record types provided they have  and  instances;Odata Person = Person { name :: Strict.Text, age :: Int32 } deriving GHC.Genericinstance SOP.Generic Person#instance SOP.HasDatatypeInfo Person:kind! RowPG Person'RowPG Person :: [(Symbol, NullityType)]<= '["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 Y type family embeds a subset of Haskell types as Postgres types. As an open type family,  is extensible.:kind! PG LocalTimePG LocalTime :: PGType= 'PGtimestampnewtype MyDouble = My Double:set -XTypeFamilies%type instance PG MyDouble = 'PGfloat8squeal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresql 1squeal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresqlsqueal-postgresql ( hask)squeal-postgresql ( hask)squeal-postgresql ( hask)squeal-postgresql ( hask) ( hask)""Squeal expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17=>?@ACFHMSUVX_k}z squeal-postgresqlContained by operatorssqueal-postgresqlA  RankNTypea for functions with a variable-length list of homogeneous arguments and at least 1 more argument.squeal-postgresqlA  RankNTypeL for functions with a fixed-length list of heterogeneous arguments. Use the 6. operator to end your argument lists, like so.EprintSQL (unsafeFunctionN "fun" (true :* false :* localTime *: true))!fun(TRUE, FALSE, LOCALTIME, TRUE)squeal-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 569, indeed a subcategory as it is closed under the usual 57 and 58.squeal-postgresqlA  RankNType for binary operators.squeal-postgresqlAn  is a closed  . It is a F RankNType but don't be scared. Think of it as an expression which sees no namespaces, so you can't use parameters or alias references. It can be used as a simple piece of more complex s.squeal-postgresql;s are used in a variety of contexts, such as in the target 9 of the :" command, as new column values in ; or <, or in search =>s 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  are outer ::  , the from- clauses of any outer queries in which the % is a correlated subquery expression; commons ::  , the ?s that are in scope for the ;grp ::  I, the I of the fromC clause which may limit which columns may be referenced by alias; schemas ::  x;, 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 $ unsafeUnaryOpL "NOT" true (NOT TRUE)squeal-postgresql.printSQL $ true & unsafeUnaryOpR "IS NOT TRUE"(TRUE IS NOT TRUE)squeal-postgresql"printSQL $ unsafeFunction "f" truef(TRUE) squeal-postgresqlVprintSQL $ unsafeFunctionN "f" (currentTime :* localTimestamp :* false *: literal 'a'),f(CURRENT_TIME, LOCALTIMESTAMP, FALSE, E'a')squeal-postgresqlinputssqueal-postgresqlmust have at least 1 inputsqueal-postgresqloutputsqueal-postgresqlinputssqueal-postgresqloutputsqueal-postgresqlinputsqueal-postgresqloutputsqueal-postgresql left inputsqueal-postgresql right inputsqueal-postgresqloutputsqueal-postgresqlcannot reference aliases   Type expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'.7=>?@AHMSUVXk*Gsqueal-postgresqlH is a demoted version of a Isqueal-postgresqlIs are used in Ls and / commands.Lsqueal-postgresqlprintSQL $ true & cast int4(TRUE :: int4)Msqueal-postgresqlA safe version of L* which just matches a value with its type.printSQL (1 & astype int) (1 :: int)Nsqueal-postgresqlN" will add a type annotation to an = which can be useful for fixing the storage type of a value.printSQL (inferredtype true)(TRUE :: bool)Osqueal-postgresql The enum or composite type in a } can be expressed by its alias.Psqueal-postgresql&The composite type corresponding to a {+ definition can be expressed by its alias.Qsqueal-postgresql&The composite type corresponding to a |+ definition can be expressed by its alias.Rsqueal-postgresqllogical Boolean (true/false)Ssqueal-postgresqlsigned two-byte integerTsqueal-postgresqlsigned two-byte integerUsqueal-postgresqlsigned four-byte integerVsqueal-postgresqlsigned four-byte integerWsqueal-postgresqlsigned four-byte integerXsqueal-postgresqlsigned eight-byte integerYsqueal-postgresqlsigned eight-byte integerZsqueal-postgresql arbitrary precision numeric type[squeal-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 amount`squeal-postgresql variable-length character stringasqueal-postgresqlfixed-length character stringbsqueal-postgresqlfixed-length character stringcsqueal-postgresql variable-length character stringdsqueal-postgresql variable-length character stringesqueal-postgresqlbinary data ("byte array")fsqueal-postgresqldate and time (no time zone)gsqueal-postgresql"date and time, including time zonehsqueal-postgresql calendar date (year, month, day)isqueal-postgresqltime of day (no time zone)jsqueal-postgresql time of day, including time zoneksqueal-postgresql time spanlsqueal-postgresqluniversally unique identifiermsqueal-postgresqlIPv4 or IPv6 host addressnsqueal-postgresqltextual JSON dataosqueal-postgresqlbinary JSON data, decomposedpsqueal-postgresqlvariable length arrayqsqueal-postgresqlfixed length arrayrenderSQL (fixarray @'[2] json) "json[2]"rsqueal-postgresqltext search queryssqueal-postgresqltext search documentLsqueal-postgresqltype to cast assqueal-postgresqlvalue to convertMsqueal-postgresqltype to specify assqueal-postgresqlvalue-GHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrs-IJKLMNGHOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrs Date/Time expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone -.7@ACHSX_õsqueal-postgresqlA  to use in  construction.squeal-postgresql&Affine space operations on time types.squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !+ 365)(make_date(1984, 7, 3) + 365)squeal-postgresql+printSQL (365 +! makeDate (1984 :* 7 *: 3))(365 + make_date(1984, 7, 3))squeal-postgresql+printSQL (makeDate (1984 :* 7 *: 3) !- 365)(make_date(1984, 7, 3) - 365)squeal-postgresql4printSQL (makeDate (1984 :* 7 *: 3) !-! currentDate)&(make_date(1984, 7, 3) - CURRENT_DATE)squeal-postgresqlprintSQL currentDate CURRENT_DATEsqueal-postgresqlprintSQL currentTime CURRENT_TIMEsqueal-postgresqlprintSQL currentTimestampCURRENT_TIMESTAMPsqueal-postgresqlprintSQL localTime LOCALTIMEsqueal-postgresqlprintSQL localTimestampLOCALTIMESTAMPsqueal-postgresql%Current date and time (equivalent to ) printSQL nownow()squeal-postgresql+Create date from year, month and day fields$printSQL (makeDate (1984 :* 7 *: 3))make_date(1984, 7, 3)squeal-postgresql0Create time from hour, minute and seconds fields%printSQL (makeTime (8 :* 15 *: 23.5))make_time(8, 15, 23.5)squeal-postgresqlGCreate timestamp from year, month, day, hour, minute and seconds fields=printSQL (makeTimestamp (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))(make_timestamp(2013, 7, 15, 8, 15, 23.5)squeal-postgresqluCreate timestamp with time zone from year, month, day, hour, minute and seconds fields; the current time zone is used?printSQL (makeTimestamptz (2013 :* 7 :* 15 :* 8 :* 15 *: 23.5))*make_timestamptz(2013, 7, 15, 8, 15, 23.5)squeal-postgresqlprintSQL $ interval_ 7 Days(INTERVAL '7.0 days')6666 Text search expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UVsqueal-postgresql @ matches tsquery ?squeal-postgresqlAND  A s togethersqueal-postgresqlOR  A s togethersqueal-postgresql negate a  Asqueal-postgresql A followed by  Asqueal-postgresqlconvert array of lexemes to  @squeal-postgresqlnumber of lexemes in  @squeal-postgresql$number of lexemes plus operators in  Asqueal-postgresqlproduce  A ignoring punctuationsqueal-postgresqlproduce  A2 that searches for a phrase, ignoring punctuationsqueal-postgresqlproduce  A from a web search style querysqueal-postgresqlget indexable part of a  Asqueal-postgresqlnormalize words and convert to  Asqueal-postgresqlreduce document text to  @squeal-postgresql!assign weight to each element of  @squeal-postgresql"remove positions and weights from  @squeal-postgresql#jsonToTSvector (document *: filter)> reduce each value in the document, specified by filter to a  @D, and then concatenate those in document order to produce a single  @. filter is a  BV array, that enumerates what kind of elements need to be included into the resulting  @p. 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  @D, and then concatenate those in document order to produce a single  @. filter is a  CV array, that enumerates what kind of elements need to be included into the resulting  @p. 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  @squeal-postgresql-select only elements with given weights from  @squeal-postgresql display a  A match Text expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UVsqueal-postgresqlprintSQL $ lower "ARRRGGG"lower(E'ARRRGGG')squeal-postgresqlprintSQL $ upper "eeee"upper(E'eeee')squeal-postgresqlprintSQL $ charLength "four"char_length(E'four')squeal-postgresqlThe  expression returns true if the string matches the supplied pattern. If patterns does not contain percent signs or underscores, then the pattern only represents the string itself; in that case  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' LIKE E'a%')squeal-postgresqlrThe key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale.printSQL $ "abc" `ilike` "a%"(E'abc' ILIKE E'a%')Sort expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'.1_ Esqueal-postgresqlThe $ clause causes the result rows of a 1) 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.UYou can also control the order in which rows are processed by window functions using  within D.squeal-postgresqls are used by & to optionally sort the results of a E.  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 F@ operator. Similarly, descending order is determined with the G operator. , ,  and p options are used to determine whether nulls appear before or after non-null values in the sort ordering of a  result column.   Parameters(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.=>?@ACHSUVXkUsqueal-postgresqlA X constraint is used to indicate a value that is supplied externally to a SQL statement. H, I and JV 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 I.glet expr = parameter @1 int4 :: Expression outer '[] grp schemas '[ 'Null 'PGint4] from ('Null 'PGint4) printSQL expr ($1 :: int4)squeal-postgresql takes a 7 using type application and for basic types, infers a I.blet expr = param @1 :: Expression outer commons grp schemas '[ 'Null 'PGint4] from ('Null 'PGint4) printSQL expr ($1 :: int4)squeal-postgresqlparamMath expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UV'squeal-postgresql:{let% expression :: Expr (null 'PGfloat4) expression = atan2_ (pi *: 2)in printSQL expression:}atan2(pi(), 2)squeal-postgresql&integer division, truncates the result:{letO expression :: Expression outer commons grp schemas params from (null 'PGint2) expression = 5 `quot_` 2in printSQL expression:}(5 / 2)squeal-postgresqlremainder upon integer division:{letO expression :: Expression outer commons grp schemas params from (null 'PGint2) expression = 5 `rem_` 2in printSQL expression:}(5 % 2)squeal-postgresql:{letQ expression :: Expression outer commons grp schemas params from (null 'PGfloat4) expression = trunc piin printSQL expression:} trunc(pi())squeal-postgresql:{letQ expression :: Expression outer commons grp schemas params from (null 'PGfloat4) expression = round_ piin printSQL expression:} round(pi())squeal-postgresql:{letQ expression :: Expression outer commons grp schemas params from (null 'PGfloat4) expression = ceiling_ piin printSQL expression:} ceiling(pi())Logical expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UV7squeal-postgresqlA  is an , which can evaluate to ,  or KL1. This is because SQL uses a three valued logic.squeal-postgresql printSQL trueTRUEsqueal-postgresqlprintSQL falseFALSEsqueal-postgresqlprintSQL $ not_ true (NOT TRUE)squeal-postgresqlprintSQL $ true .&& false(TRUE AND FALSE)squeal-postgresqlprintSQL $ true .|| false(TRUE OR FALSE)squeal-postgresql:{letO expression :: Expression outer commons grp schemas params from (null 'PGint2)9 expression = caseWhenThenElse [(true, 1), (false, 2)] 3in printSQL expression:}2CASE WHEN TRUE THEN 1 WHEN FALSE THEN 2 ELSE 3 ENDsqueal-postgresql:{letO expression :: Expression outer commons grp schemas params from (null 'PGint2)" expression = ifThenElse true 1 0in printSQL expression:} CASE WHEN TRUE THEN 1 ELSE 0 ENDsqueal-postgresqlwhens and thenssqueal-postgresqlelsesqueal-postgresqlthensqueal-postgresqlelse32Null(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.UVIsqueal-postgresql analagous to printSQL null_NULLsqueal-postgresql analagous to printSQL $ notNull trueTRUEsqueal-postgresql+return the leftmost value which is not NULL'printSQL $ coalesce [null_, true] falseCOALESCE(NULL, TRUE, FALSE)squeal-postgresql analagous to MN using COALESCEprintSQL $ fromNull true null_COALESCE(NULL, TRUE)squeal-postgresqlprintSQL $ null_ & isNull NULL IS NULLsqueal-postgresqlprintSQL $ null_ & isNotNullNULL IS NOT NULLsqueal-postgresql analagous to  using IS NULL$printSQL $ matchNull true not_ null_4CASE WHEN NULL IS NULL THEN TRUE ELSE (NOT NULL) ENDsqueal-postgresqlright inverse to ", if its arguments are equal then  gives NULL.#:set -XTypeApplications -XDataKindswlet expr = nullIf (false *: param @1) :: Expression outer commons grp schemas '[ 'NotNull 'PGbool] from ('Null 'PGbool) printSQL exprNULLIF(FALSE, ($1 :: bool))squeal-postgresqlwhat to convert NULL tosqueal-postgresqlwhat to convert NULL tosqueal-postgresqlfunction to perform when NULL is absentComparison expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone -.HSUVXlsqueal-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-postgresqllet expr = greatest [param @1] currentTimestamp :: Expression outer commons grp schemas '[ 'NotNull 'PGtimestamptz] from ('NotNull 'PGtimestamptz) 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          444444Aggregate functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.7=>?@ACHUV_Z%squeal-postgresqlA type family that calculates  type of a .squeal-postgresqlA type family that calculates  of a given argument .squeal-postgresqls are used for the input of  s. 0 invokes the aggregate once for each input row.  invokes the aggregate once for each distinct value of the expression (or distinct set of values, for multiple expressions) found in the inputsqueal-postgresql? functions compute a single result from a set of input values.  functions can be used as H  s as well as Os.squeal-postgresql4A special aggregation that does not require an input:{let\ expression :: Expression '[] commons ('Grouped bys) schemas params from ('NotNull 'PGint8) expression = countStarin printSQL expression:}count(*)squeal-postgresql:{letw expression :: Expression '[] commons ('Grouped bys) schemas params '[tab ::: '["col" ::: null ty]] ('NotNull 'PGint8) expression = count (All #col)in printSQL expression:}count(ALL "col")squeal-postgresql:{let expression :: Expression '[] commons ('Grouped bys) schemas params '[tab ::: '["col" ::: 'Null 'PGnumeric]] ('Null 'PGnumeric)# expression = sum_ (Distinct #col)in printSQL expression:}sum(DISTINCT "col")squeal-postgresql9input values, including nulls, concatenated into an arraysqueal-postgresql!aggregates values as a JSON arraysqueal-postgresql!aggregates values as a JSON array squeal-postgresql=the bitwise AND of all non-null input values, or null if none:{lety expression :: Expression '[] commons ('Grouped bys) schemas params '[tab ::: '["col" ::: null 'PGint4]] ('Null 'PGint4)% expression = bitAnd (Distinct #col)in printSQL expression:}bit_and(DISTINCT "col")!squeal-postgresql<the bitwise OR of all non-null input values, or null if none:{lety expression :: Expression '[] commons ('Grouped bys) schemas params '[tab ::: '["col" ::: null 'PGint4]] ('Null 'PGint4) expression = bitOr (All #col)in printSQL expression:}bit_or(ALL "col")"squeal-postgresql2true if all input values are true, otherwise false:{letu winFun :: WindowFunction '[] commons 'Ungrouped schemas params '[tab ::: '["col" ::: null 'PGbool]] ('Null 'PGbool) winFun = boolAnd #colin printSQL winFun:}bool_and("col")#squeal-postgresql9true if at least one input value is true, otherwise false:{lety expression :: Expression '[] commons ('Grouped bys) schemas params '[tab ::: '["col" ::: null 'PGbool]] ('Null 'PGbool) expression = boolOr (All #col)in printSQL expression:}bool_or(ALL "col")$squeal-postgresqlequivalent to ":{lety expression :: Expression '[] commons ('Grouped bys) schemas params '[tab ::: '["col" ::: null 'PGbool]] ('Null 'PGbool)$ expression = every (Distinct #col)in printSQL expression:}every(DISTINCT "col")%squeal-postgresql3maximum value of expression across all input values&squeal-postgresql3minimum value of expression across all input values'squeal-postgresql1the average (arithmetic mean) of all input values(squeal-postgresqlcorrelation coefficient:{let expression :: Expression '[] c ('Grouped g) s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)$ expression = corr (All (#y *: #x))in printSQL expression:}corr(ALL "y", "x"))squeal-postgresqlpopulation covariance:{let expression :: Expression '[] c ('Grouped g) s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)( expression = covarPop (All (#y *: #x))in printSQL expression:}covar_pop(ALL "y", "x")*squeal-postgresqlsample covariance:{let winFun :: WindowFunction '[] c 'Ungrouped s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8) winFun = covarSamp (#y *: #x)in printSQL winFun:}covar_samp("y", "x")+squeal-postgresql.average of the independent variable (sum(X)/N):{let expression :: Expression '[] c ('Grouped g) s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8)( expression = regrAvgX (All (#y *: #x))in printSQL expression:}regr_avgx(ALL "y", "x"),squeal-postgresql,average of the dependent variable (sum(Y)/N):{let winFun :: WindowFunction '[] c 'Ungrouped s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8) winFun = regrAvgY (#y *: #x)in printSQL winFun:}regr_avgy("y", "x")-squeal-postgresql:number of input rows in which both expressions are nonnull:{let winFun :: WindowFunction '[] c 'Ungrouped s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGint8) winFun = regrCount (#y *: #x)in printSQL winFun:}regr_count("y", "x").squeal-postgresqly-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs >>> :{ let expression :: Expression '[] c ('Grouped g) s p '[t ::: '["x" ::: 'NotNull 'PGfloat8, "y" ::: 'NotNull 'PGfloat8]] ('Null 'PGfloat8) expression = regrIntercept (All (y *: @x)) in printSQL expression :} regr_intercept(ALL "y", "x")/squeal-postgresql regr_r2(Y, X)', square of the correlation coefficient0squeal-postgresqlregr_slope(Y, X)P, slope of the least-squares-fit linear equation determined by the (X, Y) pairs1squeal-postgresqlregr_sxx(Y, X)G, sum(X^2) - sum(X)^2/N ( sum of squares  of the independent variable)2squeal-postgresqlregr_sxy(Y, X)[, sum(X*Y) - sum(X) * sum(Y)/N ( sum of products  of independent times dependent variable)3squeal-postgresqlregr_syy(Y, X)E, sum(Y^2) - sum(Y)^2/N ( sum of squares  of the dependent variable)4squeal-postgresqlhistorical alias for 65squeal-postgresql1population standard deviation of the input values6squeal-postgresql-sample standard deviation of the input values7squeal-postgresqlhistorical alias for 98squeal-postgresqlVpopulation variance of the input values (square of the population standard deviation)9squeal-postgresqlNsample variance of the input values (square of the sample standard deviation)squeal-postgresql*escape hatch to define aggregate functions squeal-postgresql what to count squeal-postgresqlwhat to aggregate!squeal-postgresqlwhat to aggregate"squeal-postgresqlwhat to aggregate#squeal-postgresqlwhat to aggregate$squeal-postgresqlwhat to aggregate%squeal-postgresqlwhat to maximize&squeal-postgresqlwhat to minimize'squeal-postgresqlwhat to averagesqueal-postgresqlaggregate functionsqueal-postgresqlfunction& !"#$%&'()*+,-./0123456789& !"#$%&'()*+,-./0123456789Window functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'.7=>?@AHMSX_Bsqueal-postgresqlA  RankNTypeS for window functions with a fixed-length list of heterogeneous arguments. Use the 6% operator to end your argument lists.Csqueal-postgresqlA  RankNType& for window functions with 1 argument.Dsqueal-postgresqlA  RankNType( for window functions with no arguments.Esqueal-postgresqlA window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.Hsqueal-postgresqlA HD is a set of table rows that are somehow related to the current rowJsqueal-postgresqlThe J clause within DP divides the rows into groups, or partitions, that share the same values of the J x(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.Ksqueal-postgresql*escape hatch for defining window functionsLsqueal-postgresql9escape hatch for defining multi-argument window functionsMsqueal-postgresql+rank of the current row with gaps; same as N of its first peer printSQL rankrank()Nsqueal-postgresql?number of the current row within its partition, counting from 1printSQL rowNumber row_number()Osqueal-postgresqlFrank of the current row without gaps; this function counts peer groupsprintSQL denseRank dense_rank()Psqueal-postgresqlIrelative rank of the current row: (rank - 1) / (total partition rows - 1)printSQL percentRankpercent_rank()Qsqueal-postgresqlmcumulative distribution: (number of partition rows preceding or peer with current row) / total partition rowsprintSQL cumeDist cume_dist()Rsqueal-postgresql[integer ranging from 1 to the argument value, dividing the partition as equally as possibleprintSQL $ ntile 5ntile(5)Ssqueal-postgresqlreturns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row.Tsqueal-postgresqlreturns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row.Usqueal-postgresqlLreturns value evaluated at the row that is the first row of the window frameVsqueal-postgresqlKreturns value evaluated at the row that is the last row of the window frameWsqueal-postgresqlqreturns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such rowBsqueal-postgresqlinputssqueal-postgresqloutputCsqueal-postgresqlinputsqueal-postgresqloutputDsqueal-postgresqlcannot reference aliasesIsqueal-postgresqlJ clausesqueal-postgresql clauseJsqueal-postgresql partitionsBCDEFGHIJKLMNOPQRSTUVWJMNOPQRSTUVWKLEFGHIDCBSqueal queries(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&',-.17=>?@AHMSUVX_ekq0asqueal-postgresqlbk provides a way to write auxiliary statements for use in a larger query. These statements, referred to as cQs, can be thought of as defining temporary tables that exist just for one query.csqueal-postgresqlA c is an auxiliary statement in a b clause.esqueal-postgresqlA e; is used to eliminate groups that are not of interest. An J q may only use f while a K q must use g% whose conditions are combined with .hsqueal-postgresqlA h indicates the I of a q. A i indicates J while a j indicates K. NoGroups is distinguised from  Group Nil& since no aggregation can be done on NoGroups while all output s must be aggregated in  Group Nil. In general, all output s in the complement of bys must be aggregated in  Group bys.ksqueal-postgresqlks 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.nsqueal-postgresqlA n? can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these.qsqueal-postgresqlA q4 computes a table. The table expression contains a s" that is optionally followed by a t, u, v, w, x and ys. 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.ssqueal-postgresqlVA table reference that can be a table name, or a derived table such as a subquery, a JOIN- construct, or complex combinations of these.tsqueal-postgresql)optional search coditions, combined with . After the processing of the s! 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 sP; this is not required, but otherwise the WHERE clause will be fairly useless.usqueal-postgresqlThe u 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.vsqueal-postgresql"If a table has been grouped using 0, but only certain groups are of interest, the v can be used, much like a t;, to eliminate groups from the result. Expressions in the vw can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function).wsqueal-postgresqlThe w. is for optional sorting. When more than one u is specified, the later (right) values are used to sort rows that are equal according to the earlier (left) values.xsqueal-postgresqlThe x 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).ysqueal-postgresqlThe y 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.zsqueal-postgresqlThe simplest kinds of z are { and | which emits all columns that a q produces. A select } is a list of s. A z could be a list of Es ~ H. / zs can be selected with .squeal-postgresqlThe top level  type is parameterized by a schemas x4, against which the query is type-checked, an input  parameters Haskell , and an ouput row Haskell .#A top-level query can be run using P, or if parameters = () using Q. Generally,  parametersT will be a Haskell tuple or record whose entries may be referenced using positional Rs and rowR will be a Haskell record, whose entries will be targeted using overloaded labels.#Let's see some examples of queries.*:set -XDeriveAnyClass -XDerivingStrategies:{+data Row a b = Row { col1 :: a, col2 :: b } deriving stock (GHC.Generic)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):} simple query:`type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let6 query :: Query_ (Public Schema) () (Row Int32 Int32)) query = select Star (from (table #tab))in printSQL query:}SELECT * FROM "tab" AS "tab"restricted query::{ let6 query :: Query_ (Public Schema) () (Row Int32 Int32) query =< select_ ((#col1 + #col2) `as` #col1 :* #col1 `as` #col2) ( from (table #tab)! & where_ (#col1 .> #col2) & where_ (#col2 .> 0) )in printSQL query:}sSELECT ("col1" + "col2") AS "col1", "col1" AS "col2" FROM "tab" AS "tab" WHERE (("col1" > "col2") AND ("col2" > 0)) subquery::{let6 query :: Query_ (Public Schema) () (Row Int32 Int32)S query = select Star (from (subquery (select Star (from (table #tab)) `as` #sub)))in printSQL query:}5SELECT * FROM (SELECT * FROM "tab" AS "tab") AS "sub"limits and offsets::{let6 query :: Query_ (Public Schema) () (Row Int32 Int32)V query = select Star (from (table #tab) & limit 100 & offset 2 & limit 50 & offset 2)in printSQL query:}.SELECT * FROM "tab" AS "tab" LIMIT 50 OFFSET 4parameterized query::{let@ query :: Query_ (Public Schema) (Only Int32) (Row Int32 Int32)F query = select Star (from (table #tab) & where_ (#col1 .> param @1))in printSQL query:}:SELECT * FROM "tab" AS "tab" WHERE ("col1" > ($1 :: int4))aggregation query::{ let6 query :: Query_ (Public Schema) () (Row Int64 Int32) query =L select_ ((fromNull 0 (sum_ (All #col2))) `as` #col1 :* #col1 `as` #col2)& ( from (table (#tab `as` #table1)) & groupBy #col1- & having (sum_ (Distinct #col2) .> 1) )in printSQL query:}SELECT COALESCE(sum(ALL "col2"), 0) AS "col1", "col1" AS "col2" FROM "tab" AS "table1" GROUP BY "col1" HAVING (sum(DISTINCT "col2") > 1) sorted query::{let6 query :: Query_ (Public Schema) () (Row Int32 Int32)A query = select Star (from (table #tab) & orderBy [#col1 & Asc])in printSQL query:}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"]E ,"fk_customers" ::: ForeignKey '["customer_id"] "customers" '["id"]D ,"fk_shippers" ::: ForeignKey '["shipper_id"] "shippers" '["id"] ]:}ctype NamesColumns = '["id" ::: 'NoDef :=> 'NotNull 'PGint4, "name" ::: 'NoDef :=> 'NotNull 'PGtext]Dtype CustomersConstraints = '["pk_customers" ::: PrimaryKey '["id"]]Btype ShippersConstraints = '["pk_shippers" ::: PrimaryKey '["id"]]:{type OrdersSchema =@ '[ "orders" ::: 'Table (OrdersConstraints :=> OrdersColumns)C , "customers" ::: 'Table (CustomersConstraints :=> NamesColumns)C , "shippers" ::: 'Table (ShippersConstraints :=> NamesColumns) ]:}:{data Order = Order { price :: Float , customerName :: Text , shipperName :: Text } deriving GHC.Genericinstance SOP.Generic Order"instance SOP.HasDatatypeInfo Order:}:{let0 query :: Query_ (Public OrdersSchema) () Order query = select_ ( #o ! #price `as` #price :*& #c ! #name `as` #customerName :*$ #s ! #name `as` #shipperName )# ( from (table (#orders `as` #o). & innerJoin (table (#customers `as` #c))( (#o ! #customer_id .== #c ! #id)- & innerJoin (table (#shippers `as` #s))* (#o ! #shipper_id .== #s ! #id)) )in printSQL query:}SELECT "o"."price" AS "price", "c"."name" AS "customerName", "s"."name" AS "shipperName" FROM "orders" AS "o" INNER JOIN "customers" AS "c" ON ("o"."customer_id" = "c"."id") INNER JOIN "shippers" AS "s" ON ("o"."shipper_id" = "s"."id") self-join::{let6 query :: Query_ (Public Schema) () (Row Int32 Int32) query = select (#t1 & DotStar)F (from (table (#tab `as` #t1) & crossJoin (table (#tab `as` #t2))))in printSQL query:}9SELECT "t1".* FROM "tab" AS "t1" CROSS JOIN "tab" AS "t2"value queries::{let. query :: Query_ schemas () (Row String Bool) query = values* ("true" `as` #col1 :* true `as` #col2), ["false" `as` #col1 :* false `as` #col2]in printSQL query:}OSELECT * FROM (VALUES (E'true', TRUE), (E'false', FALSE)) AS t ("col1", "col2")set operations::{let6 query :: Query_ (Public Schema) () (Row Int32 Int32)T query = select Star (from (table #tab)) `unionAll` select Star (from (table #tab))in printSQL query:}G(SELECT * FROM "tab" AS "tab") UNION ALL (SELECT * FROM "tab" AS "tab") with queries::{ let6 query :: Query_ (Public Schema) () (Row Int32 Int32) query = with (2 select Star (from (table #tab)) `as` #cte1 :>>0 select Star (from (common #cte1)) `as` #cte2) ) (select Star (from (common #cte2)))in printSQL query:}xWITH "cte1" AS (SELECT * FROM "tab" AS "tab"), "cte2" AS (SELECT * FROM "cte1" AS "cte1") SELECT * FROM "cte2" AS "cte2"window function queries:{let6 query :: Query_ (Public Schema) () (Row Int32 Int64) query = selectW (#col1 & Also (rank `as` #col2 `Over` (partitionBy #col1 & orderBy [#col2 & Asc]))) (from (table #tab))in printSQL query:}lSELECT "col1" AS "col1", rank() OVER (PARTITION BY "col1" ORDER BY "col2" ASC) AS "col2" FROM "tab" AS "tab"correlated subqueries:{ let1 query :: Query_ (Public Schema) () (Only Int32) query =? select (#col1 `as` #fromOnly) (from (table (#tab `as` #t1)) & where_ (exists (/ select Star (from (table (#tab `as` #t2))0 & where_ (#t2 ! #col2 .== #t1 ! #col1)))))in printSQL query:}{SELECT "col1" AS "fromOnly" FROM "tab" AS "t1" WHERE EXISTS (SELECT * FROM "tab" AS "t2" WHERE ("t2"."col2" = "t1"."col1"))squeal-postgresqlVThe process of retrieving or the command to retrieve data from a database is called a . The general  type is parameterized byouter :: FromType) - outer scope for a correlated subquery,commons :: FromType - scope for all  table expressions,schemas :: SchemasType - scope for all s and s,params :: [NullityType] - scope for all SRs,row :: RowType - return type of the .squeal-postgresqlDThe results of two queries can be combined using the set operation  . Duplicate rows are eliminated.squeal-postgresqlDThe results of two queries can be combined using the set operation 2, the disjoint union. Duplicate rows are retained.squeal-postgresqlDThe results of two queries can be combined using the set operation 2, the intersection. Duplicate rows are eliminated.squeal-postgresqlDThe results of two queries can be combined using the set operation 0, the intersection. Duplicate rows are retained.squeal-postgresqlDThe results of two queries can be combined using the set operation 4, the set difference. Duplicate rows are eliminated.squeal-postgresqlDThe results of two queries can be combined using the set operation 2, the set difference. Duplicate rows are retained.squeal-postgresqlthe q in the  command constructs an intermediate virtual table by possibly combining tables, views, eliminating rows, grouping, etc. This table is finally passed on to processing by the select list. The zI determines which columns of the intermediate table are actually output.squeal-postgresqlLike  but takes an  list of s instead of a general z.squeal-postgresqlvAfter the select list has been processed, the result table can be subject to the elimination of duplicate rows using .squeal-postgresqlLike  but takes an  list of s instead of a general z.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]Ylet query = values (1 `as` #a :* "one" `as` #b) [] :: Query outer commons schemas '[] RowprintSQL query2SELECT * FROM (VALUES (1, E'one')) AS t ("a", "b")squeal-postgresqlK computes a row value or set of row values specified by value expressions.squeal-postgresqlA  generates a q 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 K operator to match the left-to-right sequencing of their placement in SQL.squeal-postgresqlA  is an endomorphism of q(s which adds a search condition to the t.squeal-postgresqlA  is a transformation of qs which switches its I from J to K. Use  groupBy Nil/ to perform a "grand total" aggregation query.squeal-postgresqlA  is an endomorphism of q(s which adds a search condition to the v.squeal-postgresqlA  is an endomorphism of qs which adds to the x.squeal-postgresqlAn  is an endomorphism of qs which adds to the y.squeal-postgresqlA real  is a table from the database.squeal-postgresql derives a table from a .squeal-postgresql derives a table from a |.squeal-postgresql0 derives a table from a common table expression.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-postgresqlleft & innerJoin right on&. The joined table is filtered by the on condition.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 rightI. Thus, the joined table always has at least one row for each row in left.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 leftg. This is the converse of a left join: the result table will always have a row for each row in right.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-postgresqlimport Data.Monoid (Sum (..))import Data.Int (Int64):{  let) query :: Query_ schema () (Sum Int64) query = withRecursive* ( values_ ((1 & astype int) `as` #n) `unionAll`" select_ ((#n + 1) `as` #n); (from (common #t) & where_ (#n .< 100)) `as` #t )[ ( select_ (fromNull 0 (sum_ (All #n)) `as` #getSum) (from (common #t) & groupBy Nil)) in printSQL query:}WITH RECURSIVE "t" AS ((SELECT * FROM (VALUES ((1 :: int))) AS t ("n")) UNION ALL (SELECT ("n" + 1) AS "n" FROM "t" AS "t" WHERE ("n" < 100))) SELECT COALESCE(sum(ALL "n"), 0) AS "getSum" FROM "t" AS "t"squeal-postgresql Renders a h.squeal-postgresql Render a e.squeal-postgresql Render a qbsqueal-postgresqlcommon table expressionssqueal-postgresql larger query{squeal-postgresqlA table in the n|squeal-postgresql@ table with F}squeal-postgresql list of D s~squeal-postgresql list of D Essqueal-postgresql/ zsqueal-postgresql selectionsqueal-postgresqlintermediate virtual tablesqueal-postgresql select listsqueal-postgresqlintermediate virtual tablesqueal-postgresql selectionsqueal-postgresqlintermediate virtual tablesqueal-postgresql select listsqueal-postgresqlintermediate virtual tablesqueal-postgresql]When more than one row is specified, all the rows must must have the same number of elementssqueal-postgresqlone row of valuessqueal-postgresqltable referencesqueal-postgresqlfiltering conditionsqueal-postgresqlgrouped columnssqueal-postgresqlhaving conditionsqueal-postgresqllimit parametersqueal-postgresqloffset parametersqueal-postgresqlrightsqueal-postgresqlleftsqueal-postgresqlrightsqueal-postgresqlon conditionsqueal-postgresqlleftsqueal-postgresqlrightsqueal-postgresqlon conditionsqueal-postgresqlleftsqueal-postgresqlrightsqueal-postgresqlon conditionsqueal-postgresqlleftsqueal-postgresqlrightsqueal-postgresqlon conditionsqueal-postgresqlleft?abcdefghijklmnopqrstuvwxyz}~{|?z}~{|abcdqrstuvwxynopklmhijefg!Squeal data manipulation language(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone&'-.7=>?@AHMSUVX_gkqsqueal-postgresqlSpecify additional tables.squeal-postgresqlNo squeal-postgresqlAn 0- list of table expressions, allowing columns from other tables to appear in the WHERE condition. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table in the . list, unless you wish to set up a self-join.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 3 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 (or an alias), and to rows proposed for insertion using the special  #excluded table.squeal-postgresql 9 simply avoids inserting a row as its alternative action.squeal-postgresql h updates the existing row that conflicts with the row proposed for insertion as its alternative action.squeal-postgresqlA > specifies an action to perform upon a constraint violation.  will raise an error.  ! simply avoids inserting a row.  N updates the existing row that conflicts with the row proposed for insertion.squeal-postgresqlA  computes and return value(s) based on each row actually inserted, updated or deleted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. Only rows that were successfully inserted or updated or deleted will be returned. For example, if a row was locked but not updated because an  = condition was not satisfied, the row will not be returned.  {* will return all columns in the row. Use  Returning Nil8 in the common case where no return values are desired.squeal-postgresql is either . or a value, parameterized by an appropriate .squeal-postgresqlUse the  value for a column.squeal-postgresql a value for a column.squeal-postgresqlA  describes what to  a table.squeal-postgresql describes  lists of D   s whose  must match the tables'.squeal-postgresql* describes a subquery that permits use of  s.squeal-postgresql describes a subquery whose  must match the tables'.squeal-postgresqlThe top level  type is parameterized by a schemas x4, against which the query is type-checked, an input  parameters Haskell , and an ouput row Haskell . A top-level  can be run using H, or if parameters = () using T. Generally,  parametersT will be a Haskell tuple or record whose entries may be referenced using positional Rs and rowR will be a Haskell record, whose entries will be targeted using overloaded labels.*:set -XDeriveAnyClass -XDerivingStrategies:{+data Row a b = Row { col1 :: a, col2 :: b } deriving stock (GHC.Generic)6 deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo):}simple insert:[type Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint4, "col2" ::: 'Def :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{let5 manipulation :: Manipulation_ (Public Schema) () () manipulation =G insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))in printSQL manipulation:}6INSERT INTO "tab" ("col1", "col2") VALUES (2, DEFAULT)parameterized insert:`type Columns = '["col1" ::: 'NoDef :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]3type Schema = '["tab" ::: 'Table ('[] :=> Columns)]:{letA manipulation :: Manipulation_ (Public Schema) (Int32, Int32) () manipulation =W insertInto_ #tab (Values_ (Set (param @1) `as` #col1 :* Set (param @2) `as` #col2))in printSQL manipulation:}FINSERT INTO "tab" ("col1", "col2") VALUES (($1 :: int4), ($2 :: int4))returning insert::{let? manipulation :: Manipulation_ (Public Schema) () (Only Int32) manipulation =D insertInto #tab (Values_ (Set 2 `as` #col1 :* Set 3 `as` #col2)): OnConflictDoRaise (Returning (#col1 `as` #fromOnly))in printSQL manipulation:}OINSERT INTO "tab" ("col1", "col2") VALUES (2, 3) RETURNING "col1" AS "fromOnly"upsert:jtype CustomersColumns = '["name" ::: 'NoDef :=> 'NotNull 'PGtext, "email" ::: 'NoDef :=> 'NotNull 'PGtext]9type CustomersConstraints = '["uq" ::: 'Unique '["name"]]\type CustomersSchema = '["customers" ::: 'Table (CustomersConstraints :=> CustomersColumns)]:{ let> manipulation :: Manipulation_ (Public CustomersSchema) () () manipulation = insertInto #customersQ (Values_ (Set "John Smith" `as` #name :* Set "john@smith.com" `as` #email))$ (OnConflict (OnConstraint #uq)\ (DoUpdate (Set (#excluded ! #email <> "; " <> #customers ! #email) `as` #email) [])) (Returning_ Nil)in printSQL manipulation:}INSERT INTO "customers" ("name", "email") VALUES (E'John Smith', E'john@smith.com') ON CONFLICT ON CONSTRAINT "uq" DO UPDATE SET "email" = ("excluded"."email" || (E'; ' || "customers"."email")) query insert::{let5 manipulation :: Manipulation_ (Public Schema) () ()N manipulation = insertInto_ #tab (Subquery (select Star (from (table #tab))))in printSQL manipulation:}.INSERT INTO "tab" SELECT * FROM "tab" AS "tab"update::{let5 manipulation :: Manipulation_ (Public Schema) () ()B manipulation = update_ #tab (Set 2 `as` #col1) (#col1 ./= #col2)in printSQL manipulation:}4UPDATE "tab" SET "col1" = 2 WHERE ("col1" <> "col2")delete::{letD manipulation :: Manipulation_ (Public Schema) () (Row Int32 Int32)K manipulation = deleteFrom #tab NoUsing (#col1 .== #col2) (Returning Star)in printSQL manipulation:}5DELETE FROM "tab" WHERE ("col1" = "col2") RETURNING *delete and using clause::{type Schema3 =' '[ "tab" ::: 'Table ('[] :=> Columns)- , "other_tab" ::: 'Table ('[] :=> Columns)/ , "third_tab" ::: 'Table ('[] :=> Columns) ]:}:{ let6 manipulation :: Manipulation_ (Public Schema3) () () manipulation =H deleteFrom #tab (Using (table #other_tab & also (table #third_tab)))+ ( (#tab ! #col2 .== #other_tab ! #col2)/ .&& (#tab ! #col2 .== #third_tab ! #col2) ) (Returning_ Nil)in printSQL manipulation:}DELETE FROM "tab" USING "other_tab" AS "other_tab", "third_tab" AS "third_tab" WHERE (("tab"."col2" = "other_tab"."col2") AND ("tab"."col2" = "third_tab"."col2"))with manipulation:itype ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]:{letE manipulation :: Manipulation_ (Public ProductsSchema) (Only Day) () manipulation = withQ (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del)Q (insertInto_ #products_deleted (Subquery (select Star (from (common #del)))))in printSQL manipulation:}WITH "del" AS (DELETE FROM "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" SELECT * FROM "del" AS "del"squeal-postgresqlA  is a statement which may modify data in the database, but does not alter its schemas. Examples are inserts, updates and deletes. A  is also considered a % even though it does not modify data. The general  type is parameterized bycommons :: FromType - scope for all  table expressions,schemas :: SchemasType - scope for all s and s,params :: [NullityType] - scope for all SRs,row :: RowType - return type of the .squeal-postgresql a }squeal-postgresql describes a single  list of D   s whose  must match the tables'.squeal-postgresql Convert a  into a .squeal-postgresqlXWhen a table is created, it contains no data. The first thing to do before a database can be of much use is to insert data. Data is conceptually inserted one row at a time. Of course you can also insert more than one row, but there is no way to insert less than one row. Even if you know only some column values, a complete row must be created.squeal-postgresqlLike  but with  and no .squeal-postgresqlAn ] command changes the values of the specified columns in all rows that satisfy the condition.squeal-postgresqlUpdate a row returning .squeal-postgresqlDelete rows from a table.squeal-postgresqlDelete rows returning .squeal-postgresql Render a squeal-postgresql Render a .squeal-postgresqlWHERE ssqueal-postgresqltable to updatesqueal-postgresql%modified values to replace old valuessqueal-postgresql0condition under which to perform update on a rowsqueal-postgresqlresults to returnsqueal-postgresqltable to updatesqueal-postgresql%modified values to replace old valuessqueal-postgresql0condition under which to perform update on a rowsqueal-postgresqltable to delete fromsqueal-postgresql%condition under which to delete a rowsqueal-postgresqlresults to returnsqueal-postgresqltable to delete fromsqueal-postgresql%condition under which to delete a row!!Subquery expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.SUVXOsqueal-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 L0 if no comparison with a subquery row returns &, and at least one comparison returns L.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 T if no true result is found (including the case where the subquery returns no rows).;printSQL $ subAny "foo" like (values_ ("foobar" `as` #foo))C(E'foo' LIKE ANY (SELECT * FROM (VALUES (E'foobar')) AS t ("foo")))squeal-postgresqlThe result is T if the left-hand expression's result is equal to any of the right-hand expressions.*printSQL $ true `in_` [true, false, null_]TRUE IN (TRUE, FALSE, NULL)squeal-postgresqlThe result is X if the left-hand expression's result is not equal to any of the right-hand expressions.&printSQL $ true `notIn` [false, null_]TRUE NOT IN (FALSE, NULL)squeal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquerysqueal-postgresql expressionsqueal-postgresqloperatorsqueal-postgresqlsubquerysqueal-postgresql expressionsqueal-postgresql expressionSet returning functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone -.>HSUVXkޕsqueal-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 with 1 argument.squeal-postgresqlAEscape hatch for a set returning function with multiple argument.squeal-postgresql generateSeries (start *: stop)"Generate a series of values, from start to stop with a step size of onesqueal-postgresql &generateSeries (start :* stop *: step)"Generate a series of values, from start to stop with a step size of stepsqueal-postgresql &generateSeries (start :* stop *: step)"Generate a series of values, from start to stop with a step size of stepsqueal-postgresqlinputssqueal-postgresqloutputsqueal-postgresqlinputsqueal-postgresqloutputsqueal-postgresqlset returning functionsqueal-postgresqlset returning functionsqueal-postgresqlset returning functionsqueal-postgresqlset returning functionsqueal-postgresqlset returning functionArray and composite functions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone.=>?@AHSUVXeksqueal-postgresql%printSQL $ array [null_, false, true]ARRAY[NULL, FALSE, TRUE]squeal-postgresql,construct a 1-dimensional fixed length array*printSQL $ array1 (null_ :* false *: true)ARRAY[NULL, FALSE, TRUE]%:type array1 (null_ :* false *: true) array1 (null_ :* false *: true) :: Expression outer commons grp schemas params from0 (null ('PGfixarray '[3] ('Null 'PGbool)))squeal-postgresql,construct a 2-dimensional fixed length arrayHprintSQL $ array2 ((null_ :* false *: true) *: (false :* null_ *: true))/ARRAY[[NULL, FALSE, TRUE], [FALSE, NULL, TRUE]]C:type array2 ((null_ :* false *: true) *: (false :* null_ *: true)) =array2 ((null_ :* false *: true) *: (false :* null_ *: true)) :: Expression outer commons grp schemas 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 Expand an array to a set of rows 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 ]:}vlet i = row (0 `as` #real :* 1 `as` #imaginary) :: Expression outer commons grp schemas params from ('NotNull Complex) printSQL i ROW(0, 1) squeal-postgresql:{type Complex = 'PGcomposite' '[ "real" ::: 'NotNull 'PGfloat8) , "imaginary" ::: 'NotNull 'PGfloat8 ]/type Schema = '["complex" ::: 'Typedef Complex]:}zlet i = row (0 `as` #real :* 1 `as` #imaginary) :: Expression outer '[] grp (Public Schema) from params ('NotNull Complex)(printSQL $ i & field #complex #imaginary"(ROW(0, 1)::"complex")."imaginary"squeal-postgresqlarray elementssqueal-postgresqlindex squeal-postgresql1zero or more expressions for the row field values squeal-postgresqlrow typesqueal-postgresql field name    &Json and Jsonb functions and operators(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone&'-.=>?HSUVXekx- squeal-postgresqlBuild rows from Json types. squeal-postgresqlBuild rows from Json types. squeal-postgresqlBuilds a possibly-heterogeneously-typed JSON object out of a variadic argument list. The elements of the argument list must alternate between text and values.squeal-postgresql8Get JSON value (object field or array element) at a key.squeal-postgresqlAGet JSON value (object field or array element) at a key, as text.squeal-postgresql#Get JSON value at a specified path.squeal-postgresql+Get JSON value at a specified path as text.squeal-postgresql?Does the string exist as a top-level key within the JSON value?squeal-postgresql6Do any of these array strings exist as top-level keys?squeal-postgresql6Do all of these array strings exist as top-level keys?squeal-postgresqlDDelete a key or keys from a JSON object, or remove an array element.If the right operand is text w: Delete key / value pair or string element from left operand. Key / value pairs are matched based on their key value, text[] : Delete multiple key / value pairs or string elements from left operand. Key / value pairs are matched based on their key value, integer : Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.squeal-postgresqlhDelete the field or element with specified path (for JSON arrays, negative integers count from the end)squeal-postgresqlReturns the value as json. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json value.squeal-postgresqlReturns the value as jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid jsonb value.squeal-postgresqlgReturns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays.squeal-postgresql!Returns the row as a JSON object.squeal-postgresqlTBuilds a possibly-heterogeneously-typed JSON array out of a variadic argument list.squeal-postgresql]Builds a possibly-heterogeneously-typed (binary) JSON array out of a variadic argument list.squeal-postgresqlBuilds a JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair. squeal-postgresqlBuilds a binary JSON object out of a text array. The array must have two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.!squeal-postgresqlThis is an alternate form of k that takes two arrays; one for keys and one for values, that are zipped pairwise to create a JSON object."squeal-postgresqlThis is an alternate form of  s that takes two arrays; one for keys and one for values, that are zipped pairwise to create a binary JSON object.#squeal-postgresql;Returns the number of elements in the outermost JSON array.$squeal-postgresqlBReturns the number of elements in the outermost binary JSON array.%squeal-postgresqlReturns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.&squeal-postgresqlReturns the type of the outermost binary JSON value as a text string. Possible types are object, array, string, number, boolean, and null.'squeal-postgresqllReturns its argument with all object fields that have null values omitted. Other null values are untouched.(squeal-postgresqllReturns its argument with all object fields that have null values omitted. Other null values are untouched.)squeal-postgresql . jsonbSet target path new_value create_missing?Returns target with the section designated by path replaced by  new_value , or with  new_value added if create_missing is U and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.*squeal-postgresql / jsonbInsert target path new_value insert_afterReturns target with  new_valueF inserted. If target section designated by path is in a JSONB array,  new_value- will be inserted before target or after if  insert_after is U=. If target section designated by path is in JSONB object,  new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.+squeal-postgresql+Returns its argument as indented JSON text.,squeal-postgresql@Expands the outermost JSON object into a set of key/value pairs._printSQL (select Star (from (jsonEach (literal (Json (object ["a" .= "foo", "b" .= "bar"])))))):SELECT * FROM json_each(('{"a":"foo","b":"bar"}' :: json))-squeal-postgresqlGExpands the outermost binary JSON object into a set of key/value pairs.aprintSQL (select Star (from (jsonbEach (literal (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))))<SELECT * FROM jsonb_each(('{"a":"foo","b":"bar"}' :: jsonb)).squeal-postgresql@Expands the outermost JSON object into a set of key/value pairs.cprintSQL (select Star (from (jsonEachText (literal (Json (object ["a" .= "foo", "b" .= "bar"]))))))?SELECT * FROM json_each_text(('{"a":"foo","b":"bar"}' :: json))/squeal-postgresqlGExpands the outermost binary JSON object into a set of key/value pairs.eprintSQL (select Star (from (jsonbEachText (literal (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))))ASELECT * FROM jsonb_each_text(('{"a":"foo","b":"bar"}' :: jsonb))0squeal-postgresql1Returns set of keys in the outermost JSON object.PprintSQL (jsonObjectKeys (literal (Json (object ["a" .= "foo", "b" .= "bar"]))))3json_object_keys(('{"a":"foo","b":"bar"}' :: json))1squeal-postgresql1Returns set of keys in the outermost JSON object.RprintSQL (jsonbObjectKeys (literal (Jsonb (object ["a" .= "foo", "b" .= "bar"]))))5jsonb_object_keys(('{"a":"foo","b":"bar"}' :: jsonb))2squeal-postgresqleExpands the JSON expression to a row whose columns match the record type defined by the given table.3squeal-postgresqllExpands the binary JSON expression to a row whose columns match the record type defined by the given table.4squeal-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.5squeal-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.6squeal-postgresql.Builds an arbitrary record from a JSON object.7squeal-postgresql5Builds an arbitrary record from a binary JSON object.8squeal-postgresql@Builds an arbitrary set of records from a JSON array of objects.9squeal-postgresqlGBuilds an arbitrary set of records from a binary JSON array of objects. squeal-postgresql json typesqueal-postgresqlrow type squeal-postgresqlrow typesqueal-postgresql json type/    !"#$%&'()*+,-./0123456789/  !"#$%&'()*+,-./01 2345 6789 88889 9 9 66Squeal data definition language(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone&',-.7=>?@AHMSUVX_k;<squeal-postgresql<s are used in [ commands.?squeal-postgresqlLift G to a fieldAsqueal-postgresqlAn A8 describes the alteration to perform on a single column.Dsqueal-postgresqlAn D is either NULL or has DEFAULT.Esqueal-postgresqlES adds a new column, initially filled with whatever default value is given or with NULL.:{ let definition :: DefinitionX '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint40 , "col2" ::: 'Def :=> 'Null 'PGtext ])]]S definition = alterTable #tab (addColumn #col2 (text & nullable & default_ "foo"))in printSQL definition:}=ALTER TABLE "tab" ADD COLUMN "col2" text NULL DEFAULT E'foo';:{ let definition :: DefinitionX '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint42 , "col2" ::: 'NoDef :=> 'Null 'PGtext ])]]B definition = alterTable #tab (addColumn #col2 (text & nullable))in printSQL definition:}.ALTER TABLE "tab" ADD COLUMN "col2" text NULL;Fsqueal-postgresqlAn F@ describes the alteration to perform on the columns of a table.Isqueal-postgresql Analagous to M there is also IA which is invoked when a referenced column is changed (updated).Jsqueal-postgresql[if any referencing rows has not changed when the constraint is checked, an error is raisedKsqueal-postgresql#prevents update of a referenced rowLsqueal-postgresql\the updated values of the referenced column(s) should be copied into the referencing row(s)Msqueal-postgresqlM= indicates what to do with rows that reference a deleted row.Nsqueal-postgresqlWif any referencing rows still exist when the constraint is checked, an error is raisedOsqueal-postgresql%prevents deletion of a referenced rowPsqueal-postgresqlospecifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as wellQsqueal-postgresqlHA constraint synonym between types involved in a foreign key constraint.Rsqueal-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.Usqueal-postgresqlA UB is a statement that changes the schemas of the database, like a [, b, or c command. Us may be composed using the  operator.Xsqueal-postgresqlA E without input or output can be run as a statement along with other Us, by embedding it using X.Ysqueal-postgresqlY 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.Zsqueal-postgresqlIdempotent version of Y. [squeal-postgresql[ adds a table to the schema.:set -XOverloadedLabels:{type Table = '[] :=>% '[ "a" ::: 'NoDef :=> 'Null 'PGint4) , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]:}:{letE setup :: Definition (Public '[]) (Public '["tab" ::: 'Table Table]) setup = createTable #tab7 (nullable int `as` #a :* nullable real `as` #b) Nilin printSQL setup:}1CREATE TABLE "tab" ("a" int NULL, "b" real NULL);\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. \H fixes this. Interestingly, this property makes it an idempotent in the  of Us.*: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-postgresqlA ] constraint is the most generic s type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.:{type Schema = '[A "tab" ::: 'Table ('[ "inequality" ::: 'Check '["a","b"]] :=> '[( "a" ::: 'NoDef :=> 'NotNull 'PGint4,' "b" ::: 'NoDef :=> 'NotNull 'PGint4 ])]:}:{let7 definition :: Definition (Public '[]) (Public Schema) definition = createTable #tab$ ( (int & notNullable) `as` #a :*# (int & notNullable) `as` #b )4 ( check (#a :* #b) (#a .> #b) `as` #inequality ):}printSQL definitioneCREATE TABLE "tab" ("a" int NOT NULL, "b" int NOT NULL, CONSTRAINT "inequality" CHECK (("a" > "b")));^squeal-postgresqlA ^y constraint ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table.:{type Schema = '[> "tab" ::: 'Table( '[ "uq_a_b" ::: 'Unique '["a","b"]] :=> '[% "a" ::: 'NoDef :=> 'Null 'PGint4,$ "b" ::: 'NoDef :=> 'Null 'PGint4 ])]:}:{let7 definition :: Definition (Public '[]) (Public Schema) definition = createTable #tab! ( (int & nullable) `as` #a :* (int & nullable) `as` #b )& ( unique (#a :* #b) `as` #uq_a_b ):}printSQL definitionWCREATE TABLE "tab" ("a" int NULL, "b" int NULL, CONSTRAINT "uq_a_b" UNIQUE ("a", "b"));_squeal-postgresqlA _ constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.:{type Schema = '[> "tab" ::: 'Table ('[ "pk_id" ::: 'PrimaryKey '["id"]] :=> '[' "id" ::: 'Def :=> 'NotNull 'PGint4,* "name" ::: 'NoDef :=> 'NotNull 'PGtext ])]:}:{let7 definition :: Definition (Public '[]) (Public Schema) definition = createTable #tab ( serial `as` #id :*' (text & notNullable) `as` #name )" ( primaryKey #id `as` #pk_id ):}printSQL definition^CREATE TABLE "tab" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_id" PRIMARY KEY ("id"));`squeal-postgresqlA ` specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.:{ type Schema = '[ "users" ::: 'Table (2 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>, '[ "id" ::: 'Def :=> 'NotNull 'PGint40 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]) , "emails" ::: 'Table (. '[ "pk_emails" ::: 'PrimaryKey '["id"]C , "fk_user_id" ::: 'ForeignKey '["user_id"] "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; OnDeleteCascade OnUpdateCascade `as` #fk_user_id )in printSQL setup:}cCREATE TABLE "users" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_users" PRIMARY KEY ("id"));CREATE TABLE "emails" ("id" serial, "user_id" int NOT NULL, "email" text NULL, CONSTRAINT "pk_emails" PRIMARY KEY ("id"), CONSTRAINT "fk_user_id" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE);A `$ can even be a table self-reference.:{ type Schema = '[ "employees" ::: 'Table (9 '[ "employees_pk" ::: 'PrimaryKey '["id"]V , "employees_employer_fk" ::: 'ForeignKey '["employer_id"] "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 #idF OnDeleteCascade OnUpdateCascade `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);asqueal-postgresql:{leta definition :: Definition '["muh_schema" ::: schema, "public" ::: public] '["public" ::: public]% definition = dropSchema #muh_schema:}printSQL definitionDROP SCHEMA "muh_schema";bsqueal-postgresqlb! removes a table from the schema.:{letS definition :: Definition '["public" ::: '["muh_table" ::: 'Table t]] (Public '[])# definition = dropTable #muh_table:}printSQL definitionDROP TABLE "muh_table";csqueal-postgresqlc3 changes the definition of a table from the schema.dsqueal-postgresqld- changes the name of a table from the schema.%printSQL $ alterTableRename #foo #bar"ALTER TABLE "foo" RENAME TO "bar";esqueal-postgresqlAn e adds a table constraint.:{let definition :: DefinitionZ '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]x '["public" ::: '["tab" ::: 'Table ('["positive" ::: 'Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]Q definition = alterTable #tab (addConstraint #positive (check #col (#col .> 0)))in printSQL definition:}@ALTER TABLE "tab" ADD CONSTRAINT "positive" CHECK (("col" > 0));fsqueal-postgresqlA f drops a table constraint.:{let definition :: Definitionw '["public" ::: '["tab" ::: 'Table ('["positive" ::: Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]Z '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]9 definition = alterTable #tab (dropConstraint #positive)in printSQL definition:}-ALTER TABLE "tab" DROP CONSTRAINT "positive";gsqueal-postgresqlA g removes a column. Whatever data was in the column disappears. Table constraints involving the column are dropped, too. However, if the column is referenced by a foreign key constraint of another table, PostgreSQL will not silently drop that constraint.:{ let definition :: Definition. '["public" ::: '["tab" ::: 'Table ('[] :=>- '[ "col1" ::: 'NoDef :=> 'Null 'PGint42 , "col2" ::: 'NoDef :=> 'Null 'PGtext ])]]X '["public" ::: '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]]1 definition = alterTable #tab (dropColumn #col2)in printSQL definition:}%ALTER TABLE "tab" DROP COLUMN "col2";hsqueal-postgresqlA h renames a column.:{let definition :: DefinitionW '["public" ::: '["tab" ::: 'Table ('[] :=> '["foo" ::: 'NoDef :=> 'Null 'PGint4])]]W '["public" ::: '["tab" ::: 'Table ('[] :=> '["bar" ::: 'NoDef :=> 'Null 'PGint4])]]7 definition = alterTable #tab (renameColumn #foo #bar)in printSQL definition:}/ALTER TABLE "tab" RENAME COLUMN "foo" TO "bar";isqueal-postgresqlAn i alters a single column.jsqueal-postgresqlA j sets a new default for a column. Note that this doesn't affect any existing rows in the table, it just changes the default for future insert and update commands.:{let definition :: DefinitionW '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]U '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]]@ definition = alterTable #tab (alterColumn #col (setDefault 5))in printSQL definition:}3ALTER TABLE "tab" ALTER COLUMN "col" SET DEFAULT 5;ksqueal-postgresqlA k( removes any default value for a column.:{let definition :: DefinitionU '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]]W '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]= definition = alterTable #tab (alterColumn #col dropDefault)in printSQL definition:}2ALTER TABLE "tab" ALTER COLUMN "col" DROP DEFAULT;lsqueal-postgresqlA l adds a NOT NULL constraint to a column. The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.:{let definition :: DefinitionW '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]Z '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]< definition = alterTable #tab (alterColumn #col setNotNull)in printSQL definition:}2ALTER TABLE "tab" ALTER COLUMN "col" SET NOT NULL;msqueal-postgresqlA m drops a NOT NULL constraint from a column.:{let definition :: DefinitionZ '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]W '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]]= definition = alterTable #tab (alterColumn #col dropNotNull)in printSQL definition:}3ALTER TABLE "tab" ALTER COLUMN "col" DROP NOT NULL;nsqueal-postgresqlAn n converts a column to a different data type. This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast.:{ let definition :: DefinitionZ '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]]] '["public" ::: '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGnumeric])]] definition =J alterTable #tab (alterColumn #col (alterType (numeric & notNullable)))in printSQL definition:};ALTER TABLE "tab" ALTER COLUMN "col" TYPE numeric NOT NULL;osqueal-postgresqlCreate a view. >>> type ABC = '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4] >>> type BC = '["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4] >>> :{ let definition :: Definition '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC)]] '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc" ::: 'View BC]] definition = createView  bc (select_ (b :* c) (from (table gabc))) in printSQL definition :} CREATE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";psqueal-postgresql Drop a view.:{ let definition :: Definition '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])J , "bc" ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]] '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]] definition = dropView #bcin printSQL definition:}DROP VIEW "bc";qsqueal-postgresql'Enumerated types are created using the q 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');rsqueal-postgresqlGEnumerated types can also be generated from a Haskell type, for example:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo Schwarma:{letq createSchwarma :: Definition (Public '[]) '["public" ::: '["schwarma" ::: 'Typedef (PG (Enumerated Schwarma))]]9 createSchwarma = createTypeEnumFrom @Schwarma #schwarmain printSQL createSchwarma:};CREATE TYPE "schwarma" AS ENUM ('Beef', 'Lamb', 'Chicken');ssqueal-postgresqlsg creates a composite type. The composite type is specified by a list of attribute names and data types.:{type PGcomplex = 'PGcomposite' '[ "real" ::: 'NotNull 'PGfloat8) , "imaginary" ::: 'NotNull 'PGfloat8 ]:}:{letV setup :: Definition (Public '[]) '["public" ::: '["complex" ::: 'Typedef PGcomplex]]& setup = createTypeComposite #complex1 (float8 `as` #real :* float8 `as` #imaginary)in printSQL setup:}=CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);tsqueal-postgresqlFComposite types can also be generated from a Haskell type, for exampleQdata Complex = Complex {real :: Double, imaginary :: Double} deriving GHC.Genericinstance SOP.Generic Complex$instance SOP.HasDatatypeInfo Complex@type Schema = '["complex" ::: 'Typedef (PG (Composite Complex))]:{let: createComplex :: Definition (Public '[]) (Public Schema); createComplex = createTypeCompositeFrom @Complex #complexin printSQL createComplex:}=CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);usqueal-postgresqlux creates a new domain. A domain is essentially a data type with constraints (restrictions on the allowed set of values).Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same ]v constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.:{letY createPositive :: Definition (Public '[]) (Public '["positive" ::: 'Typedef 'PGfloat4])U createPositive = createDomain #positive real (#value .> 0 .&& (#value & isNotNull))in printSQL createPositive:}QCREATE DOMAIN "positive" AS real CHECK ((("value" > 0) AND "value" IS NOT NULL));vsqueal-postgresql Drop a type.:data Schwarma = Beef | Lamb | Chicken deriving GHC.Genericinstance SOP.Generic Schwarma%instance SOP.HasDatatypeInfo Schwarma~printSQL (dropType #schwarma :: Definition '["public" ::: '["schwarma" ::: 'Typedef (PG (Enumerated Schwarma))]] (Public '[]))DROP TYPE "schwarma";wsqueal-postgresqlused in [/ commands as a column constraint to note that NULL may be present in a columnxsqueal-postgresqlused in [, commands as a column constraint to ensure NULL is not present in a columnysqueal-postgresqlused in [2 commands as a column constraint to give a defaultzsqueal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type {squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type |squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type }squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type ~squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlgnot a true type, but merely a notational convenience for creating unique identifier columns with type squeal-postgresqlRender M.squeal-postgresqlRender I.Esqueal-postgresql column to addsqueal-postgresqltype of the new columnXsqueal-postgresqlno input or output[squeal-postgresqlthe name of the table to addsqueal-postgresql%the names and datatype of each columnsqueal-postgresql(constraints that must hold for the table\squeal-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 table]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 updatedbsqueal-postgresqltable to removecsqueal-postgresqltable to altersqueal-postgresqlalteration to performdsqueal-postgresqltable to renamesqueal-postgresqlwhat to rename itesqueal-postgresqlconstraint to addfsqueal-postgresqlconstraint to dropgsqueal-postgresqlcolumn to removehsqueal-postgresqlcolumn to renamesqueal-postgresqlwhat to rename the columnisqueal-postgresqlcolumn to altersqueal-postgresqlalteration to performjsqueal-postgresqldefault value to setosqueal-postgresqlthe name of the view to addsqueal-postgresqlquerypsqueal-postgresqlview to removeqsqueal-postgresql(name of the user defined enumerated typesqueal-postgresqllabels of the enumerated typersqueal-postgresql(name of the user defined enumerated typessqueal-postgresql'name of the user defined composite typesqueal-postgresql&list of attribute names and data typestsqueal-postgresql'name of the user defined composite typevsqueal-postgresqlname of the user defined typeE<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~EUVWXYZ[\oqrst?@uRST]^_`QMNOPIJKLabpvcdFGHefDEghiABCjklmn<=>wxyz{|}~Binary encoding and decoding(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone&'-.45678=>?@ACHUVX_k squeal-postgresqlA is a 1-tuple type, useful for encoding a single parameter with ! or decoding a single value with .import Data.TextDtoParams @(Only (Maybe Text)) @'[ 'Null 'PGtext] (Only (Just "foo"))K (Just "foo") :* NilUfromRow @'["fromOnly" ::: 'Null 'PGtext] @(Only (Maybe Text)) (K (Just "bar") :* Nil)$Right (Only {fromOnly = Just "bar"})squeal-postgresqlA D constraint generically sequences the parsings of the columns of a  into the fields of a record N provided they have the same field names. You should not define instances of . Instead define VW and VX" instances which in turn provide  instances.squeal-postgresql :set -XOverloadedStringsimport Data.Text<newtype UserId = UserId { getUserId :: Int16 } deriving ShowQinstance FromValue 'PGint2 UserId where fromValue = UserId <$> fromValue @'PGint2`data UserRow = UserRow { userId :: UserId, userName :: Maybe Text } deriving (Show, GHC.Generic)instance Generic UserRow instance HasDatatypeInfo UserRowJtype User = '["userId" ::: 'NotNull 'PGint2, "userName" ::: 'Null 'PGtext]LfromRow @User @UserRow (K (Just "\NUL\SOH") :* K (Just "bloodninja") :* Nil)ORight (UserRow {userId = UserId {getUserId = 1}, userName = Just "bloodninja"})squeal-postgresqlA * constraint gives a decoding to a Haskell c from the binary format of a PostgreSQL fixed-length array. You should not define instances for ", just use the provided instances.squeal-postgresqlA  constraint lifts the  parser to a decoding of a (Symbol, NullityType) to a  , decoding s to (s. You should not define instances for ", just use the provided instances.squeal-postgresqlA C constraint gives a parser from the binary format of a PostgreSQL  into a Haskell .squeal-postgresql0newtype Id = Id { getId :: Int16 } deriving ShowIinstance FromValue 'PGint2 Id where fromValue = Id <$> fromValue @'PGint2squeal-postgresqlA 3 constraint generically sequences the encodings of 2s of the fields of a tuple or record to a row of 's. You should not define instances of . Instead define VW" instances which in turn provide  instances.squeal-postgresql1type Params = '[ 'NotNull 'PGbool, 'Null 'PGint2]RtoParams @(Bool, Maybe Int16) @'[ 'NotNull 'PGbool, 'Null 'PGint2] (False, Just 0)-K (Just "\NUL") :* K (Just "\NUL\NUL") :* Nil:set -XDeriveGenericHdata Tuple = Tuple { p1 :: Bool, p2 :: Maybe Int16} deriving GHC.Genericinstance Generic Tuple.toParams @Tuple @Params (Tuple False (Just 0))-K (Just "\NUL") :* K (Just "\NUL\NUL") :* Nilsqueal-postgresqlA + constraint gives an encoding of a Haskell c into 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 an encoding of a (Symbol, Type) to a (Symbol, NullityType) , encoding s to (s. You should not define instances for ", just use the provided instances.squeal-postgresqlA + constraint gives an encoding of a Haskell . into into the binary format of a PostgreSQL .squeal-postgresqlLifts a  constraint to a field.squeal-postgresqlThe object identifier of a .:set -XTypeApplications oid @'PGbool16squeal-postgresqlA + constraint gives an encoding of a Haskell . into into the binary format of a PostgreSQL .squeal-postgresql#:set -XTypeApplications -XDataKindstoParam @Bool @'PGbool FalseK "\NUL"toParam @Int16 @'PGint2 0 K "\NUL\NUL"toParam @Int32 @'PGint4 0K "\NUL\NUL\NUL\NUL":set -XMultiParamTypeClasses0newtype Id = Id { getId :: Int16 } deriving Show;instance ToParam Id 'PGint2 where toParam = toParam . getIdtoParam @Id @'PGint2 (Id 1) K "\NUL\SOH"PQ monad(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone-./8=>?@ACHSUVXk^ 5squeal-postgresql s that can be thrown by Squeal.9squeal-postgresqlthe state of LibPQ<squeal-postgresql Ehttps://www.postgresql.org/docs/current/static/errcodes-appendix.html>squeal-postgresql> is an mtl style constraint, similar to YZ , for using [\ to ? runs a # with params from a type with a  constraint. It calls  # and doesn't afraid of anything.@ is like ? for a returning-free statement.A is like ? for a parameter-free statement.B is like A for a returning-free statement.C is like ? for query statements.D is like C for a parameter-free statement.E3 has the same type signature as a composition of   and ?@ but provides an optimization by preparing the statement with   and then traversing a   container with 9. The temporary prepared statement is then deallocated.F is a flipped EG is like E but works on - containers for a returning-free statement.H is a flipped G.I lets you lift actions from [\- that require a connection into your monad.5To define an instance, you can minimally define only ?, E, G and I,. Monad transformers get a default instance.Jsqueal-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, JA is a class for indexed monad transformers that support running Us using P.Ksqueal-postgresqlindexed analog of Lsqueal-postgresqlindexed analog of Msqueal-postgresqlindexed analog of Nsqueal-postgresqlindexed analog of flipped Osqueal-postgresqlindexed analog of Psqueal-postgresqlRun a U with ."It should be functorial in effect.define id = return () Sdefine (statement1 >>> statement2) = define statement1 & pqThen (define statement2)Qsqueal-postgresqlKWe keep track of the schema via an Atkey indexed state monad transformer, Q.Tsqueal-postgresql.Makes a new connection to the database server.bThis function opens a new database connection using the parameters taken from the string conninfo.The passed string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., ' and .ETo specify the schema you wish to connect with, use type application.:set -XDataKinds:set -XPolyKinds:set -XTypeOperatorsHtype Schema = '["tab" ::: '[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint2]]:set -XTypeApplications:set -XOverloadedStringsEconn <- connectdb @Schema "host=localhost port=5432 dbname=exampledb"^Note that, for now, squeal doesn't offer any protection from connecting with the wrong schema!Usqueal-postgresql$Closes the connection to the server.Vsqueal-postgresqlDo T and U before and after a computation.Wsqueal-postgresqlSafely W to a smaller schema.Xsqueal-postgresqlRun a Q and keep the result and the .Ysqueal-postgresql Execute a Q% and discard the result but keep the .Zsqueal-postgresql Evaluate a Q and discard the  but keep the result.[squeal-postgresql5Get a row corresponding to a given row number from a  <, throwing an exception if the row number is out of bounds.\squeal-postgresql:Intended to be used for unfolding in streaming libraries, \8 takes a total number of rows (which can be found with ` ) and a  2 and given a row number if it's too large returns >, otherwise returning the row along with the next row number.]squeal-postgresqlGet all rows from a  .^squeal-postgresql%Get the first row if possible from a  ._squeal-postgresqlLifts actions on results from LibPQ.`squeal-postgresql8Returns the number of rows (tuples) in the query result.asqueal-postgresql)Returns the result status of the command.bsqueal-postgresqlUReturns the error message most recently generated by an operation on the connection.csqueal-postgresqlRReturns the error code most recently generated by an operation on the connection. Ehttps://www.postgresql.org/docs/current/static/errcodes-appendix.htmldsqueal-postgresql Check if a  's status is either  or  otherwise  a 6.esqueal-postgresqlCatch 5s.fsqueal-postgresqlHandle 5s.gsqueal-postgresql return a 5 or a result.?squeal-postgresql,  or @squeal-postgresql,  or Csqueal-postgresql and friendsDsqueal-postgresql and friendsEsqueal-postgresql, , or  , and friendsFsqueal-postgresql,  or Gsqueal-postgresql,  or Hsqueal-postgresql,  or Tsqueal-postgresqlconninfo[squeal-postgresql row numbersqueal-postgresqlresult\squeal-postgresqltotal number of rowssqueal-postgresqlresultsqueal-postgresql row number]squeal-postgresqlresult^squeal-postgresqlresultesqueal-postgresqlhandlerfsqueal-postgresqlhandlerA  57869:;<=>?ECDA@BFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefgATUVWQRSXYZJKLMNOP>?ECDA@BFGHI `[]\^_ abc57869:;<=defgConnection pools(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone-./4=>?@AHSVXsqueal-postgresqlCreate a striped pool of connections. Although the garbage collector will destroy all idle connections when the pool is garbage collected it's recommended to manually Y when you're done with the pool so that the connections are freed up as soon as possible.squeal-postgresql%Temporarily take a connection from a B, perform an action with it, and return it to the pool afterwards.'If the pool has an idle connection available, it is used immediately. Otherwise, if the maximum number of connections has not yet been reached, a new connection is created and used. If the maximum number of connections has been reached, this function blocks until a connection becomes available.squeal-postgresqlvDestroy all connections in all stripes in the pool. Note that this will ignore any exceptions in the destroy function.This function is useful when you detect that all connections in the pool are broken. For example after a database has been restarted all connections opened before the restart will be broken. In that case it's better to close those connections so that U 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-postgresql[The number of stripes (distinct sub-pools) to maintain. The smallest acceptable value is 1.squeal-postgresqlAmount of time for which an unused connection is kept open. The smallest acceptable value is 0.5 seconds. The elapsed time before destroying a connection may be a little longer than requested, as the reaper thread wakes at 1-second intervals.squeal-postgresqlMaximum number of connections to keep open per stripe. The smallest acceptable value is 1. Requests for connections will block if this limit is reached on a single stripe, even if other stripes have idle connections available.squeal-postgresqlpoolsqueal-postgresqlsessionsqueal-postgresqlpoolLiteral expressions(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone =>?SX_ksqueal-postgresqlThe 7 class allows embedding a Haskell value directly as an  using .printSQL (literal 'a')E'a' printSQL (literal (1 :: Double))1.0*printSQL (literal (Json [1 :: Double, 2]))('[1.0,2.0]' :: json)"printSQL (literal (Enumerated GT))'GT'#Squeal transaction control language(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNone-.>H_1squeal-postgresqlThe D transaction property has no effect unless the transaction is also  and . When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a j transaction and without any risk of contributing to or being canceled by a serialization failure. This 4 is well suited for long-running reports or backups.squeal-postgresqlBThe transaction access mode determines whether the transaction is  or . ' is the default. When a transaction is /, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and  COPY FROMB if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTEZ if the command they would execute is among those listed. This is a high-level notion of * that does not prevent all writes to disk.squeal-postgresqlSThe SQL standard defines four levels of transaction isolation. The most strict is h, which is defined by the standard in a paragraph which says that any concurrent execution of a set of ; transactions is guaranteed to produce the same effect as running them one at a time in some order. The other three levels are defined in terms of phenomena, resulting from interaction between concurrent transactions, which must not occur at each level. The phenomena which are prohibited at various levels are: Dirty readK: A transaction reads data written by a concurrent uncommitted transaction.Nonrepeatable read: A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read). Phantom read: A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.Serialization anomaly: The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e. PostgreSQL's  mode behaves like . This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.squeal-postgresqlDirty read is not possible. Nonrepeatable read is not possible. Phantom read is not possible. Serialization anomaly is not possible.squeal-postgresqlDirty read is not possible. Nonrepeatable read is not possible. Phantom read is not possible. Serialization anomaly is possible.squeal-postgresql{Dirty read is not possible. Nonrepeatable read is possible. Phantom read is possible. Serialization anomaly is possible.squeal-postgresql{Dirty read is not possible. Nonrepeatable read is possible. Phantom read is possible. Serialization anomaly is possible.squeal-postgresql>The available transaction characteristics are the transaction , the transaction  ( or  ), and the .squeal-postgresqlRun a computation ; first , then run the computation,  & and rethrow the exception, otherwise  and  the result.squeal-postgresqlRun a computation , in .squeal-postgresql a computation;first ,then  the computation,*if it raises a serialization failure then  and restart the transaction,&if it raises any other exception then  and rethrow the exception, otherwise  and  the result.squeal-postgresqlRun a computation ; Like  but always , useful in testing.squeal-postgresqlRun a computation  in . squeal-postgresqlBEGIN a transaction.squeal-postgresqlCOMMIT a transaction.squeal-postgresqlROLLBACK a transaction.squeal-postgresql with a  ,   and  .squeal-postgresql with a  ,   and  @. This mode is well suited for long-running reports or backups.squeal-postgresql Render an .squeal-postgresql Render an .squeal-postgresql Render a .squeal-postgresql Render a .squeal-postgresqlrun inside a transactionsqueal-postgresqlrun inside a transactionsqueal-postgresqlrun inside a transactionsqueal-postgresql#run inside an ephemeral transactionsqueal-postgresql#run inside an ephemeral transactionSqueal migrations(c) Eitan Chatav, 2017eitan@morphism.tech experimentalNone&'-.7=>?HSUVX_ekqusqueal-postgresqlThe  for a Squeal migration.squeal-postgresqlr turns an indexed monad transformer and the monad it transforms into a category by restricting the return type to ()N and permuting the type variables. This is similar to how applying a monad to () yields a monoid. Since a  action has a trivial return value, the only reason to run one is for the side effects, in particular database and other IO effects.squeal-postgresqlA  p is a ( for which one can execute or rewind an , of s over p). This includes the category of pure SQL Us and the category of impure  Q  actions.squeal-postgresqlRun an , of s. Create the  as public.schema_migrationsC if it does not already exist. In one transaction, for each each  query to see if the  has been executed; if not,  the  and insert its  in the .squeal-postgresql Rewind an , of s. Create the  as public.schema_migrationsC if it does not already exist. In one transaction, for each each  query to see if the  has been executed; if so,  the  and delete its  in the .squeal-postgresqlA W is a named "isomorphism" over a given category. It should contain an inverse pair of  and  instructions and a unique .squeal-postgresqlThe  of a . Each  in a  should be unique.squeal-postgresqlThe  instruction of a .squeal-postgresqlThe  instruction of a .squeal-postgresql0 ignores the output of a computation, returning () and wrapping it up into a 5. You can lift an action in the base monad by using terminally . lift.squeal-postgresqlA  turns a  involving only pure SQL U s into a % that may be combined with arbitrary .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-postgresqlD creates a simple executable from a connection string and a list of s. squeal-postgresqlconnection stringsqueal-postgresql migrations]Squeal export module(c) Eitan Chatav, 2019eitan@morphism.tech experimentalNoneL   ')*+,-./0123456:;<=>?@ABCDEFGHIJKLMopqrstuvwxyz{|}~ GHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrs      !"#$%&'()*+,-./0123456789BCDEFGHIJKLMNOPQRSTUVWabcdefghijklmnopqrstuvwxyz}~{|      !"#$%&'()*+,-./0123456789<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~57869:;<=>?ECDA@BFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefg'#^_`^_a^bc^defghfghfgifjkfjlfjmnopnoqnornosnotnounovnownoxnoynozno{no|n}~$&%      !"#$%&'()*+, - - . / / 0 1 1 2 3 3 4 5 5 6 7 7 8 9 9 : ; < = > ? @ A B C D 2 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 ijklmnopq'rstuvwxyz{|}~                                        B C   @ A                                                                                         ! " # $ % & ' ( ) * + , -./0123456789R:;<=>?@AB>UCDEFGHLIJKLMNOPQRSFTGUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~O??#11"9DE:     4 !"#$%&<'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~0/-+).*,      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnoppqrstHuTvPQJwxyz{|}~^ ^      ^  ^WX           ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6^7^M89: ;<^=>^b?@^ABnoC^DEnoF^DGnoH^IJ^K^L^M^N^O^P^QRnoS^TU^VWXYZ[^\YZ]  ^_`abc0squeal-postgresql-0.5.2.0-4fAomBtpMjd6mRwLthA4w2Squeal.PostgreSQL.AliasSqueal.PostgreSQL.DefinitionSqueal.PostgreSQL.ExpressionSqueal.PostgreSQL.ListSqueal.PostgreSQL.PQSqueal.PostgreSQL.PoolSqueal.PostgreSQL.RenderSqueal.PostgreSQL.SchemaSqueal.PostgreSQL.PG!Squeal.PostgreSQL.Expression.Type!Squeal.PostgreSQL.Expression.Time'Squeal.PostgreSQL.Expression.TextSearch!Squeal.PostgreSQL.Expression.Text!Squeal.PostgreSQL.Expression.Sort&Squeal.PostgreSQL.Expression.Parameter!Squeal.PostgreSQL.Expression.Math"Squeal.PostgreSQL.Expression.Logic!Squeal.PostgreSQL.Expression.Null'Squeal.PostgreSQL.Expression.Comparison&Squeal.PostgreSQL.Expression.Aggregate#Squeal.PostgreSQL.Expression.WindowSqueal.PostgreSQL.QuerySqueal.PostgreSQL.Manipulation%Squeal.PostgreSQL.Expression.Subquery"Squeal.PostgreSQL.Expression.SetOf'Squeal.PostgreSQL.Expression.Collection!Squeal.PostgreSQL.Expression.JsonSqueal.PostgreSQL.Binary$Squeal.PostgreSQL.Expression.LiteralSqueal.PostgreSQL.TransactionSqueal.PostgreSQL.Migration Data.Listelem Selection FromClauseTableTypedefView ExpressiongroupalterTableRename renameColumn alterTable alterColumn dropTable dropColumn createTable addColumnTableExpressionRowPGSqueal.PostgreSQL.Manipulations ManipulationPrelude->.idListselect insertRowupdateSqueal.PostgreSQL.Logic ConditionCommonTableExpressiontsvectortsqueryjsonjsonbOverQuery.<.>manipulateParams queryParamstraversePreparedSqueal.PostgreSQL.Nullnull_ Data.Maybe fromMaybeWindowFunctionrunQueryParamsrunQuery parameterSqueal.Expression.Parameter manipulatetrueSOPGenericHasDatatypeInfoControl.Monad.State.Class MonadStateDatabase.PostgreSQLLibPQSqueal.PostgreSQLbaseGHC.OverloadedLabelsIsLabel fromLabelControl.Category>>> Data.Function&'sop-core-0.4.0.0-F1xCWaFkXPd3dWDWBPXHJgData.SOP.BasicFunctorsKunK Data.SOP.NPNPNil:*/postgresql-libpq-0.9.4.2-HyacS45qPUc33pszxfYdpMDatabase.PostgreSQL.LibPQResult SingleTuple FatalError NonfatalError BadResponseCopyBothCopyInCopyOutTuplesOk CommandOk EmptyQuery ExecStatusRow"Database.PostgreSQL.LibPQ.Internal Connection,unliftio-pool-0.2.1.0-KkKVyEp8Az19lzh0jU9MFR UnliftIO.PoolPool RenderSQL renderSQL parenthesized bracketed<+>commaSeparated doubleQuotedsingleQuotedTextsingleQuotedUtf8renderCommaSeparatedrenderCommaSeparatedConstraintrenderCommaSeparatedMaybe renderNat renderSymbolprintSQLescapeLengthInElem AlignedListDone:>> AdditionalalsoJoindisjoin extractList mapAlignedsingle*:$fAdditionalaNP$fRenderSQLAlignedList$fCategorykAlignedListQualifiedAlias IsQualified!HasAllHasInHas HasUnique AliasableasAliasedAsAlias GroupedByGrouping UngroupedGrouped::: renderAliased$fGroupedBy[]tablecolumn:$fGroupedBy[]tablecolumn:0 $fRenderSQLNP$fRenderSQLAlias$fIsLabelaliasNP$fIsLabelalias1Alias$fIsLabelalias0Aliased$fAliasablealiasexpressionNP!$fAliasablealiasexpressionAliased$fHaskindalias:field$fHaskindalias:field0$fHasIn[](,)fields(,)$fHasAllk:fields:$fHasAllkind[]fields[]$fIsQualifiedtablecolumn(,)$fRenderSQLQualifiedAlias$fIsLabela0Aliased$fIsQualifiedq0a0Aliased$fIsLabelaQualifiedAlias$fIsQualifiedqaQualifiedAlias $fEqAlias$fGenericAlias $fOrdAlias $fShowAlias $fNFDataAlias$fEqQualifiedAlias$fGenericQualifiedAlias$fOrdQualifiedAlias$fShowQualifiedAlias$fNFDataQualifiedAlias $fOrdAliased $fEqAliased $fShowAliased AllUnique IsNotElem PGJsonType PGJsonKeyPGlabel IsPGlabellabelPublic SchemasType SchemaType SchemumTypeDropIfConstraintsInvolveConstraintInvolvesRenameAlterDropCreate NullifyFrom NullifyRow NullifyType NotAllNull AllNotNull SamePGTypePGTypeOf PGIntegral PGFloatingPGNum TableToRowTableToColumns ColumnsToRowFromTypeRowType TableTypeUniquelyTableConstraintsTableConstraintCheckUnique PrimaryKey ForeignKey ColumnsType ColumnTypeColumnConstraintDefNoDef:=> NullityTypeNullNotNullPGTypePGboolPGint2PGint4PGint8 PGnumericPGfloat4PGfloat8PGmoneyPGchar PGvarcharPGtextPGbytea PGtimestamp PGtimestamptzPGdatePGtimePGtimetz PGintervalPGuuidPGinetPGjsonPGjsonb PGvararray PGfixarrayPGenum PGcomposite PGtsvector PGtsquery UnsafePGType$fSamePGType(,)(,)$fRenderSQLPGlabel$fIsPGlabellabelNP$fIsPGlabellabelPGlabel$fIsNotElem(,)Bool(,)True$fIsNotElemkBoolxFalse $fAllUniquea:$fAllUniquea[]FixArray getFixArrayVarArray getVarArray Enumerated getEnumerated Composite getCompositeJsonbgetJsonbJsongetJsonMoneycentsFixPGDimPGConstructorNamesOfConstructorNameOfConstructorsOf TupleCodeOfTupleOfTuplePGNullPGRowOfLabelsPGPG $fEqMoney $fOrdMoney $fShowMoney $fReadMoney$fGenericMoney$fEqJson $fOrdJson $fShowJson $fReadJson $fGenericJson $fEqJsonb $fOrdJsonb $fShowJsonb $fReadJsonb$fGenericJsonb $fEqComposite$fOrdComposite$fShowComposite$fReadComposite$fGenericComposite$fEqEnumerated$fOrdEnumerated$fShowEnumerated$fReadEnumerated$fGenericEnumerated $fEqVarArray $fOrdVarArray$fShowVarArray$fReadVarArray$fGenericVarArray $fEqFixArray $fOrdFixArray$fShowFixArray$fReadFixArray$fGenericFixArrayPGSubset@><@ FunctionVar FunctionN:-->OperatorExprUnsafeExpressionrenderExpressionunsafeFunctionVarunsafeBinaryOpunsafeUnaryOpLunsafeUnaryOpRunsafeFunctionunsafeFunctionN$fMonoidExpression$fMonoidExpression0$fSemigroupExpression$fSemigroupExpression0$fSemigroupExpression1$fSemigroupExpression2$fIsStringExpression$fIsStringExpression0$fIsStringExpression1$fFloatingExpression$fFractionalExpression$fNumExpression$fIsPGlabellabelExpression$fIsQualifiedtabcolNP$fIsQualifiedtabcolAliased$fIsQualifiedtabcolNP0$fIsQualifiedtabcolExpression$fIsLabelcolNP$fIsLabelcolAliased$fIsLabelcolNP0$fIsLabelcolExpression$fIsQualifiedtabcolNP1$fIsQualifiedtabcolAliased0$fIsQualifiedtabcolNP2$fIsQualifiedtabcolExpression0$fIsLabelcolNP1$fIsLabelcolAliased0$fIsLabelcolNP2$fIsLabelcolExpression0$fRenderSQLExpression$fPGSubsetPGTypePGvararray$fPGSubsetPGTypePGtsquery$fPGSubsetPGTypePGjsonb$fGenericExpression$fShowExpression$fEqExpression$fOrdExpression$fNFDataExpressionPGTypedpgtypeTypeExpressionUnsafeTypeExpressionrenderTypeExpressioncastastype inferredtypetypedef typetabletypeviewboolint2smallintint4intintegerint8bigintnumericfloat4realfloat8doublePrecisionmoneytextchar charactervarcharcharacterVaryingbytea timestamptimestampWithTimeZonedatetimetimeWithTimeZoneintervaluuidinetvararrayfixarray$fRenderSQLTypeExpression$fPGTypedschemasnull$fPGTypedschemasnull0$fPGTypedschemasnull1$fPGTypedschemasnull2$fPGTypedschemasnull3$fPGTypedschemasnull4$fPGTypedschemasnull5$fPGTypedschemasnull6$fPGTypedschemasnull7$fPGTypedschemasnull8$fPGTypedschemasnull9$fPGTypedschemasnull10$fPGTypedschemasnull11$fPGTypedschemasnull12$fPGTypedschemasnull13$fPGTypedschemasnull14$fPGTypedschemasnull15$fPGTypedschemasnull16$fPGTypedschemasnull17$fPGTypedschemasnull18$fPGTypedschemasnull19$fPGTypedschemasnull20$fPGTypedschemasnull21$fPGTypedschemasnull22$fPGTypedschemasnull23$fGenericTypeExpression$fShowTypeExpression$fEqTypeExpression$fOrdTypeExpression$fNFDataTypeExpressionTimeUnitYearsMonthsWeeksDaysHoursMinutesSeconds Microseconds MillisecondsDecades Centuries MillenniaTimeOp!++!!-!-! currentDate currentTimecurrentTimestamp localTimelocalTimestampnowmakeDatemakeTime makeTimestampmakeTimestamptz interval_$fTimeOpPGTypePGdatePGint4"$fTimeOpPGTypePGintervalPGinterval $fTimeOpPGTypePGtimetzPGinterval$fTimeOpPGTypePGtimePGinterval%$fTimeOpPGTypePGtimestamptzPGinterval#$fTimeOpPGTypePGtimestampPGinterval$fRenderSQLTimeUnit$fHasDatatypeInfoTimeUnit$fGenericTimeUnit $fEqTimeUnit $fOrdTimeUnit$fShowTimeUnit$fReadTimeUnit$fEnumTimeUnit$fGenericTimeUnit0@@.&.|.!<->arrayToTSvectortsvectorLengthnumnodeplainToTSqueryphraseToTSquerywebsearchToTSquery queryTree toTSquery toTSvector setWeightstripjsonToTSvectorjsonbToTSvectortsDeletetsFilter tsHeadlinelowerupper charLengthlikeilikeOrderByorderBySortExpressionAscDesc AscNullsFirst AscNullsLastDescNullsFirst DescNullsLast$fRenderSQLSortExpression$fShowSortExpression HasParameterparam$fHasParametern:ty$fHasParameter1:ty1atan2_quot_rem_truncround_ceiling_falsenot_.&&.||caseWhenThenElse ifThenElsenotNullcoalescefromNullisNull isNotNull matchNullnullIf BetweenExpr.==./=.>=.<=greatestleastbetween notBetweenbetweenSymmetricnotBetweenSymmetricisDistinctFromisNotDistinctFromisTrue isNotTrueisFalse isNotFalse isUnknown isNotUnknownPGAvgPGSum DistinctionAllDistinct Aggregate countStarcountsum_arrayAggjsonAggjsonbAggbitAndbitOrboolAndboolOreverymax_min_avgcorrcovarPop covarSampregrAvgXregrAvgY regrCount regrInterceptregrR2 regrSloperegrSxxregrSxyregrSyystddev stddevPop stddevSampvariancevarPopvarSamp$fRenderSQLDistinction$fRenderSQLDistinction0$fNFDataDistinction6$fAggregateNullityTypeDistinctionDistinctionExpression$fGenericDistinction$fShowDistinction$fEqDistinction$fOrdDistinctionWinFunNWinFun1WinFun0UnsafeWindowFunctionrenderWindowFunctionWindowDefinition partitionByunsafeWindowFunction1unsafeWindowFunctionNrank rowNumber denseRank percentRankcumeDistntilelaglead firstValue lastValuenthValue$fRenderSQLWindowDefinition$fOrderByWindowDefinition$fRenderSQLWindowFunction0$fAggregateNullityTypeExpressionNPWindowFunction$fGenericWindowFunction$fShowWindowFunction$fEqWindowFunction$fOrdWindowFunction$fNFDataWindowFunctionWithwith HavingClauseNoHavingHaving GroupByClauseNoGroupsGroupByBy1By2UnsafeFromClauserenderFromClause fromClause whereClause groupByClause havingClause orderByClause limitClause offsetClauseStarDotStarAlsoQuery_ UnsafeQuery renderQueryunionunionAll intersect intersectAllexcept exceptAllselect_selectDistinctselectDistinct_valuesvalues_fromwhere_groupByhavinglimitoffsettablesubqueryviewcommon crossJoin innerJoin leftOuterJoinrightOuterJoin fullOuterJoin withRecursive$fRenderSQLQuery$fIsStringSelection$fRenderSQLSelection$fIsLabelcolSelection$fIsLabelcolSelection0$fIsQualifiedtabcolSelection$fIsQualifiedtabcolSelection0!$fAliasablecolExpressionSelection$fAdditional(,)Selection$fAdditionalaFromClause$fRenderSQLFromClause$fIsQualifiedrelcolNP$fIsQualifiedrelcolBy$fIsLabelcolBy $fRenderSQLBy$fRenderSQLGroupByClause$fRenderSQLHavingClause$fOrderByTableExpression$fRenderSQLTableExpression $fRenderSQLCommonTableExpression"$fAliasablectestatementAlignedList,$fAliasablectestatementCommonTableExpression $fWithQuery$fGenericQuery $fShowQuery $fEqQuery $fOrdQuery $fNFDataQuery$fGenericFromClause$fShowFromClause$fEqFromClause$fOrdFromClause$fNFDataFromClause$fGenericTableExpression$fOrdHavingClause$fEqHavingClause$fShowHavingClause$fOrdBy$fEqBy$fShowBy UsingClauseNoUsingUsingConflictTarget OnConstraintConflictAction DoNothingDoUpdateConflictClauseOnConflictDoRaise OnConflictReturningClause ReturningOptionalDefaultSet QueryClauseValuesSelectSubquery Manipulation_UnsafeManipulationrenderManipulation Returning_Values_queryStatement insertInto insertInto_update_ deleteFrom deleteFrom_$fWithManipulation$fRenderSQLManipulation$fRenderSQLOptional$fRenderSQLQueryClause$fRenderSQLReturningClause$fRenderSQLConflictAction$fRenderSQLConflictTarget$fRenderSQLConflictClause$fGenericManipulation$fShowManipulation$fEqManipulation$fOrdManipulation$fNFDataManipulationexistssubAllsubAnyin_notInSetOfFunctionN SetOfFunctionunsafeSetOfFunctionunsafeSetOfFunctionNgenerateSeriesgenerateSeriesStepgenerateSeriesTimestamparrayarray1array2 cardinalityindexunnestrowfieldJsonToRecordFunctionJsonPopulateFunctionJsonBuildObjectjsonBuildObjectjsonbBuildObject.->.->>.#>.#>>.?.?|.?&.-.#-.toJsontoJsonb arrayToJson rowToJsonjsonBuildArrayjsonbBuildArray jsonObject jsonbObject jsonZipObjectjsonbZipObjectjsonArrayLengthjsonbArrayLength jsonTypeof jsonbTypeofjsonStripNullsjsonbStripNullsjsonbSet jsonbInsert jsonbPrettyjsonEach jsonbEach jsonEachText jsonbEachTextjsonObjectKeysjsonbObjectKeysjsonPopulateRecordjsonbPopulateRecordjsonPopulateRecordSetjsonbPopulateRecordSet jsonToRecord jsonbToRecordjsonToRecordSetjsonbToRecordSet$fJsonBuildObject:$fJsonBuildObject[]ColumnTypeExpressionUnsafeColumnTypeExpressionrenderColumnTypeExpression FieldTyped fieldtype AlterColumnUnsafeAlterColumnrenderAlterColumn AddColumn AlterTableUnsafeAlterTablerenderAlterTableOnUpdateClauseOnUpdateNoActionOnUpdateRestrictOnUpdateCascadeOnDeleteClauseOnDeleteNoActionOnDeleteRestrictOnDeleteCascade ForeignKeyedTableConstraintExpressionUnsafeTableConstraintExpressionrenderTableConstraintExpression DefinitionUnsafeDefinitionrenderDefinitionmanipDefinition createSchemacreateSchemaIfNotExistscreateTableIfNotExistscheckunique primaryKey foreignKey dropSchema addConstraintdropConstraint setDefault dropDefault setNotNull dropNotNull alterType createViewdropViewcreateTypeEnumcreateTypeEnumFromcreateTypeCompositecreateTypeCompositeFrom createDomaindropTypenullable notNullabledefault_serial2 smallserialserial4serialserial8 bigserial$fCategory[]Definition$fRenderSQLDefinition$$fRenderSQLTableConstraintExpression$fRenderSQLOnDeleteClause$fNFDataOnDeleteClause$fRenderSQLOnUpdateClause$fNFDataOnUpdateClause$fFieldTypedschemas(,)$fRenderSQLColumnTypeExpression$fAddColumn(,)$fAddColumn(,)0$fGenericDefinition$fShowDefinition$fEqDefinition$fOrdDefinition$fNFDataDefinition"$fGenericTableConstraintExpression$fShowTableConstraintExpression$fEqTableConstraintExpression$fOrdTableConstraintExpression!$fNFDataTableConstraintExpression$fGenericOnDeleteClause$fShowOnDeleteClause$fEqOnDeleteClause$fOrdOnDeleteClause$fGenericOnUpdateClause$fShowOnUpdateClause$fEqOnUpdateClause$fOrdOnUpdateClause$fGenericAlterTable$fShowAlterTable$fEqAlterTable$fOrdAlterTable$fNFDataAlterTable$fGenericAlterColumn$fShowAlterColumn$fEqAlterColumn$fOrdAlterColumn$fNFDataAlterColumn$fGenericColumnTypeExpression$fShowColumnTypeExpression$fEqColumnTypeExpression$fOrdColumnTypeExpression$fNFDataColumnTypeExpressionOnlyfromOnlyFromRowfromRow FromFixArray fromFixArray FromField fromField FromValue fromValueToParamstoParams ToFixArray toFixArrayToFieldtoFieldToNullityParamtoNullityParam HasAliasedOid aliasedOidHasOidoidToParamtoParam$fToParamEnumeratedPGenum$fToParamJsonbPGjsonb$fToParamJsonPGjson$fToParamValuePGjsonb$fToParamValuePGjson$fToParamDiffTimePGinterval$fToParamUTCTimePGtimestamptz$fToParamLocalTimePGtimestamp$fToParam(,)PGtimetz$fToParamTimeOfDayPGtime$fToParamDayPGdate$fToParamByteStringPGbytea$fToParamByteStringPGbytea0$fToParam[]PGtext$fToParamTextPGtext$fToParamTextPGtext0$fToParamCharPGchar$fToParamNetAddrPGinet$fToParamUUIDPGuuid$fToParamMoneyPGmoney$fToParamScientificPGnumeric$fToParamDoublePGfloat8$fToParamFloatPGfloat4$fToParamWord64PGint8$fToParamInt64PGint8$fToParamWord32PGint4$fToParamInt32PGint4$fToParamWord16PGint2$fToParamInt16PGint2$fToParamBoolPGbool$fHasOidPGjsonb$fHasOidPGjson$fHasOidPGinet$fHasOidPGuuid$fHasOidPGinterval$fHasOidPGtimetz$fHasOidPGtime$fHasOidPGdate$fHasOidPGtimestamptz$fHasOidPGtimestamp$fHasOidPGbytea$fHasOidPGtext$fHasOidPGvarchar$fHasOidPGchar$fHasOidPGfloat8$fHasOidPGfloat4$fHasOidPGnumeric$fHasOidPGint8$fHasOidPGint4$fHasOidPGint2$fHasOidPGbool$fToParamVarArrayPGvararray$fToParamVarArrayPGvararray0$fHasAliasedOid(,)$fToNullityParamMaybeNull$fToNullityParamxNotNull$fToParamVarArrayPGvararray1$fToField(,)(,)$fToParamCompositePGcomposite$fToFixArrayproduct:ty$fToFixArrayx[]ty$fToParamFixArrayPGfixarray$fToParamsxtys$fFromValuePGenumEnumerated$fFromValuePGjsonbJsonb$fFromValuePGjsonJson$fFromValuePGjsonbValue$fFromValuePGjsonValue$fFromValuePGintervalDiffTime$fFromValuePGtimestamptzUTCTime$fFromValuePGtimestampLocalTime$fFromValuePGtimetz(,)$fFromValuePGtimeTimeOfDay$fFromValuePGdateDay$fFromValuePGbyteaByteString$fFromValuePGbyteaByteString0$fFromValuePGtext[]$fFromValuePGtextText$fFromValuePGtextText0$fFromValuePGcharChar$fFromValuePGinetNetAddr$fFromValuePGuuidUUID$fFromValuePGmoneyMoney$fFromValuePGnumericScientific$fFromValuePGfloat8Double$fFromValuePGfloat4Float$fFromValuePGint8Int64$fFromValuePGint4Int32$fFromValuePGint2Int16$fFromValuePGboolBool$fFromField(,)(,)$fFromField(,)(,)0$fFromFixArray:typroduct$fFromFixArray[]NullMaybe$fFromFixArray[]NotNully$fFromValuePGfixarrayFixArray$fFromValuePGvararrayVarArray$fFromValuePGvararrayVarArray0$fFromValuePGvararrayVarArray1$fFromValuePGvararrayVarArray2$fFromRowresulty$fFromValuePGcompositeComposite$fHasDatatypeInfoOnly $fGenericOnly $fFunctorOnly$fFoldableOnly$fTraversableOnly$fEqOnly $fOrdOnly $fReadOnly $fShowOnly$fGenericOnly0SquealException PQExceptionResultExceptionParseExceptionPQState sqlExecStatus sqlStateCodesqlErrorMessageMonadPQmanipulateParams_ manipulate_ forPreparedtraversePrepared_ forPrepared_liftPQIndexedMonadTransPQpqAppqJoinpqBindpqThen pqAndThendefinePQunPQ connectdbfinishwithConnectionlowerConnectionrunPQexecPQevalPQgetRownextRowgetRowsfirstRow liftResultntuples resultStatusresultErrorMessageresultErrorCodeokResult catchSqueal handleSqueal trySqueal$fMonadUnliftIOPQ $fMonadIOPQ $fMMonadPQ$fMonadTransPQ$fMFunctorTYPEPQ $fMonadFailPQ $fFunctorPQ $fMonadPQ$fApplicativePQ$fMonadPQschemasContT$fMonadPQschemasRWST$fMonadPQschemasRWST0$fMonadPQschemasExceptT$fMonadPQschemasMaybeT$fMonadPQschemasWriterT$fMonadPQschemasWriterT0$fMonadPQschemasStateT$fMonadPQschemasStateT0$fMonadPQschemasReaderT$fMonadPQschemasIdentityT$fExceptionSquealException$fMonadPQschemasPQ$fIndexedMonadTransPQPQ $fEqPQState $fShowPQState$fEqSquealException$fShowSquealExceptioncreateConnectionPoolusingConnectionPooldestroyConnectionPoolLiteralliteral$fLiteralEnumerated $fLiteralText$fLiteralText0$fLiteralDouble$fLiteralFloat$fLiteralInt64$fLiteralInt32$fLiteralInt16 $fLiteral[] $fLiteralChar$fLiteralJsonb $fLiteralJson $fLiteralBoolDeferrableMode Deferrable NotDeferrable AccessMode ReadWriteReadOnlyIsolationLevel SerializableRepeatableRead ReadCommittedReadUncommittedTransactionModeisolationLevel accessModedeferrableModetransactionallytransactionally_transactionallyRetry ephemerally ephemerally_begincommitrollback defaultModelongRunningMode$fRenderSQLIsolationLevel$fRenderSQLAccessMode$fRenderSQLDeferrableMode$fRenderSQLTransactionMode$fShowIsolationLevel$fEqIsolationLevel$fShowAccessMode$fEqAccessMode$fShowDeferrableMode$fEqDeferrableMode$fShowTransactionMode$fEqTransactionModeMigrationsTable Terminally runTerminally Migratory migrateUp migrateDown Migrationnameupdown terminally pureMigration defaultMain$fMigratoryDefinition$fCategory[]Terminally$fHasDatatypeInfoMigrationRow$fGenericMigrationRow$fMigratoryTerminally$fGenericMigration$fGenericTerminally$fGenericMigrationRow0$fShowMigrationRow$fGenericMigrateCommand$fShowMigrateCommandbytestring-0.10.8.2Data.ByteString.Internal ByteString text-1.2.3.1Data.Text.InternalText GHC.MaybeMaybeNothingghc-prim GHC.TypesNatSymbolGHC.Base+++generics-sop-0.4.0.1-50QtUhSPhiXGrcY9j3QNt9Generics.SOP.UniverseCodeType GHC.TypeLits TypeError D:R:PGValue D:R:PGNetAddr D:R:PGUUIDD:R:PGDiffTime D:R:PG(,)D:R:PGTimeOfDay D:R:PGDay D:R:PGUTCTimeD:R:PGLocalTimeD:R:PGByteStringD:R:PGByteString0D:R:PG[] D:R:PGText D:R:PGText0 D:R:PGChar D:R:PGDouble D:R:PGFloatD:R:PGScientific D:R:PGWord64 D:R:PGWord32 D:R:PGWord16 D:R:PGInt64 D:R:PGInt32 D:R:PGInt16 D:R:PGBool D:R:PGMoney D:R:PGJson D:R:PGJsonbD:R:PGCompositeD:R:PGEnumeratedD:R:PGVarArray0D:R:PGFixArrayJustmaybeunsafeAggregate1unsafeAggregateN GHC.ClassesminGHC.Num+CategoryrenderCreationGHC.Exception.Type Exception execParamsData.Traversabletraverseprepare Traversable execPrepared Data.FoldableFoldableFunctorMonad<*>join=<<>> Control.Monad<=<exec GHC.Exceptionthrow Data.EitherEitherdestroyAllResources&unliftio-0.2.12-5DamjS12r4928CJMrZLZhtUnliftIO.Exception onExceptionreturntryIOcreateMigrationsinsertMigrationdeleteMigrationselectMigration