sql: common table expressions

[tbg]

Common Table Expressions (CTE) are also known as WITH clauses. They're currently unsupported, but came up in a recent tech talk we gave, and apparently at least one person on the internet thinks they're really useful.

http://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/

WITH users_tasks AS (
  SELECT 
         users.email,
         some_aggregate(tasks.name) as task_list,
         projects.title
  FROM
       users,
       tasks,
       project
  WHERE
        users.id = tasks.user_id
        projects.title = tasks.project_id
  GROUP BY
           users.email,
           projects.title
)

[petermattis]

Already on the roadmap, though not currently on any near horizon.

[tbg]

Linked into roadmap.

[novabyte]

This would be great to see. We're group number two on the internet who think they'd be handy to have 😄

An example of our use case for it:

WITH p AS (
  INSERT INTO user_edge (source_id, destination_id, position, updated_at)
  SELECT ($1, $2, $3, $3)
  WHERE EXISTS (SELECT id FROM users WHERE id=$1)
)
INSERT INTO user_edge_metadata (source_id, count, state, updated_at)
SELECT $1, 1, $2, $3 FROM p
ON CONFLICT(source_id) DO
  UPDATE SET count = count + 1, updated_at = $3;

The purpose of the query is to increment a counter field in a metadata table for every relation added to the user_edge table for a given user (i.e. source_id). Let me know if you can think of a more efficent way to handle this scenario. The code is inspired by code in the FlockDB project.

[petermattis]

@novabyte You might be able to achieve a similar effect using INSERT ... RETURNING by which you can chain INSERTs into 2 different tables, though I haven't worked out the precise query for doing so.

CTEs are definitely on our radar.

[tamird]

Also note that the WHERE EXISTS (SELECT ...) portion of the query is a correlated subquery, which is separately tracked in #3288.

[knz]

@tamird in this particular case the subquery is not correlated: WHERE id=$1 refers to the placeholder $1 from the surrounding pgwire context, not to a column in the outer context. So it would run fine.

[cuongdo]

This is needed to support ActiveRecord, which issues the following query:

WITH pk_constraint AS (
  SELECT conrelid, unnest(conkey) AS connum FROM pg_constraint
  WHERE contype = 'p'
    AND conrelid = '"numbers"'::regclass
), cons AS (
  SELECT conrelid, connum, row_number() OVER() AS rownum FROM pk_constraint
)
SELECT attr.attname FROM pg_attribute attr
INNER JOIN cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.connum
ORDER BY cons.rownum

[knz]

To achieve this the recursion that instantiates the plans (currently newPlan and preparePlan) must maintain a name environment with the table names currently active at that level -- like most functional languages support Let ... in ... expressions. Then the code in getDataSource must be modified to find data sources in that environment before the global namespaces (current database and session.SearchPath).