Manipulation

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 "john@smith.com" `as` #email))
      (OnConflict (OnConstraint #uq)
        (DoUpdate (Set (#excluded ! #email <> "; " <> #customers ! #email) `as` #email) []))
      (Returning_ Nil)
in printSQL manp
:}
INSERT INTO "customers" AS "customers" ("name", "email") VALUES ((E'John Smith' :: text), (E'john@smith.com' :: text)) ON CONFLICT ON CONSTRAINT "uq" DO UPDATE SET "email" = ("excluded"."email" || ((E'; ' :: text) || "customers"."email"))

query insert:

>>> :{
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"

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

Convert a Query into a Manipulation.

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.

Returning a List

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.