7.2. Табличные выражения

7.2.1.1. Соединённые таблицы

Соединённая таблица ­­­­­­— это таблица, полученная из двух других (реальных или производных от них) таблиц в соответствии с правилами соединения конкретного типа. Общий синтаксис описания соединённой таблицы:

T1 тип_соединения T2 [ условие_соединения ]

Соединения любых типов могут вкладываются друг в друга или объединяться: и T1, и T2 могут быть результатами соединения. Для однозначного определения порядка соединений предложения JOIN можно заключать в скобки. Если скобки отсутствуют, предложения JOIN обрабатываются слева направо.

Для наглядности предположим, что у нас есть таблицы t1:

 num | name
-----+------
   1 | a
   2 | b
   3 | c

и t2:

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

С ними для разных типов соединений мы получим следующие результаты:

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

Условие соединения в предложении ON может также содержать выражения, не связанные непосредственно с соединением. Это может быть полезно в некоторых запросах, но не следует использовать это необдуманно. Рассмотрите следующий запрос:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

Заметьте, что если поместить ограничение в предложение WHERE, вы получите другой результат:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

Это связано с тем, что ограничение, помещённое в предложение ON, обрабатывается до операции соединения, тогда как ограничение в WHERE — после. Это не имеет значения при внутренних соединениях, но важно при внешних.

7.2.1.2. Псевдонимы таблиц и колонок

Таблицам и ссылкам на сложные таблицы в запросе можно дать временное имя, по которому к ним можно будет обращаться в рамках запроса. Такое имя называется псевдонимом таблицы.

Определить псевдоним таблицы можно, написав

FROM табличная_ссылка AS псевдоним

или

FROM табличная_ссылка псевдоним

Ключевое слово AS является необязательным. Вместо псевдоним здесь может быть любой идентификатор.

Псевдонимы часто применяются для назначения коротких идентификаторов длинным именам таблиц с целью улучшения читаемости запросов. Например:

SELECT * FROM "очень_длинное_имя_таблицы" s JOIN "другое_длинное_имя" a
  ON s.id = a.num;

Псевдоним становится новым именем таблицы в рамках текущего запроса, т. е. после назначения псевдонима использовать исходное имя таблицы в другом месте запроса нельзя. Таким образом, следующий запрос недопустим:

SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- неправильно

Хотя в основном псевдонимы используются для удобства, они бывают необходимы, когда таблица соединяется сама с собой, например:

SELECT * FROM people AS mother JOIN people AS child
  ON mother.id = child.mother_id;

Кроме того, псевдонимы обязательно нужно назначать подзапросам (см. Подраздел 7.2.1.3).

В случае неоднозначности определения псевдонимов можно использовать скобки. В следующем примере первый оператор назначает псевдоним b второму экземпляру my_table, а второй оператор назначает псевдоним результату соединения:

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

В другой форме назначения псевдонима временные имена даются не только таблицам, но и её колонкам:

FROM табличная_ссылка [AS] псевдоним ( колонка1 [, колонка2 [, ...]] )

Если псевдонимов колонок оказывается меньше, чем фактически колонок в таблице, остальные колонки сохраняют свои исходные имена. Эта запись особенно полезна для замкнутых соединений или подзапросов.

Когда псевдоним применяется к результату JOIN, он скрывает оригинальные имена таблиц внутри JOIN. Например, это допустимый SQL-запрос:

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

а запрос:

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

ошибочный, так как псевдоним таблицы a не виден снаружи определения псевдонима c.

7.2.1.3. Подзапросы

Подзапросы, образующие таблицы, должны заключаться в скобки и им обязательно должны назначаться псевдонимы (как описано в Подразделе 7.2.1.2). Например:

FROM (SELECT * FROM table1) AS псевдоним

Этот пример равносилен записи FROM table1 AS псевдоним. Более интересные ситуации, которые нельзя свести к простому соединению, возникают, когда в подзапросе используются агрегирующие функции или группировка.

Подзапросом может также быть список VALUES:

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

Такому подзапросу тоже требуется псевдоним. Назначать псевдонимы колонкам списка VALUES не требуется, но вообще это хороший приём. Подробнее это описано в Разделе 7.7.

7.2.1.4. Табличные функции

Табличные функции — это функции, выдающие набор строк, содержащих либо базовые типы данных (скалярных типов), либо составные типы (табличные строки). Они применяются в запросах как таблицы, представления или подзапросы в предложении FROM. Колонки, возвращённые табличными функциями, можно включить в выражения SELECT, JOIN или WHERE так же, как колонки таблиц, представлений или подзапросов.

Табличные функции можно также скомбинировать, используя запись ROWS FROM. Результаты функций будут возвращены в параллельных колонках; число строк в этом случае будет наибольшим из результатов всех функций, а результаты функций с меньшим количеством строк будут дополнены значениями NULL.

