Squeal.PostgreSQL.Manipulation

data manipulation language

Manipulation

newtype Manipulation (with :: FromType) (db :: SchemasType) (params :: [NullType]) (columns :: RowType)

A Manipulation is a statement which may modify data in the database, but does not alter its schemas. Examples are insertIntos, updates and deleteFroms. A queryStatement is also considered a Manipulation even though it does not modify data.

The general Manipulation type is parameterized by

• with :: FromType - scope for all common table expressions,
• db :: SchemasType - scope for all tables and views,
• params :: [NullType] - scope for all parameters,
• row :: RowType - return type of the Manipulation.

Let's see some examples of Manipulations.

simple insert:

>>> type Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint4, "col2" ::: 'Def :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
let
  manp :: Manipulation with (Public Schema) '[] '[]
  manp =
    insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))
in printSQL manp
:}
INSERT INTO "tab" AS "tab" ("col1", "col2") VALUES ((2 :: int4), DEFAULT)

out-of-line parameterized insert:

>>> type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
let
  manp :: Manipulation with (Public Schema) '[ 'NotNull 'PGint4] '[]
  manp =
    insertInto_ #tab $ Values_
      (Default `as` #col1 :* Set (param @1) `as` #col2)
in printSQL manp
:}
INSERT INTO "tab" AS "tab" ("col1", "col2") VALUES (DEFAULT, ($1 :: int4))

in-line parameterized insert:

>>> type Columns = '["col1" ::: 'Def :=> 'NotNull 'PGint4, "col2" ::: 'NoDef :=> 'NotNull 'PGint4]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
data Row = Row { col1 :: Optional SOP.I ('Def :=> Int32), col2 :: Int32 }
  deriving stock (GHC.Generic)
  deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)
:}

>>> :{
let
  manp :: Row -> Row -> Manipulation with (Public Schema) '[] '[]
  manp row1 row2 = insertInto_ #tab $ inlineValues row1 [row2]
  row1 = Row {col1 = Default, col2 = 2 :: Int32}
  row2 = Row {col1 = NotDefault (3 :: Int32), col2 = 4 :: Int32}
in printSQL (manp row1 row2)
:}
INSERT INTO "tab" AS "tab" ("col1", "col2") VALUES (DEFAULT, (2 :: int4)), ((3 :: int4), (4 :: int4))

returning insert:

>>> :{
let
  manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4]
  manp =
    insertInto #tab (Values_ (Set 2 `as` #col1 :* Set 3 `as` #col2))
      OnConflictDoRaise (Returning #col1)
in printSQL manp
:}
INSERT INTO "tab" AS "tab" ("col1", "col2") VALUES ((2 :: int4), (3 :: int4)) RETURNING "col1" AS "col1"

upsert:

