Select¶

In this chapter we look at reading data in detail. We already know that the type defines the shape of the result of selectSch: which fields to read, which related tables to pull in, and whether they are nested as a list or as a single row. Here we see which result shapes there are, and then move on to describing the query: conditions, following relations, ordering, limiting the number of rows, aggregates and, finally, the way out for when pg-schema cannot express what you need.

Data for the examples¶

The examples of this chapter are in tutorial/app/app/Tut/Select.hs. Before them the program recreates the schema and fills it with a small data set: four users, three of whom have a manager, four projects in different statuses and six tasks. Here is how the data is filled in:

Filling the database for the chapter's examples

type UserIn = "id" := Int64 :. "name" := Text :. "email" := Maybe Text
  :. "managerId" := Maybe Int64

type ProjectIn = "id" := Int64 :. "ownerId" := Int64 :. "title" := Text
  :. "status" := ProjectStatus :. "tags" := PgArr Text

type TaskIn = "projectId" := Int64 :. "seq" := Int32 :. "title" := Text
  :. "priority" := Int32 :. "payload" := Maybe Payload :. "watchers" := PgArr Int64

_ <- insertSch_ (MyAnn "users") conn $ rowsOf @UserIn
  [ 1 :. "Alice" :. Just "alice@example.com" :. Nothing
  , 2 :. "Bob"   :. Just "bob@example.com"   :. Just 1
  , 3 :. "Carol" :. Nothing                  :. Just 1
  , 4 :. "Dave"  :. Just "dave@example.com"  :. Just 2 ]
