peter.eisentraut@2ndquadrant.com
@petereisentraut

SELECT date_trunc('month', orderdate), sum(netamount)
  FROM customers JOIN orders USING (customerid)
  WHERE customers.age BETWEEN 18 AND 35
  GROUP BY date_trunc('month', orderdate)
  ORDER BY sum(netamount) DESC;

       date_trunc       |   sum
------------------------+----------
 2004-07-01 00:00:00-04 | 54849.04
 2004-04-01 00:00:00-05 | 53741.94
 2004-05-01 00:00:00-04 | 50858.31
 2004-06-01 00:00:00-04 | 50694.45
 2004-12-01 00:00:00-05 | 50298.48
 2004-08-01 00:00:00-04 | 49868.39
 2004-10-01 00:00:00-04 | 49151.65
 2004-11-01 00:00:00-05 | 49041.88
 2004-01-01 00:00:00-05 | 47463.62
 2004-03-01 00:00:00-05 | 47455.33
 2004-09-01 00:00:00-04 | 46128.39
 2004-02-01 00:00:00-05 | 44295.26
(12 rows)

Time: 15.794 ms

while (true)
{
    char *input = readline("=> ");
    if (strcmp(input, "\q") == 0)
        exit(0);
    PGresult = PQexec(conn, input);
    /* print result */
}

$ otool -L /usr/local/opt/postgresql@10/bin/psql
/usr/local/opt/postgresql@10/bin/psql:
        /usr/local/Cellar/postgresql@10/10.0/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.10.0)
        /usr/local/opt/gettext/lib/libintl.8.dylib (compatibility version 10.0.0, current version 10.5.0)
        /usr/local/opt/readline/lib/libreadline.7.dylib (compatibility version 7.0.0, current version 7.0.0)
        /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1238.50.2)

C→S  'Q' num "SELECT ..."
S→C  'T' num ...
S→C  'D' num ...
S→C  'D' num ...
...
S→C  'Z' num 'I'

C→S  'Q' ...
...

C→S  'X' num

yylex()

SELECT date_trunc('month', orderdate), sum(netamount) FROM ...

---

SELECT
IDENT
'('
SCONST
','
IDENT
')'
','
IDENT
'('
IDENT
')'
FROM
...

List:
  - SelectStmt:
      targetList:
        - ResTarget
        - ResTarget
      fromClause:
        - JoinExpr
      whereClause:
        ...
      groupClause:
        ...
      sortClause:
        - SortBy:
            ...

WHERE customers.age BETWEEN 18 AND 35

---

select oid from pg_type where typname = 'int2';
select oprcode from pg_operator
    where oprname = '>=' and oprleft = 21 and oprright = 21;
select prosrc from pg_proc where proname = 'int2ge';

---

Datum
int2ge(PG_FUNCTION_ARGS)
{
    int16       arg1 = PG_GETARG_INT16(0);
    int16       arg2 = PG_GETARG_INT16(1);

    PG_RETURN_BOOL(arg1 >= arg2);
}

- Plan:
    Node Type: "Sort"
    Sort Key:
      - "(sum(orders.netamount)) DESC"
    Plans:
      - Node Type: "Aggregate"
        Strategy: "Hashed"
        Group Key: ...
        Plans:
          - Node Type: "Hash Join"
            Hash Cond: "(orders.customerid = customers.customerid)"
            Plans:
              - Node Type: "Seq Scan"
                Relation Name: "orders"
              - Node Type: "Hash"
                Plans:
                  - Node Type: "Seq Scan"
                    Relation Name: "customers"
                    Filter: "((age >= 18) AND (age <= 35))"

rel = heap_open(oid, lock);
scandesc = heap_beginscan(rel, snapshot, nkeys, keys);

tuple = heap_getnext(scandesc, direction);

heap_endscan(scandesc);
heap_close(rel);