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

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

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

В предложении FROM табличная функция может получить псевдоним, но также может остаться без псевдонима. Если функция используется в предложении 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. Когда такая функция используется в запросе, ожидаемая структура строки должна быть задана в самом запросе, так что система может знать как обработать и спланировать запрос. Рассмотрим пример:

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.2. Предолжение WHERE

Синтаксис WHERE Clause следующий:

WHERE условие_поиска

где условие поиска является любым значением выражения (см. Section 4.2), которое возвращает значение логического типа boolean.

После того как выполнена обработка предложения FROM, каждая строка, полученной в итоге производной вируальной таблицы, проверяется согласно условию поиска. Если результатом этого условия является истина, то строка оставляется в виртуальной таблице, в противном случае (например, если результатом является ложь или NULL), строка отбрасывается. Условие поиска обычно использует по крайней мере одну колонку из таблицы, полученой в предложении FROM; этого не требуется, но в противном случае предложение WHERE будет фактически бесполезным.

Note: Условие соединения INNER JOIN модет быть написано либо в предложении WHERE, либо в предложении JOIN. Например, эти табличные выражения эквивалентны:

FROM a, b WHERE a.id = b.id AND b.val > 5

и:

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

или возможно даже:

FROM a NATURAL JOIN b WHERE b.val > 5

Какой из вышеперечисленных запросов использовать вам, в основном вопрос стиля. Синтаксис JOIN в предложенииFROM, предположительно, не будет решением, переносимым на другие SQL СУБД, даже если они работают по стандарту SQL. Для OUTER JOIN в этом случае выбора нет: такие соединения должны выполняться в предложенииFROM. Предложение ON/USING в OUTER JOIN не эквивалентно условию в WHERE, потому что результаты запроса будут разными.

Вот несколько примеров предложения WHERE:

SELECT... FROM fdt WHERE c1 > 5 SELECT... FROM fdt WHERE c1 IN (1, 2, 3) SELECT... FROM fdt WHERE c1 IN (SELECT c1 FROM t2) SELECT... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) SELECT... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100 SELECT... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdt — это таблица, полученная в предложении FROM. Строки, которые не попадают под условие поиска в выражении WHERE, удаляются из fdt. Обратите внимание, используйте скалярные позапросы как выражения значений. Как и другие запросы, подзапросы могут возвращать сложные табличные выражения. Также обратите внимание, как fdt используется в подзапросах. Полное имя c1 такое как fdt.c1 необходимо только если c1 также является именем колонки во входной таблице подзапроса. Но полное имя колонки обеспечивает однозначность, даже когда она не требуется. Этот пример показывает как пространство имён колонок внешнего запроса расширяет пространаство имён колонок во внутренних запросах.

7.2.3. Предложения GROUP BY и HAVING

После прохода через фильтр WHERE, полученная входная таблица может быть сгруппирована, используя предложение GROUP BY, а также из неё могут удалены строки, используя предложение HAVING.

SELECT список _ выборки FROM... [WHERE...] GROUP BY ссылка_на_группирумую_колонку [, ссылка_на_группирумую_колонку ]...

GROUP BY Clause используется для группировки вместе тех строк таблицы, которые имеют те же самые значения во всех перечисленных колонках. Порядок, в котором перечисляются колоки значения не имеет. Эффект группировки состоит в комбинировании каждого списка строк, имеющих общие значения колонок в одну сгруппированную строку, которая представляет все строки в данной группе. Это осуществляется исключением избыточности в выводе и/или подсчётом агрегатов, которые применяются к группам. Например:

=> SELECT * FROM test1; x | y---+--- a | 3 c | 2 b | 5 a | 1(4 rows) => SELECT x FROM test1 GROUP BY x; x--- a b c(3 rows)

Во втором запросе, мы не можем написать SELECT * FROM test1 GROUP BY x, потому что в нём нет ни одного значения в колонке y, которое может быть ассоциировано с каждой группой. Группируемые колонки могут быть перечислены в списке выбора, в случае, если они имеют хотя бы одно значение в каждой группе.

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

=> SELECT x, sum(y) FROM test1 GROUP BY x; x | sum---+----- a | 4 b | 5 c | 2(3 rows)

Здесь sum является агрегатной функцией, которая подсчитывает одно значение во всей группе. Больше информации о доступных агрегатных функциях можно найти в Section 9.18.

Tip: Группировка без агрегирующих выражений эффективно подсчитывает список отдельных значений в колонке. Это же может быть достигнуто, используя предложение DISTINCT (см. Section 7.3.3).

Здесь другой пример: он подсчитывает полные продажи для каждого продукта (вместо подсчёта полных продаж всех продуктов):

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales FROM products p LEFT JOIN sales s USING (product_id) GROUP BY product_id, p.name, p.price;

В этом примере, колонки product_id, p.name и p.price должны быть в предложении GROUP BY, поскольку в запросе они указываются в списке выборки (но см. ниже). Колонки s.units нет в списке GROUP BY, поскольку она используется только в агрегирующем выражении (sum(...)), которое представляет собой продажи продукта. Для каждого продукта, запрос возвращает суммарную строку о всех продажах этого продукта.

Если таблица products сделана так, что product_id является первичным ключём, то его должно быть достаточно для группировки по product_id в данном выше примере, так как name и price должны быть функционально зависимы от product_id и таким образом не должно быть двусмысленности в отношенни того какие name и price будут соответствовать каждой группе product_id.

В строгом SQL, GROUP BY может группировать только по колонкам исходной таблицы, но PostgreSQL расширяет поведение группировки, позволяя GROUP BY группировать по колонкам в списке выбора. Также позволяется группировка по значениям вместо имён колонок.

Если таблица была сгруппирована с помощью предложения GROUP BY, но интерес представляют только определённые группы, можно использовать предложение HAVING, которое очень похоже на предложение WHERE, но только для удаления групп из результата. Синтаксис такой:

SELECT список_выбора FROM... [WHERE...] GROUP BY... HAVING логическое_выражение

Выражения в предложении HAVING могут использовать как сгруппированные выражения, так и несгруппированные выражения (которые необходимо обработать агрегатной функцией).

Ghbvth:

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3; x | sum---+----- a | 4 b | 5(2 rows) => SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c'; x | sum---+----- a | 4 b | 5(2 rows)

Ещё один, более реалистичный пример:

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit FROM products p LEFT JOIN sales s USING (product_id) WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks' GROUP BY product_id, p.name, p.price, p.cost HAVING sum(p.price * s.units) > 5000;

В этом примере, предложение WHERE выбирает строки с колонками, которые не группируются (выражение истинно только для продаж за последние четыре недели), в то время как предложение HAVING ограничивает вывод сгруппированных результатов только для случаев, когда объём продаж превышает 5000. Обратите внимание, что агрегатные выражения могут разными во всех частях запроса.

Если запрос содержит вызовы агрегатный функций, но не содержит предложение GROUP BY, группировка всё-равно будет осуществлена: результатом будет одна сгруппированная строка (или не будет никаких строк, если данная сгруппированная строка будет исключена предложением HAVING). Тоже самое будет, если запрос содержит предложение HAVING, даже без каких-либо вызовов агрегатных функций GROUP BY.