Select

SELECT vs PERFORM vs CALL

A snag with writing procedures rather than functions is CALL procedurename(args) can’t be combined with FROM... WHERE clauses. A way around this in PL/PGSQL is to use PERFORM functioname(args) which acts like a procedure in that its result is ignored, so doesn’t require a dummy variable while opening the way for it to also be used as a normal query invoked by SELECT.

There are many tokens to tweak queries returning sets or aggregators.

Table Expressions

These always have a FROM clause.

SELECT
FROM
WHERE

LATERAL

I really battled with using jsonb_each(utility) until I discovered

SELECT key, value
FROM tictactoe_states, LATERAL jsonb_each(utility)
WHERE id < 10;

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined. A common application is providing an argument value for a set-returning function.

It turns out LATERAL is implied, so the above can be abbreviated to:

SELECT key, value
FROM tictactoe_states, jsonb_each(utility)
WHERE id < 10;

Implicitly stating LATERAL is good style.

The LATERAL key word is unnecessary in this example, but we use it for clarity.

The order of the tables after FROM is important because I initially tried

SELECT key, value
FROM jsonb_each(utility), tictactoe_states
WHERE id < 10;

Which resulted in

ERROR:  column "utility" does not exist
LINE 2: FROM jsonb_each(utility), tictactoe_states

Something that also wasted a couple of hours was learning settof record returning functions such as jsonb_each have to be in the FROM part of the query because

SELECT jsonb_each(utility) FROM tictactoe_states WHERE id < 10;

results in

 jsonb_each 
------------
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
 (o,50)
 (x,50)
(18 rows)

and I’ve yet to figure out how to retrieve elements from these records.

Aggregate Functions

Aggregate functions are not allowed in WHERE

filter

FILTER can be combined with an aggregate expression.

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
FROM weather
GROUP BY city;

SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);

FILTER is much like WHERE, except that it removes rows only from the input of the particular aggregate function that it is attached to. Here, the count aggregate counts only rows with temp_lo below 45; but the max aggregate is still applied to all rows, so it still finds the reading of 46. — Aggregate Functions