вызов_функции [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_колонки [, ...])]]
ROWS FROM( вызов_функции [, ...] ) [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_колонки [, ...])]]

Если указано предложение WITH ORDINALITY, к колонкам результатов функций будет добавлена ещё одна, с типом bigint. В этой колонке нумеруются строки результирующего набора, начиная с 1. (Это обобщение стандартного SQL-синтаксиса UNNEST ... WITH ORDINALITY.) По умолчанию, эта колонка называется ordinality, но ей можно присвоить и другое имя с помощью указания AS.

Специальную табличную функцию UNNEST можно вызвать с любым числом параметров-массивов, а возвращает она соответствующее число колонок, как если бы UNNEST (Раздел 9.18) вызывалась для каждого параметра в отдельности, а результаты объединялись с помощью конструкции ROWS FROM.

UNNEST( выражение_массива [, ...] ) [WITH ORDINALITY] [[AS] псевдоним_таблицы [(псевдоним_колонки [, ...])]]

Если псевдоним_таблицы не указан, в качестве имени таблицы используется имя функции; в случае с конструкцией ROWS FROM() — имя первой функции.

Если псевдонимы колонок не указаны, то для функции, возвращающей базовый тип данных, именем колонки будет имя функции. Для функций, возвращающих составной тип, имена результирующих колонок определяются индивидуальными атрибутами типа.

Несколько примеров:

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

В некоторых случаях бывает удобно определить табличную функцию, возвращающую различные наборы колонок при разных вариантах вызова. Для этого нужно указать, что она возвращает псевдотип record. Используя такую функцию, ожидаемую структуру строк нужно описать в самом запросе, чтобы система знала, как разобрать запрос и составить его план. Записывается это так:

вызов_функции [AS] псевдоним (определение_колонки [, ...])
вызов_функции AS [псевдоним] (определение_колонки [, ...])
ROWS FROM( ... вызов_функции AS (определение_колонки [, ...]) [, ...] )

Без ROWS FROM() список определения_колонок заменяет список псевдонимов, который можно также добавить в предложении FROM; имена в определениях колонок служат псевдонимами. С ROWS FROM() список определения_колонок можно добавить к каждой функции отдельно, либо в случае с одной функцией и без предложения WITH ORDINALITY, список определения_колонок можно записать вместо списка с псевдонимами колонок после ROWS FROM().

Взгляните на этот пример:

SELECT *
    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

Здесь функция dblink (из модуля dblink) выполняет удалённый запрос. Она объявлена как функция, возвращающая тип record, так как он подойдёт для запроса любого типа. В этом случае фактический набор колонок функции необходимо описать в вызывающем её запросе, чтобы анализатор запроса знал, например, как преобразовать *.

7.2.1.5. Подзапросы LATERAL

Перед подзапросами в предложении FROM можно добавить ключевое слово LATERAL. Это позволит ссылаться в них на колонки предшествующих элементов списка FROM. (Без LATERAL каждый подзапрос выполняется независимо и поэтому не может обращаться к другим элементам FROM.)

Перед табличными функциями в предложении FROM также можно указать LATERAL, но для них это ключевое слово необязательно; в аргументах функций в любом случае можно обращаться к колонкам в предыдущих элементах FROM.

Элемент LATERAL может находиться на верхнем уровне списка FROM или в дереве JOIN. В последнем случае он может также ссылаться на любые элементы в левой части JOIN, справа от которого он находится.

Когда элемент FROM содержит ссылки LATERAL, запрос выполняется следующим образом: сначала для строки элемента FROM с целевыми колонками, или набора строк из нескольких элементов FROM, содержащих целевые колонки, вычисляется элемент LATERAL со значениями этих колонок. Затем результирующие строки обычным образом соединяются со строками, из которых они были вычислены. Эта процедура повторяется для всех строк исходных таблиц.

LATERAL можно использовать так:

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

Здесь это не очень полезно, так как тот же результат можно получить более простым и привычным способом:

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

Применять LATERAL имеет смысл в основном, когда для вычисления соединяемых строк необходимо обратиться к колонкам других таблиц. В частности, это полезно, когда нужно передать значение функции, возвращающей набор данных. Например, если предположить, что vertices(polygon) возвращает набор вершин многоугольника, близкие вершины многоугольников из таблицы polygons можно получить так:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

Этот запрос можно записать и так:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

или переформулировать другими способами. (Как уже упоминалось, в данном примере ключевое слово LATERAL не требуется, но мы добавили его для ясности.)

Особенно полезно бывает использовать LEFT JOIN с подзапросом LATERAL, чтобы исходные строки оказывались в результате, даже если подзапрос LATERAL не возвращает строк. Например, если функция get_product_names() выдаёт названия продуктов, выпущенных определённым производителем, но о продукции некоторых производителей информации нет, мы можем найти, каких именно, примерно так:

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;