Common Table Expressions (“

CommentsProcedural SQL (PSQL) Statements

Common Table Expressions (“`WITH … AS … SELECT`”)

Syntax

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...]

<with-list-element> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]

Table 1. Arguments for Common Table Expressions
Argument	Description
query-name	Alias for a table expression
column-name	Alias for a column in a table expression

A common table expression or CTE can be described as a virtual table or view, defined in a preamble to a main query, and going out of scope after the main query’s execution.The main query can reference any CTEs defined in the preamble as if they were regular tables or views.CTEs can be recursive, i.e. self-referencing, but they cannot be nested.

CTE Notes

A CTE definition can contain any legal query-expression, as long as it doesn’t have a “WITH…” preamble of its own (no nesting).
CTEs defined for the same main query can reference each other, but care should be taken to avoid loops.
CTEs can be referenced from anywhere in the main query.
Each CTE can be referenced multiple times in the main query, using different aliases if necessary.
When enclosed in parentheses, CTE constructs can be used as subqueries in SELECT statements, but also in UPDATEs, MERGEs etc.

In PSQL, CTEs are also supported in FOR loop headers:

for
  with my_rivers as (select * from rivers where owner = 'me')
    select name, length from my_rivers into :rname, :rlen
do
begin
  ..
end

Example

with dept_year_budget as (
  select fiscal_year,
         dept_no,
         sum(projected_budget) as budget
  from proj_dept_budget
  group by fiscal_year, dept_no
)
select d.dept_no,
       d.department,
       dyb_2008.budget as budget_08,
       dyb_2009.budget as budget_09
from department d
     left join dept_year_budget dyb_2008
       on d.dept_no = dyb_2008.dept_no
       and dyb_2008.fiscal_year = 2008
     left join dept_year_budget dyb_2009
       on d.dept_no = dyb_2009.dept_no
       and dyb_2009.fiscal_year = 2009
where exists (
  select * from proj_dept_budget b
  where d.dept_no = b.dept_no
);

Common Table Expressions (“

Common Table Expressions (“WITH …​ AS …​ SELECT”)

CTE Notes

Example

Common Table Expressions (“`WITH … AS … SELECT`”)