_ <- insertSch_ (MyAnn "projects") conn $ rowsOf @ProjectIn
  [ 1 :. 1 :. "Website" :. Project_status_active   :. pgArr' ["web", "frontend"]
  , 2 :. 2 :. "API"     :. Project_status_active   :. pgArr' ["backend"]
  , 3 :. 3 :. "Docs"    :. Project_status_draft    :. pgArr' []
  , 4 :. 4 :. "Legacy"  :. Project_status_archived :. pgArr' ["backend"] ]
_ <- insertSch_ (MyAnn "tasks") conn $ rowsOf @TaskIn
  [ 1 :. 1 :. "Design"    :. 10  :. Just (Payload 3) :. pgArr' [2, 3]
  , 1 :. 2 :. "Frontend"  :. 30  :. Just (Payload 8) :. pgArr' [3]
  , 1 :. 3 :. "Polish"    :. 100 :. Nothing          :. pgArr' []
  , 2 :. 1 :. "Schema"    :. 20  :. Just (Payload 5) :. pgArr' [1]
  , 2 :. 2 :. "Endpoints" :. 40  :. Nothing          :. pgArr' [1, 4]
  , 3 :. 1 :. "Outline"   :. 50  :. Nothing          :. pgArr' [] ]

All examples are run by the same helper function run. It prints the result rows one per line, then the query parameters and, separately, the formatted SQL:

-- | Run a query, print the rows one per line and the query parameters,
-- then the formatted SQL.
run :: Show r => String -> IO ([r], (Text, [SomeToField])) -> IO ()
run name act = do
  sql <- section name $ do
    (rows, (sql, params)) <- act
    mapM_ print rows
    putStrLn $ "params: " <> show params
    pure sql
  sqlSection name [sql]

Result shape¶

Let us start with a query without any conditions. We read projects together with their owners and tasks:

type Owner = "name" := Text

data Task = Task { seq :: Int32, title :: Text, priority :: Int32 }
  deriving (Show, Generic)

data Project = Project
  { title              :: Text
  , status             :: ProjectStatus
  , projectOwner       :: Owner
  , tasksProjectIdFkey :: [Task]
  } deriving (Show, Generic)

selectSch (MyAnn "projects") @Project conn qpEmpty

Project {title = "Website", status = Project_status_active, projectOwner = "name" =: "Alice", tasksProjectIdFkey = [Task {seq = 1, title = "Design", priority = 10},Task {seq = 2, title = "Frontend", priority = 30},Task {seq = 3, title = "Polish", priority = 100}]}
Project {title = "API", status = Project_status_active, projectOwner = "name" =: "Bob", tasksProjectIdFkey = [Task {seq = 1, title = "Schema", priority = 20},Task {seq = 2, title = "Endpoints", priority = 40}]}
Project {title = "Docs", status = Project_status_draft, projectOwner = "name" =: "Carol", tasksProjectIdFkey = [Task {seq = 1, title = "Outline", priority = 50}]}
Project {title = "Legacy", status = Project_status_archived, projectOwner = "name" =: "Dave", tasksProjectIdFkey = []}
params: []

Formatted SQL

select t0.title "title",
       t0.status "status",
       jsonb_build_object('name',t1.name) "projectOwner",
       array_to_json(array(
           select jsonb_build_object('seq',t2.seq,'title',t2.title,'priority',t2.priority)
           from tut.tasks t2
           where t2.project_id=t0.id)) "tasksProjectIdFkey"
from tut.projects t0
join tut.users t1 on t0.owner_id=t1.id;

Two fields of the type Project describe relations. tasksProjectIdFkey is, as in the chapter Quick start, the foreign key from tasks to projects: for a project it leads to child rows, so the field is a list. projectOwner goes the other way: the key projects_owner_id_fkey points from projects to users, so a project has exactly one owner, and the field is a single row of type Owner. We made up the name projectOwner ourselves. The renamer MyRenamer from the chapter Core workflow translates it into the real name of the foreign key:

data MyRenamer :: Renamer

type family MyRenamerImpl (s :: Symbol) :: Symbol where
  MyRenamerImpl "projectOwner" = "projects_owner_id_fkey"
  MyRenamerImpl "manager" = "users_manager_id_fkey"
  MyRenamerImpl "subordinates" = "users_manager_id_fkey"
  MyRenamerImpl s = CamelToSnake s

type instance ApplyRenamer MyRenamer s = MyRenamerImpl s

Note that Owner is not a record but a PgTag: type Owner = "name" := Text. The two notations can be mixed freely. A field of a Generic record can be a PgTag row, a list of them or a Maybe, and a record, in turn, can be part of a :. chain, as in the chapter Core workflow. For a small row of one or two fields you do not have to define a separate record.

The SQL shows how pg-schema builds such a query. The parent row is attached with a join, and child rows are gathered by a subquery into a JSON array, so the whole result is read by one query.

If a foreign key allows NULL, there may be no parent. Then the field must have a Maybe type, and a left outer join is used instead of a join. A good example is the column users.manager_id: it refers to the same table users. The same foreign key can be followed both ways: to the manager (one row or Nothing) and to the subordinates (a list). pg-schema decides the direction from the field type, and the renamer lets us give both fields clear names, manager and subordinates:

data Person = Person
  { name         :: Text
  , manager      :: Maybe Owner
  , subordinates :: [Owner]
  } deriving (Show, Generic)

selectSch (MyAnn "users") @Person conn qpEmpty

Person {name = "Alice", manager = Nothing, subordinates = ["name" =: "Bob","name" =: "Carol"]}
Person {name = "Bob", manager = Just ("name" =: "Alice"), subordinates = ["name" =: "Dave"]}
Person {name = "Carol", manager = Just ("name" =: "Alice"), subordinates = []}
Person {name = "Dave", manager = Just ("name" =: "Bob"), subordinates = []}
params: []

Formatted SQL

select t0.name "name",
       case when t1.name is null then null else jsonb_build_object('name',t1.name) end "manager",
       array_to_json(array(
           select jsonb_build_object('name',t2.name)
           from tut.users t2
           where t2.manager_id=t0.id)) "subordinates"
from tut.users t0
left outer join tut.users t1 on t0.manager_id=t1.id;

Conditions¶

Conditions are set with the function qWhere inside qRoot. For comparing with a parameter there are the operators =?, <?, >?, <=? and >=?, and for matching strings against a pattern ~=? (like) and ~~? (ilike). Conditions are combined with the operators &&& and |||, and pnot negates them. &&& binds tighter than |||, like and and or in SQL, so no extra parentheses are needed:

selectSch (MyAnn "projects") @("title" := Text :. "status" := ProjectStatus) conn $ qRoot do
  qWhere $ "status" =? Project_status_active &&& "title" ~~? ("%site%" :: Text)
    ||| pnot ("status" =? Project_status_active) &&& pinArr "id" [3, 4 :: Int64]

"title" =: "Website" :. "status" =: Project_status_active
"title" =: "Docs" :. "status" =: Project_status_draft
"title" =: "Legacy" :. "status" =: Project_status_archived
params: [SomeToField Project_status_active,SomeToField "%site%",SomeToField Project_status_active,SomeToField (PGArray {fromPGArray = [3,4]})]

Formatted SQL

select t0.title "title",
       t0.status "status"
from tut.projects t0
where (t0.status = ? and t0.title ilike ? or not (t0.status = ?) and t0.id = any(?::int8[]));

Here we selected active projects whose title contains "site" and, besides them, inactive projects with id 3 or 4. pinArr checks that a value is in a list. The list may be empty, and then the condition is simply false. Note that the column id is used in the condition, although it is not in the result: conditions may refer to any column of the table.

Columns that allow NULL deserve special attention. To check that a value is missing, use pnull. To compare with a value that may itself be Nothing, there is the operator =??: it turns into is not distinct from, and Nothing matches NULL with it. Another way to check membership in a list is pin, which takes a non-empty list (NonEmpty):

selectSch (MyAnn "users") @("name" := Text :. "email" := Maybe Text) conn $ qRoot do
  qWhere $ pnull "email" ||| "email" =?? Just ("bob@example.com" :: Text)
    ||| pin "name" ("Dave" :| ["Eve" :: Text])

"name" =: "Bob" :. "email" =: Just "bob@example.com"
"name" =: "Carol" :. "email" =: Nothing
"name" =: "Dave" :. "email" =: Just "dave@example.com"
params: [SomeToField (Just "bob@example.com"),SomeToField (PGArray {fromPGArray = ["Dave","Eve"]})]

Formatted SQL

select t0.name "name",
       t0.email "email"
from tut.users t0
where ((t0.email is null or t0.email is not distinct from ?) or t0.name = any(?::text[]));

The types of values in conditions are checked as strictly as the result fields. If a column allows NULL, the value it is compared with must be Maybe too. For example, "managerId" =? (1 :: Int64) does not compile:

• You have to use Maybe for nullable fields

  Table: NameNS "tut" "users"
  DB Field name: "manager_id"
  DB Field type: NameNS "pg_catalog" "int8"
  Haskell Field type: Int64

The right way is "managerId" =? Just (1 :: Int64). Note that the message says manager_id: names in conditions, like result fields, go through the renamer, so you can write them either in camelCase or as they are named in the database.

Result branches: qPath¶

Conditions, ordering and row limits can be set not only for the root table but for any branch of the result. That is what qPath with a relation name is for: inside it everything refers to the table at the other end of the relation. In the next example we take only active projects, order them by title in reverse, and keep one task for each project — the one with the highest priority among those with a priority below 50 (ascf and descf order by a field ascending and descending):

selectSch (MyAnn "projects") @Project conn $ qRoot do
  qWhere $ "status" =? Project_status_active
  qOrderBy [descf "title"]
  qPath "tasksProjectIdFkey" do
    qWhere $ "priority" <? (50 :: Int32)
    qOrderBy [descf "priority"]
    qLimit 1

Project {title = "Website", status = Project_status_active, projectOwner = "name" =: "Alice", tasksProjectIdFkey = [Task {seq = 2, title = "Frontend", priority = 30}]}
Project {title = "API", status = Project_status_active, projectOwner = "name" =: "Bob", tasksProjectIdFkey = [Task {seq = 2, title = "Endpoints", priority = 40}]}
params: [SomeToField 50,SomeToField Project_status_active]

Formatted SQL

select t0.title "title",
       t0.status "status",
       jsonb_build_object('name',t1.name) "projectOwner",
       array_to_json(array(
           select jsonb_build_object('seq',t2.seq,'title',t2.title,'priority',t2.priority)
           from tut.tasks t2
           where t2.project_id=t0.id and t2.priority < ?
           order by t2.priority Desc nulls last
           limit 1)) "tasksProjectIdFkey"
from tut.projects t0
join tut.users t1 on t0.owner_id=t1.id
where t0.status = ?
order by t0.title Desc nulls last;

The conditions on the branch go into the subquery that gathers the tasks, so they affect only the contents of the list of tasks, not which projects get into the result. A project without matching tasks stays in the result with an empty list. That is how child branches behave. With a parent branch it is different: the parent is attached with a join, so conditions set in a qPath to it (for example, qPath "projectOwner") go into the where of the main query and filter the root rows.

Branch conditions are tied to a result field

As we saw in the chapter Quick start, conditions, ordering and limits from qPath apply to the result field with the same relation name. If the type has no such field, they silently do not get into the query. For a parent branch this means that the filter on root rows disappears together with the field.

Sometimes you need the opposite: to select the rows themselves by what is in a related table. For example, find the projects that have a task with a priority below 20. That is what the condition pchild is for. It takes the name of the relation together with the database schema, parameters for selecting child rows and a condition on them. The relation name goes through the renamer as everywhere, so we write it the same way as in qPath: "tasksProjectIdFkey".

type Tut name = "tut" ->> name

selectSch (MyAnn "projects") @("title" := Text) conn $ qRoot do
  qWhere $ pchild (Tut "tasksProjectIdFkey") defTabParam ("priority" <? (20 :: Int32))

"title" =: "Website"
params: [SomeToField 20]

Formatted SQL

select t0.title "title"
from tut.projects t0
where exists (
    select 1
    from (
        select *
        from tut.tasks t0q1
        where t0q1.project_id = t0.id) t0q1
    where t0q1.priority < ?);

The relation name here is a type: pchild, like selectSch with its annotation, takes it as a visible type argument. You cannot write it right in the call as "tut" ->> "tasksProjectIdFkey": the operator ->> also exists at the value level (it builds names in ordinary code), and GHC reads such an expression as a value. The type synonym Tut removes this ambiguity. pchild turns into exists with a subquery. The selection parameters defTabParam mean "all child rows". They can be narrowed: TabParam holds ordering (order) and limit/offset (lo, a value of type LO { limit, offset }; lo1 is limit 1). This way you can express the condition "among the first N children". Let us find the projects whose task with the highest priority value has a priority below 50:

selectSch (MyAnn "projects") @("title" := Text) conn $ qRoot do
  qWhere $ pchild (Tut "tasksProjectIdFkey")
    defTabParam { order = [descf "priority"], lo = lo1 }
    ("priority" <? (50 :: Int32))

"title" =: "API"
params: [SomeToField 50]

Formatted SQL

select t0.title "title"
from tut.projects t0
where exists (
    select 1
    from (
        select *
        from tut.tasks t0q1
        where t0q1.project_id = t0.id
        order by t0q1.priority Desc nulls last
        limit 1) t0q1
    where t0q1.priority < ?);

For "Website" the first task in this order is "Polish" with priority 100, for "Docs" it is "Outline" with priority 50, so only "API" is left in the result.

pparent works the other way: it filters rows by their parent. Let us find the tasks of active projects with a priority of at least 30:

selectSch (MyAnn "tasks") @("title" := Text :. "priority" := Int32) conn $ qRoot do
  qWhere $ pparent (Tut "tasksProjectIdFkey") ("status" =? Project_status_active)
    &&& "priority" >=? (30 :: Int32)
  qOrderBy [ascf "priority"]

"title" =: "Frontend" :. "priority" =: 30
"title" =: "Endpoints" :. "priority" =: 40
"title" =: "Polish" :. "priority" =: 100
params: [SomeToField Project_status_active,SomeToField 30]

Formatted SQL

select t0.title "title",
       t0.priority "priority"
from tut.tasks t0
where exists (
    select 1
    from (
        select *
        from tut.projects t0q1
        where t0q1.id = t0.project_id) t0q1
    where t0q1.status = ?) and t0.priority >= ?
order by t0.priority Asc nulls last;

The difference between pchild/pparent and qPath matters. pchild and pparent decide which rows get into the result, but do not add the related rows themselves to the result. A qPath to a child branch, on the contrary, does not affect which root rows are selected but defines what ends up in the nested list. With a branch to a parent, as we saw above, it is different: the parent is attached with a join, and conditions in a qPath to it filter the root rows just like pparent. It is convenient to decide first which rows you need at the root, and then tune the contents of the child branches separately.

Ordering, limit, offset and distinct¶

We have already met qOrderBy: it takes a list of fields, each with an order direction (ascf ascending, descf descending). qLimit and qOffset limit the number of rows. Let us take the second and third tasks by descending priority:

selectSch (MyAnn "tasks") @("title" := Text :. "priority" := Int32) conn $ qRoot do
  qOrderBy [descf "priority"]
  qLimit 2
  qOffset 1

"title" =: "Outline" :. "priority" =: 50
"title" =: "Endpoints" :. "priority" =: 40
params: []

Formatted SQL

select t0.title "title",
       t0.priority "priority"
from tut.tasks t0
order by t0.priority Desc nulls last
limit 2
offset 1;

qDistinct removes duplicate rows. For example, this is how to find out which project statuses occur in the database:

selectSch (MyAnn "projects") @("status" := ProjectStatus) conn $ qRoot do
  qDistinct
  qOrderBy [ascf "status"]

"status" =: Project_status_draft
"status" =: Project_status_active
"status" =: Project_status_archived
params: []

Formatted SQL

select distinct t0.status "status"
from tut.projects t0
order by t0.status Asc nulls last;

The statuses are ordered as the values are declared in the enum ('draft', 'active', 'archived' in tutorial/sql/00-create-schema.sql), not alphabetically. qDistinctOn keeps one row for each value of the given fields, and the ordering decides which row is kept. Let us keep, for each project, one task with the lowest priority:

selectSch (MyAnn "tasks") @("projectId" := Int64 :. "title" := Text :. "priority" := Int32) conn $ qRoot do
  qDistinctOn [ascf "projectId"]
  qOrderBy [ascf "priority"]

"projectId" =: 1 :. ("title" =: "Design" :. "priority" =: 10)
"projectId" =: 2 :. ("title" =: "Schema" :. "priority" =: 20)
"projectId" =: 3 :. ("title" =: "Outline" :. "priority" =: 50)
params: []

Formatted SQL

select distinct on (t0.project_id) t0.project_id "projectId",
       t0.title "title",
       t0.priority "priority"
from tut.tasks t0
order by t0.project_id Asc nulls last,t0.priority Asc nulls last;

PostgreSQL requires the fields of distinct on to come first in order by, and qDistinctOn adds them there itself.

Since the parent is attached with a join, its fields can be used in distinct on and order by too, mixed with the fields of the root. Each field is given inside the qPath of the table it belongs to, and the order of fields in the query follows the order of the calls. Let us keep, for each combination of task number and status of its project, one task — the one with the highest priority:

selectSch (MyAnn "tasks")
  @( "seq" := Int32 :. "title" := Text :. "priority" := Int32
  :. "tasksProjectIdFkey" := ("title" := Text :. "status" := ProjectStatus) )
  conn $ qRoot do
    qDistinctOn [ascf "seq"]
    qPath "tasksProjectIdFkey" do
      qDistinctOn [ascf "status"]
    qOrderBy [descf "priority"]

"seq" =: 1 :. ("title" =: "Outline" :. ("priority" =: 50 :. "tasksProjectIdFkey" =: ("title" =: "Docs" :. "status" =: Project_status_draft)))
"seq" =: 1 :. ("title" =: "Schema" :. ("priority" =: 20 :. "tasksProjectIdFkey" =: ("title" =: "API" :. "status" =: Project_status_active)))
"seq" =: 2 :. ("title" =: "Endpoints" :. ("priority" =: 40 :. "tasksProjectIdFkey" =: ("title" =: "API" :. "status" =: Project_status_active)))
"seq" =: 3 :. ("title" =: "Polish" :. ("priority" =: 100 :. "tasksProjectIdFkey" =: ("title" =: "Website" :. "status" =: Project_status_active)))
params: []

Formatted SQL

select distinct on (t0.seq,t1.status) t0.seq "seq",
       t0.title "title",
       t0.priority "priority",
       jsonb_build_object('title',t1.title,'status',t1.status) "tasksProjectIdFkey"
from tut.tasks t0
join tut.projects t1 on t0.project_id=t1.id
order by t0.seq Asc nulls last,t1.status Asc nulls last,t0.priority Desc nulls last;

Note that for a task the field tasksProjectIdFkey is the single row of its project, while for a project the field with the same name is the list of its tasks. The relation name is the same, but the meaning depends on the table we start from.

The query got distinct on (t0.seq, t1.status) and order by t0.seq, t1.status, t0.priority desc: a field of the root, a field of the parent and again a field of the root. Ordering without distinct on works the same way, by calling qOrderBy at the root and inside a qPath to the parent.

All these functions work in child branches, as we saw with qLimit in the section about qPath. But qLimit, qOffset and qDistinct make no sense in a branch to a parent, since a row has only one parent, and the compiler does not accept them there.

For a foreign key that refers to its own table, qPath does not fit. qPath decides the direction from the relation: whether it leads from the current table or into it. For the relation users_manager_id_fkey both are true, and the name alone does not tell whether we go to the manager or to the subordinates. (The result type has no such ambiguity: there the direction is seen from the field type — a list or a single row.) The compiler says so:

• qPath cannot be used for self-reference relation.
  Use qPathFromHere or qPathToHere.

  Table: NameNS "tut" "users"
  Relation: "users_manager_id_fkey"

Instead of qPath, use qPathFromHere here (by the key of the current row, that is, to the manager) and qPathToHere (to the rows that refer to the current one, that is, to the subordinates). Let us select users without a manager or with the manager id = 1, and keep in the list of subordinates only those whose name contains the letter "o":

selectSch (MyAnn "users") @Person conn $ qRoot do
  qWhere $ pnull "managerId" ||| "managerId" =? Just (1 :: Int64)
  qOrderBy [ascf "name"]
  qPathToHere "subordinates" do
    qWhere $ "name" ~~? ("%o%" :: Text)

Person {name = "Alice", manager = Nothing, subordinates = ["name" =: "Bob","name" =: "Carol"]}
Person {name = "Bob", manager = Just ("name" =: "Alice"), subordinates = []}
Person {name = "Carol", manager = Just ("name" =: "Alice"), subordinates = []}
params: [SomeToField "%o%",SomeToField (Just 1)]

Formatted SQL

select t0.name "name",
       case when t1.name is null then null else jsonb_build_object('name',t1.name) end "manager",
       array_to_json(array(
           select jsonb_build_object('name',t2.name)
           from tut.users t2
           where t2.manager_id=t0.id and t2.name ilike ?)) "subordinates"
from tut.users t0
left outer join tut.users t1 on t0.manager_id=t1.id
where (t0.manager_id is null or t0.manager_id = ?)
order by t0.name Asc nulls last;

Bob's subordinate Dave is not in the list: his name has no letter "o".

The names manager and subordinates here are just two names of the same foreign key, and the direction is set not by the name but by the function. If we wrote qPathFromHere "manager" with the same condition, it would apply to the manager and, as with any parent branch, filter the users themselves: only those whose manager's name contains "o" would remain. On our data there are none, and the result would be empty.

Aggregates¶

Aggregate functions are also set by the result type. A field of type Aggr' ACount Int64 becomes count(*), and Aggr' AMax Int32 becomes max over the column named like the field. The other fields without aggregates become grouping keys, and group by is added automatically:

selectSch (MyAnn "tasks")
  @("projectId" := Int64 :. "cnt" := Aggr' ACount Int64 :. "priority" := Aggr' AMax Int32)
  conn $ qRoot do
    qOrderBy [ascf "projectId"]

"projectId" =: 1 :. ("cnt" =: Aggr' {unAggr' = 3} :. "priority" =: Aggr' {unAggr' = 100})
"projectId" =: 2 :. ("cnt" =: Aggr' {unAggr' = 2} :. "priority" =: Aggr' {unAggr' = 40})
"projectId" =: 3 :. ("cnt" =: Aggr' {unAggr' = 1} :. "priority" =: Aggr' {unAggr' = 50})
params: []