>>> type CustomersColumns = '["name" ::: 'NoDef :=> 'NotNull 'PGtext, "email" ::: 'NoDef :=> 'NotNull 'PGtext]
>>> type CustomersConstraints = '["uq" ::: 'Unique '["name"]]
>>> type CustomersSchema = '["customers" ::: 'Table (CustomersConstraints :=> CustomersColumns)]
>>> :{
let
  manp :: Manipulation with (Public CustomersSchema) '[] '[]
  manp =
    insertInto #customers
      (Values_ (Set "John Smith" `as` #name :* Set "[e-mail]" `as` #email))
      (OnConflict (OnConstraint #uq)
        (DoUpdate (Set (#excluded ! #email <> "; " <> #customers ! #email) `as` #email) []))
      (Returning_ Nil)
in printSQL manp
:}
INSERT INTO "customers" AS "customers" ("name", "email") VALUES ((E'John Smith' :: text), (E'[e-mail]' :: text)) ON CONFLICT ON CONSTRAINT "uq" DO UPDATE SET "email" = ("excluded"."email" || ((E'; ' :: text) || "customers"."email"))

query insert:

>>> :{
let
  manp :: Manipulation with (Public Schema) '[] '[]
  manp = insertInto_ #tab (Subquery (select Star (from (table #tab))))
in printSQL manp
:}
INSERT INTO "tab" AS "tab" SELECT * FROM "tab" AS "tab"

update:

>>> :{
let
  manp :: Manipulation with (Public Schema) '[] '[]
  manp = update_ #tab (Set 2 `as` #col1) (#col1 ./= #col2)
in printSQL manp
:}
UPDATE "tab" AS "tab" SET "col1" = (2 :: int4) WHERE ("col1" <> "col2")

delete:

>>> :{
let
  manp :: Manipulation with (Public Schema) '[] '["col1" ::: 'NotNull 'PGint4, "col2" ::: 'NotNull 'PGint4]
  manp = deleteFrom #tab NoUsing (#col1 .== #col2) (Returning Star)
in printSQL manp
:}
DELETE FROM "tab" AS "tab" WHERE ("col1" = "col2") RETURNING *

delete and using clause:

>>> :{
type Schema3 =
  '[ "tab" ::: 'Table ('[] :=> Columns)
   , "other_tab" ::: 'Table ('[] :=> Columns)
   , "third_tab" ::: 'Table ('[] :=> Columns) ]
:}

>>> :{
let
  manp :: Manipulation with (Public Schema3) '[] '[]
  manp =
    deleteFrom #tab (Using (table #other_tab & also (table #third_tab)))
    ( (#tab ! #col2 .== #other_tab ! #col2)
    .&& (#tab ! #col2 .== #third_tab ! #col2) )
    (Returning_ Nil)
in printSQL manp
:}
DELETE FROM "tab" AS "tab" USING "other_tab" AS "other_tab", "third_tab" AS "third_tab" WHERE (("tab"."col2" = "other_tab"."col2") AND ("tab"."col2" = "third_tab"."col2"))

with manipulation:

>>> type ProductsColumns = '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]
>>> type ProductsSchema = '["products" ::: 'Table ('[] :=> ProductsColumns), "products_deleted" ::: 'Table ('[] :=> ProductsColumns)]
>>> :{
let
  manp :: Manipulation with (Public ProductsSchema) '[ 'NotNull 'PGdate] '[]
  manp = with
    (deleteFrom #products NoUsing (#date .< param @1) (Returning Star) `as` #del)
    (insertInto_ #products_deleted (Subquery (select Star (from (common #del)))))
in printSQL manp
:}
WITH "del" AS (DELETE FROM "products" AS "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" AS "products_deleted" SELECT * FROM "del" AS "del"

Constructors

UnsafeManipulation

Fields

• renderManipulation :: ByteString

Instances

With Manipulation

Defined in Squeal.PostgreSQL.Manipulation

Methods

with :: forall (db :: SchemasType) (params :: [NullType]) (with0 :: FromType) (with1 :: FromType) (row :: RowType). Path (CommonTableExpression Manipulation db params) with0 with1 -> Manipulation with1 db params row -> Manipulation with0 db params row

Eq (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

Methods

(==) :: Manipulation with db params columns -> Manipulation with db params columns -> Bool

(/=) :: Manipulation with db params columns -> Manipulation with db params columns -> Bool

Ord (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

Methods

compare :: Manipulation with db params columns -> Manipulation with db params columns -> Ordering

(<) :: Manipulation with db params columns -> Manipulation with db params columns -> Bool

(<=) :: Manipulation with db params columns -> Manipulation with db params columns -> Bool

(>) :: Manipulation with db params columns -> Manipulation with db params columns -> Bool

(>=) :: Manipulation with db params columns -> Manipulation with db params columns -> Bool

max :: Manipulation with db params columns -> Manipulation with db params columns -> Manipulation with db params columns

min :: Manipulation with db params columns -> Manipulation with db params columns -> Manipulation with db params columns

Show (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

Methods

showsPrec :: Int -> Manipulation with db params columns -> ShowS

show :: Manipulation with db params columns -> String

showList :: [Manipulation with db params columns] -> ShowS

Generic (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

Associated Types

type Rep (Manipulation with db params columns) :: Type -> Type

Methods

from :: Manipulation with db params columns -> Rep (Manipulation with db params columns) x

to :: Rep (Manipulation with db params columns) x -> Manipulation with db params columns

NFData (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

Methods

rnf :: Manipulation with db params columns -> ()

RenderSQL (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: Manipulation with db params columns -> ByteString

type Rep (Manipulation with db params columns)

Defined in Squeal.PostgreSQL.Manipulation

type Rep (Manipulation with db params columns) = D1 ('MetaData "Manipulation" "Squeal.PostgreSQL.Manipulation" "squeal-postgresql-0.8.0.0-HHFIvalMWy63oPSAK2xG8g" 'True) (C1 ('MetaCons "UnsafeManipulation" 'PrefixI 'True) (S1 ('MetaSel ('Just "renderManipulation") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 ByteString)))

type family Manipulation_ (db :: SchemasType) (params :: Type) (row :: Type) where ...

The Manipulation_ type is parameterized by a db SchemasType, against which it is type-checked, an input params Haskell Type, and an ouput row Haskell Type.

Generally, params will be a Haskell tuple or record whose entries may be referenced using positional params and row will be a Haskell record, whose entries will be targeted using overloaded labels.

A Manipulation_ can be run using manipulateParams, or if params = () using manipulate.

Manipulation_ is a type family which resolves into a Manipulation, so don't be fooled by the input params and output row Haskell Types, which are converted into appropriate Postgres [NullType] params and RowType rows. Use manipulation to fix actual Haskell input params and output rows.

>>> :set -XDeriveAnyClass -XDerivingStrategies
>>> type Columns = '["col1" ::: 'NoDef :=> 'Null 'PGint8, "col2" ::: 'Def :=> 'NotNull 'PGtext]
>>> type Schema = '["tab" ::: 'Table ('[] :=> Columns)]
>>> :{
data Row = Row { col1 :: Maybe Int64, col2 :: String }
  deriving stock (GHC.Generic)
  deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)
:}

>>> :{
let
  manp :: Manipulation_ (Public Schema) (Int64, Int64) Row
  manp = deleteFrom #tab NoUsing (#col1 .== param @1 + param @2) (Returning Star)
  stmt :: Statement (Public Schema) (Int64, Int64) Row
  stmt = manipulation manp
:}

>>> :type manp
manp
  :: Manipulation
       '[]
       '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]]
       '[ 'NotNull 'PGint8, 'NotNull 'PGint8]
       '["col1" ::: 'Null 'PGint8, "col2" ::: 'NotNull 'PGtext]
>>> :type stmt
stmt
  :: Statement
       '["public" ::: '["tab" ::: 'Table ('[] :=> Columns)]]
       (Int64, Int64)
       Row

Equations

Manipulation_ db params row = Manipulation '[] db (TuplePG params) (RowPG row)

queryStatement

Arguments

:: Query '[] with db params columns

Query to embed as a Manipulation

-> Manipulation with db params columns

Convert a Query into a Manipulation.

newtype ReturningClause with db params from row

A ReturningClause computes and returns value(s) based on each row actually inserted, updated or deleted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. Only rows that were successfully inserted or updated or deleted will be returned. For example, if a row was locked but not updated because an OnConflict DoUpdate condition was not satisfied, the row will not be returned. Returning Star will return all columns in the row. Use Returning_ Nil in the common case where no return values are desired.

Constructors

Returning (Selection 'Ungrouped '[] with db params from row)

Instances

RenderSQL (ReturningClause with db params from row)

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: ReturningClause with db params from row -> ByteString

pattern Returning_

Arguments

:: SListI row

=> NP (Aliased (Expression 'Ungrouped '[] with db params from)) row

row of values

-> ReturningClause with db params from row

Returning a List

data UsingClause with db params from where

Specify additional tables with Using an also 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 Using list, unless you wish to set up a self-join. NoUsing if no additional tables are to be used.

Constructors

NoUsing :: UsingClause with db params '[]

Using

Fields

• :: FromClause '[] with db params from

  what to use

• -> UsingClause with db params from