Formatted SQL

select t0.project_id "projectId",
       count(*) "cnt",
       max(t0.priority) "priority"
from tut.tasks t0
group by project_id
order by t0.project_id Asc nulls last;

Aggr' is meant for queries with grouping: each group has at least one row, so max over a mandatory column cannot return NULL, and Maybe is not needed. Without grouping, however, max over an empty table returns NULL. For such queries there is Aggr: it requires Maybe for all functions except count. The compiler does not forbid Aggr' without grouping, but then a query to an empty table fails when reading the result, so without grouping it is better to always use Aggr:

selectSch (MyAnn "tasks")
  @("cnt" := Aggr ACount Int64 :. "priority" := Aggr AMax (Maybe Int32))
  conn qpEmpty

"cnt" =: Aggr {unAggr = 6} :. "priority" =: Aggr {unAggr = Just 100}
params: []

Formatted SQL

select count(*) "cnt",
       max(t0.priority) "priority"
from tut.tasks t0;

The functions count, min, max, sum and avg are supported.

jsonb and arrays¶

A jsonb column can be read into any type with the FromJSON and ToJSON instances from aeson. Where postgresql-simple itself parses or passes values, you also need FromField (reading at the root of a query) and ToField (single-table writes, such as the insertSch that fills this chapter's data). They are easiest to define with fromJSONField and toJSONField. The column tasks.payload holds the estimate of a task:

newtype Payload = Payload { estimate :: Int } deriving (Show, Generic)

instance FromJSON Payload
instance ToJSON Payload

instance FromField Payload where fromField = fromJSONField
instance ToField Payload where toField = toJSONField

Arrays are read into the type PgArr, which we met in the chapter Single-table DML. Its elements have type Maybe, because a PostgreSQL array may contain NULL, so in the output the watchers look like [Just 2, Just 3]:

selectSch (MyAnn "tasks")
  @("title" := Text :. "payload" := Maybe Payload :. "watchers" := PgArr Int64)
  conn $ qRoot do
    qWhere $ pnot (pnull "payload")

"title" =: "Design" :. ("payload" =: Just (Payload {estimate = 3}) :. "watchers" =: PgArr {unPgArr = [Just 2,Just 3]})
"title" =: "Frontend" :. ("payload" =: Just (Payload {estimate = 8}) :. "watchers" =: PgArr {unPgArr = [Just 3]})
"title" =: "Schema" :. ("payload" =: Just (Payload {estimate = 5}) :. "watchers" =: PgArr {unPgArr = [Just 1]})
params: []

Formatted SQL

select t0.title "title",
       t0.payload "payload",
       t0.watchers "watchers"
from tut.tasks t0
where not (t0.payload is null);

The way out: UnsafeCol, pUnsafeCond and UnsafeOrd¶

A lot can be written with pg-schema, but not everything. It is a pity when a big query over twenty tables does not fit the scheme because one condition has to compare the sum of two fields. For such cases there are "unsafe" tools: they let you put a piece of SQL into a query, while the compiler still checks everything else. UnsafeCol describes a result field as an arbitrary SQL expression: the type lists the columns substituted for the ? marks, the expression itself and the type of the result. The ? marks here stand for columns, not query parameters. The column names are checked, but the expression and whether its result matches the type (Int32) are not: an error in them shows up only at run time. pUnsafeCond sets a condition as arbitrary text, and UnsafeOrd an ordering expression together with its direction. Both build text in a small monad CondMonad, where qual @"field" returns the name of a column of the current table with the right alias. qual does not check the field name, so a typo in it also shows up only at run time:

selectSch (MyAnn "tasks")
  @("title" := Text :. "titleLength" := UnsafeCol '["title"] "length(?)" Int32)
  conn $ qRoot do
    qWhere $ pUnsafeCond do
      sq <- qual @"seq"
      priority <- qual @"priority"
      tell [SomeToField (40 :: Int32)]
      pure $ sq <> " + " <> priority <> " > ?"
    qOrderBy
      [ UnsafeOrd do
          title <- qual @"title"
          pure ("length(" <> title <> ")", Desc) ]

"title" =: "Endpoints" :. "titleLength" =: UnsafeCol ['title'] 'length(?)' 9
"title" =: "Outline" :. "titleLength" =: UnsafeCol ['title'] 'length(?)' 7
"title" =: "Polish" :. "titleLength" =: UnsafeCol ['title'] 'length(?)' 6
params: [SomeToField 40]

Formatted SQL

select t0.title "title",
       length(t0.title) "titleLength"
from tut.tasks t0
where t0.seq + t0.priority > ?
order by length(t0.title) Desc nulls last;

Here we selected the tasks whose number plus priority is greater than 40, computed the length of the title for each, and ordered the tasks by that length. The programmer, not the compiler, is responsible for such expressions, so use them only when the regular tools really are not enough. A value from the program, like the number 40 in our condition, is better passed as a parameter than pasted into the text: tell adds it to the parameter list, and a ? stands in its place in the text. This keeps SQL injection out of the query.

Summary¶

The result type defines child lists ([T]), a parent (T) and an optional parent (Maybe T).
Conditions are built from the operators =?, ~~?, &&&, |||, pnot, pin, pinArr, pnull, =??.
pchild and pparent filter root rows, a qPath to a child branch defines its contents, and a qPath to a parent filters root rows too.
Ordering can be set at any level, and limit, offset and distinct at the root and in child branches.
For a table related to itself, use qPathFromHere and qPathToHere.
Aggregates are set by the types Aggr and Aggr'.

What next¶

We can now read data in any shape we need. Writing data into several related tables at once — projects together with tasks and events — is also done in one operation, and that is the subject of the chapter Tree DML. The full list of rules for result types is in the reference.

Select¶

Data for the examples¶

Result shape¶

Conditions¶

Result branches: qPath¶

Filtering rows by related tables: pchild and pparent¶

Ordering, limit, offset and distinct¶

A table related to itself¶

Aggregates¶

jsonb and arrays¶

The way out: UnsafeCol, pUnsafeCond and UnsafeOrd¶

Summary¶

What next¶