Select 1 from что это

Урок 5. Ограничение и смещение

Кроме фильтров и сортировок, в SELECT-запросах можно также ограничивать выборку. Например, когда вам нужные не все данные, а скажем только первые 10 строк. Для этого существует конструкция LIMIT.

Работать в этом уроке будем с такой таблицей:

Таблица products

id	name	count	price	country
1	Телевизор	3	43200.00	RU
2	Микроволновая печь	4	3200.00	UA
3	Холодильник	3	12000.00	RU
4	Роутер	1	1340.00	US
5	Компьютер	0	26150.00	RU
6	Холодильник 2	2	14390.00	BL
7	Чайник	8	1200.00	RU
8	Дрон	1	45990.00	CH
9	Вентилятор	3	3000.00	RU

Давайте попробуем вывести 5 самых дорогих товаров. Сперва напишем базовый SQL-запрос:

SELECT * FROM products ORDER BY price DESC

И с помощью данного запроса мы получим все товары, отсортированные по цене в обратном порядке — ORDER BY price DESC. То есть дорогие товары будут сверху:

Таблица products с сортировкой

id	name	count	price	country
8	Дрон	1	45990.00	CH
1	Телевизор	3	43200.00	RU
5	Компьютер	0	26150.00	RU
6	Холодильник 2	2	14390.00	BL
3	Холодильник	3	12000.00	RU
2	Микроволновая печь	4	3200.00	UA
9	Вентилятор	3	3000.00	RU
4	Роутер	1	1340.00	US
7	Чайник	8	1200.00	RU

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

SELECT * FROM products ORDER BY price DESC LIMIT 5

И LIMIT 5 как раз и говорит базе, что нам нужны только 5 первых (верхних) записей. После выполнения запроса мы получим такую таблицу:

5 самых дорогих товара

id	name	count	price	country
8	Дрон	1	45990.00	CH
1	Телевизор	3	43200.00	RU
5	Компьютер	0	26150.00	RU
6	Холодильник 2	2	14390.00	BL
3	Холодильник	3	12000.00	RU

Теперь давайте получим 5 самых дорогих товаров, которые есть на складе. Для этого нужно написать такой SQL-запрос:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5

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

5 самых дорогих товара, которые есть на складе

id	name	count	price	country
8	Дрон	1	45990.00	CH
1	Телевизор	3	43200.00	RU
6	Холодильник 2	2	14390.00	BL
3	Холодильник	3	12000.00	RU
2	Микроволновая печь	4	3200.00	UA

Обратите внимание на порядок следования блоков запроса:

1. Сперва идет SELECT FROM — выбрать ИЗ;
2. Затем блок условия WHERE;
3. После сортировка ORDER BY;
4. И завершает ограничение LIMIT.

Первый блок SELECT FROM обязательный, так как он говорит, что мы хотим сделать.

Остальные блоки опциональны. То есть мы можем убрать любой из них, главное, чтобы сохранялся порядок: (1) WHERE (2) ORDER BY (3) LIMIT.

Смещение с помощью OFFSET

Но вернемся снова к LIMIT. И помимо ограничения выборки, мы также можем делать смещение. Например, искать не первые 5 самых дорогих товаров, а следующую пятерку.

Для этого после LIMIT нужно добавить блок OFFSET:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5 OFFSET 5

И собственно конструкция OFFSET указывает на то, сколько записей нужно пропустить. После запуска мы получим 3 товара, так как изначально в таблице у нас 8 товаров, из которых 5 мы пропускаем с помощью OFFSET, а затем с помощью LIMIT выводим еще 5, но так как после пропуска остается всего 3 товара, то их мы и видим:

Таблица products после смещения и сортировки

id	name	count	price	country
6	Холодильник 2	2	14390.00	BL
3	Холодильник	3	12000.00	RU
2	Микроволновая печь	4	3200.00	UA

Постраничный просмотр

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

Тогда в LIMIT мы прописываем двойку, а для OFFSET изначально ставим 0:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 0

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

Товары для первой страницы

id	name	count	price	country
8	Дрон	1	45990.00	CH
1	Телевизор	3	43200.00	RU

Далее нам нужно получить следующую пару. Для этого делаем пропуск первых двух товаров:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 2

Товары для второй страницы

id	name	count	price	country
5	Компьютер	0	26150.00	RU
6	Холодильник 2	2	14390.00	BL

Теперь получим товары на третьей странице и для этого мы можем воспользоваться вот такой формулой:
(P — 1) x N, где P — это номер страницы, которая нам нужна, а N – это количество товаров, которые мы выводим на странице.

Значение N у нас фиксировано и указывается в блоке LIMIT. То есть сейчас это 2.
Значение P мы выбираем из набора целых чисел: 1, 2, 3 и тд.
Результат вычисления мы подставляем в блок OFFSET.

Сейчас мы делаем расчеты для третьей страницы, поэтому получаем:
(P — 1) x N = (3 — 1) x 2 = 4

Ставим четверку в OFFSET и получаем финальный SQL-запрос:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 4

Который вернет следующую таблицу:

Товары для третьей страницы:

id	name	count	price	country
5	Компьютер	0	26150.00	RU
6	Холодильник 2	2	14390.00	BL

С основой мы закончили, но хочу отметить одну особенность конструкции OFFSET, а именно то, что OFFSET в MySQL является частью LIMIT.

То есть мы можем использовать или чистый LIMIT, или LIMIT в паре с OFFSET, но не можем использовать OFFSET сам по себе, так как это будет синтаксической ошибкой.

Также мы не можем менять LIMIT и OFFSET местами.

Что ж, на этом с базовыми возможностями ограничений и смещений мы заканчиваем, а в следующем уроке рассмотрим особенности применения LIMIT и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.

Следующий урок

Урок 6. TOP, LIMIT, FETCH и OFFSET в других базах

Изучаем особенности использования TOP, LIMIT, FETCH и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.

Полный курс с практикой

• 57 уроков
• 261 задание
• Сертификат
• Поддержка преподавателя
• Доступ к курсу навсегда

• Можно в рассрочку

7 распространенных ошибок в SQL-запросах, которые делал каждый (почти)

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

Однако работая с большим количеством junior-ов, мы раз от раза находим в их решениях одни и те же ошибки. Реально — иногда просто создается ощущение, что они копируют друг у друга код.

Кстати, иногда такая же участь постигает и специалистов более высокого полета.

Сегодня мы решили собрать 7 таких ошибок в одном месте, чтобы как можно меньше людей их совершали.

Примечание: Ошибки будут 2 видов — реальные ошибки и своего рода best practices, которым часто не следуют.

Но, обо всем по порядку 🙂

1. Преобразование типов

Мы привыкли, что в математике мы всегда можем разделить одно число на другое и получить ответ. Если нацело не получается, то в виде дроби.

В SQL это не всегда так работает. Например, в PostgreSQL деление двух целых чисел друг на друга даст целочисленный ответ. Это можно проверить как для целочисленных столбцов, так и для чисел.

SELECT a/b FROM demo # столбец целых чисел SELECT 1 / 2 # 0

Аналогичные запросы, например, в MySQL дадут дробное число, как и положено.

Если Вы точно не уверены или хотите подстраховаться, то лучше всегда явно делать преобразование типов. Например:

SELECT a::NUMERIC/b FROM demo SELECT a*1.0/b FROM demo SELECT CAST(1 AS FLOAT)/2 FROM demo

Все перечисленные примеры дадут нужный ответ.

2. HAVING вместо WHERE

Часто встречается ошибка — оператор HAVING используется вместо WHERE в запросах с агрегацией. Это неверно!

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

SELECT date, COUNT(*) FROM transactions t WHERE date >= '2019-01-01' GROUP BY date HAVING COUNT(*) = 2 

Здесь мы сначала отсеиваем строки, в которых хранятся записи до 2019 года. После этого формируем группы и оставляем только те, в которых ровно две записи.

Некоторые же пишут так:

SELECT date, COUNT(*) FROM transactions t GROUP BY date HAVING COUNT(*) = 2 AND date >= '2019-01-01'

Так делать не нужно 🙂

Кстати, для закрепления этой темы мы специально делали задачку «Отфильтрованные продажи» у себя на платформе. Если интересно порешать и другие задачки по SQL — welcome 🙂

3. Алиасы и план запроса

Если «проговаривать SQL-запрос» словами, то получится что-то такое:

В таблице есть старая цена, а есть новая цена. Их разность я назову diff. Я хочу отобрать только те строки, где значение diff больше 100.

Звучит вполне логично. Но в SQL прям так реализовать не получится — и многие попадаются в эту ловушку.

Вот неправильный запрос:

SELECT old_price - new_price AS diff FROM goods WHERE diff > 100

Ошибка его заключается в том, что мы используем алиас столбца diff внутри оператора WHERE.

Да, это выглядит вполне логичным, но мы не можем так сделать из-за порядка выполнения операторов в SQL-запросе. Дело в том, что фильтр WHERE выполняется сильно раньше оператора SELECT (а значит и AS). Соответственно, в момент выполнения столбца diff просто не существует. Об этом, кстати, и говорит ошибка:

ERROR: column "diff" does not exist

Правильно будет использовать подзапрос или переписать запрос следующим образом:

SELECT old_price - new_price AS diff FROM goods WHERE old_price - new_price > 100

Важно: Внутри ORDER BY вы можете указывать алиас — этот оператор выполняется уже после SELECT.

Кстати, мы тут делали карточку, где наглядно показывается последовательность выполнения операторов. Возможно, это вам пригодится.

4. Не использовать COALESCE

Пришло время неочевидных пунктов. Но сейчас мы поясним свои чаяния.

COALESCE — это оператор, который принимает N значений и возвращает первое, которое не NULL. Если все NULL, то вернется NULL.

Нужен этот оператор для того, чтобы в расчеты случайно не попадали пропуски. Такие пропуски всегда сложно заметить, потому что при расчете среднего на основании ста тысяч строк вы вряд ли заметите подвох, даже если 1000 просто будет отсутствовать. Обычно такие численные пропуски заполняют средними значениями/минимальными/максимальными/медианными/средними или с помощью какой-то интерполяции — зависит от задачи.

Мы же рассмотрим нечисловой пример, а вполне себе бизнесовый. Например, есть таблица клиентов Clients. В поле name заносится имя пользователя.

Отдел маркетинга решил сделать email-рассылку, которая начинается с фразы:

Очевидно, что если name is NULL, то это превратится в тыкву:

Вот в таких случаях и помогает COALESCE:

SELECT COALESCE(name, 'Дорогой друг') FROM Clients

Совет: Лучше всегда перестраховываться. Особенно это касается вычислений и агрегирований — там вы не найдете ошибку примерно никогда, так что лучше подложить соломку.

5. Игнорирование CASE

Если вы используете CASE, то иногда вы можете сократить свои запросы в несколько раз.

Вот, например, была задача — вывести поле sum со знаком «-», если type=1 и со знаком «+», если type=0.

Пользователь предложил такое решение:

SELECT id, sum FROM transactions t WHERE type = 0 UNION ALL SELECT id, -sum FROM transactions t WHERE type = 1

В целом, не так плохо. Но это всего лишь промежуточный запрос, задача была намного масштабней и таких конструкций в итоге было наворочено очень много.

А вот то же самое с CASE:

SELECT id, CASE WHEN type = 0 THEN sum ELSE -sum END FROM transactions t 

Так более того, CASE можно использовать еще много для чего. Например, чтобы сделать из «длинной» таблицы «широкую».

А еще, кстати, COALESCE, который мы обсуждали выше — это просто «синтаксический сахар» и обертка вокруг CASE. Если интересно — мы подробно это описали в статье.

6. Лишние подзапросы

Из-за того, что многие пишут SQL-запросы также, как это «звучит» в их голове, получается нагромождение подзапросов.

Это проходит с опытом — начинаешь буквально «мыслить на SQL» и все становится ок. Но первое время появляются такие штуки:

SELECT id, LAG(neg) OVER(ORDER BY id) AS lg FROM ( SELECT id, sm, -sm AS neg FROM ( SELECT id, sum AS sm FROM transactions t ) t ) t1

И это еще не все — можно и побольше накрутить. Но зачем так, если можно так:

SELECT id, LAG(-sum) OVER(ORDER BY id) FROM transactions t 

Совет: Если пока сложно, не надо сразу бросаться писать оптимизированными конструкциями. Напишите сначала, как сможете, а потом пытайтесь сократить.

Как говорил дядюшка Кнут:

Преждевременная оптимизация — корень всех зол

7. Неправильное использование оконных функций

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

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

Одна из таких вещей — поведение оконной функции LAST_VALUE и прочих.

Например, когда мы пишем запрос:

WITH cte AS ( SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year UNION SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year union SELECT 'Sales' AS department, 35 AS employees, 2018 AS year UNION SELECT 'Sales' AS department, 25 AS employees, 2019 AS year ) SELECT c.*, LAST_VALUE(employees) OVER (PARTITION BY department ORDER BY year) AS emp FROM cte c

Мы ожидаем увидеть 2 раза по 10 для департамента Маркетинг и 2 раза по 25 для Продаж. Однако такой запрос дает иную картину:

Получается, что запрос тупо продублировал значения из столбца employees. Как так?

Лезем в документацию PostgreSQL и видим:

Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей.

Ага, вот и ответ. То есть каждый раз у нас окно — это не весь набор строк, а только до текущей строки.

Получается, есть два способа вылечить такое поведение:

• Убрать ORDER BY
• Добавить определение рамки

Вот, например, второй вариант:

WITH cte AS ( SELECT 'Marketing' AS department, 50 AS employees, 2018 AS year UNION SELECT 'Marketing' AS department, 10 AS employees, 2019 AS year union SELECT 'Sales' AS department, 35 AS employees, 2018 AS year UNION SELECT 'Sales' AS department, 25 AS employees, 2019 AS year ) SELECT c.*, LAST_VALUE(employees) OVER ( PARTITION BY department ORDER BY year ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS emp FROM cte c

Кстати, такую тему подняла наша подписчица в Телеграме под постом «7 самых важных оконных функций». Спасибо ей!

А вас рады будем видеть в числе подписчиков 🙂

Эпилог

Эти 7 ошибок — не единственные, которые часто встречаются среди новичков и даже профессионалов. У нас есть еще одна пачка тезисов по этому поводу — но это уже тема другой статьи.

Если вам есть что добавить — будем рады продолжить обсуждение в комментариях. Возможно, чей-то код станет лучше и чище в результате нашей беседы 🙂

SELECT 1 — какой смысл этого запроса?

Какой смысл несет SQL запрос SELECT 1 . Знаю, что он возвращает единицу, но зачем это нужно? Где его применяют на практике?

Отслеживать
8,331 4 4 золотых знака 26 26 серебряных знаков 48 48 бронзовых знаков
задан 3 дек 2011 в 9:54
13.6k 13 13 золотых знаков 62 62 серебряных знака 122 122 бронзовых знака

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

Ну например, что бы проверить, работает ли база вообще. Потому что если она этот запрос не может обработать, то дела плохи.

Отслеживать
ответ дан 3 дек 2011 в 9:55
112k 6 6 золотых знаков 93 93 серебряных знака 159 159 бронзовых знаков

Вообще говоря, это вычисляемый столбец: select . А так в зависимости от контекста. Например, с предикатом [NOT] EXISTS, т.к. выводить что-нибудь в списке столбцов смысла не имеет.

Отслеживать
ответ дан 3 дек 2011 в 10:38
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков

С помощью подобных запросов, можно почти бесплатно генерировать числовые последовательности.

Сам по себе — просто получить константу)

select n2.n * 4 + n1.n + 1 as n from ( select n = 0 union all select 1 union all select 2 union all select 3 ) n1, ( select n = 0 union all select 1 union all select 2 union all select 3 ) n2 

Select 1 from что это

SQL-функции выполняют произвольный список операторов SQL и возвращают результат последнего запроса в списке. В простом случае (не с множеством) будет возвращена первая строка результата последнего запроса. (Помните, что понятие « первая строка » в наборе результатов с несколькими строками определено точно, только если присутствует ORDER BY .) Если последний запрос вообще не вернёт строки, будет возвращено значение NULL.

Кроме того, можно объявить SQL-функцию как возвращающую множество (то есть, несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип , либо объявив её с указанием RETURNS TABLE( столбцы ) . В этом случае будут возвращены все строки результата последнего запроса. Подробнее это описывается ниже.

Тело SQL-функции должно представлять собой список SQL-операторов, разделённых точкой с запятой. Точка с запятой после последнего оператора может отсутствовать. Если только функция не объявлена как возвращающая void , последним оператором должен быть SELECT , либо INSERT , UPDATE или DELETE с предложением RETURNING .

Любой набор команд на языке SQL можно скомпоновать вместе и обозначить как функцию. Помимо запросов SELECT , эти команды могут включать запросы, изменяющие данные ( INSERT , UPDATE и DELETE ), а также другие SQL-команды. (В SQL -функциях нельзя использовать команды управления транзакциями, например COMMIT , SAVEPOINT , и некоторые вспомогательные команды, в частности VACUUM .) Однако последней командой должна быть SELECT или команда с предложением RETURNING , возвращающая результат с типом возврата функции. Если же вы хотите определить функцию SQL, выполняющую действия, но не возвращающую полезное значение, вы можете объявить её как возвращающую тип void . Например, эта функция удаляет строки с отрицательным жалованьем из таблицы emp :

CREATE FUNCTION clean_emp() RETURNS void AS ' DELETE FROM emp WHERE salary < 0; ' LANGUAGE SQL; SELECT clean_emp(); clean_emp ----------- (1 row)

Примечание

Прежде чем начинается выполнение команд, разбирается всё тело SQL-функции. Когда SQL-функция содержит команды, модифицирующие системные каталоги (например, CREATE TABLE ), действие таких команд не будет видимо на стадии анализа последующих команд этой функции. Так, например, команды CREATE TABLE foo (. ); INSERT INTO foo VALUES(. ); не будут работать, как ожидается, если их упаковать в одну SQL-функцию, так как foo не будет существовать к моменту разбору команды INSERT . В подобных ситуациях вместо SQL-функции рекомендуется использовать PL/PgSQL .

Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно для этого удобнее всего заключать строковую константу в доллары (см. Подраздел 4.1.2.4). Если вы решите использовать обычный синтаксис с заключением строки в апострофы, вам придётся дублировать апострофы ( ' ) и обратную косую черту ( \ ) (предполагается синтаксис спецпоследовательностей) в теле функции (см. Подраздел 4.1.2.1).

36.4.1. Аргументы SQL -функций

К аргументам SQL-функции можно обращаться в теле функции по именам или номерам. Ниже приведены примеры обоих вариантов.

Чтобы использовать имя, объявите аргумент функции как именованный, а затем просто пишите это имя в теле функции. Если имя аргумента совпадает с именем какого-либо столбца в текущей SQL-команде внутри функции, имя столбца будет иметь приоритет. Чтобы всё же перекрыть имя столбца, дополните имя аргумента именем самой функции, то есть запишите его в виде имя_функции . имя_аргумента . (Если и это имя будет конфликтовать с полным именем столбца, снова выиграет имя столбца. Неоднозначности в этом случае вы можете избежать, выбрав другой псевдоним для таблицы в SQL-команде.)

Старый подход с нумерацией позволяет обращаться к аргументам, применяя запись $ n : $1 обозначает первый аргумент, $2 — второй и т. д. Это будет работать и в том случае, если данному аргументу назначено имя.

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

Аргументы SQL-функции могут использоваться только как значения данных, но не как идентификаторы. Например, это приемлемо:

INSERT INTO mytable VALUES ($1);

а это не будет работать:

INSERT INTO $1 VALUES (42);

Примечание

Возможность обращаться к аргументам SQL-функций по именам появилась в PostgreSQL 9.2. В функциях, которые должны работать со старыми серверами, необходимо применять запись $ n .

36.4.2. Функции SQL с базовыми типами

Простейшая возможная функция SQL не имеет аргументов и просто возвращает базовый тип, например integer :

CREATE FUNCTION one() RETURNS integer AS $$ SELECT 1 AS result; $$ LANGUAGE SQL; -- Альтернативная запись строковой константы: CREATE FUNCTION one() RETURNS integer AS ' SELECT 1 AS result; ' LANGUAGE SQL; SELECT one(); one ----- 1

Заметьте, что мы определили псевдоним столбца в теле функции для её результата (дали ему имя result ), но этот псевдоним не виден снаружи функции. Вследствие этого, столбец результата получил имя one , а не result .

Практически так же легко определяются функции SQL , которые принимают в аргументах базовые типы:

CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$ SELECT x + y; $$ LANGUAGE SQL; SELECT add_em(1, 2) AS answer; answer -------- 3

Мы также можем отказаться от имён аргументов и обращаться к ним по номерам:

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $1 + $2; $$ LANGUAGE SQL; SELECT add_em(1, 2) AS answer; answer -------- 3

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

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno; SELECT 1; $$ LANGUAGE SQL;

Пользователь может выполнить эту функцию, чтобы дебетовать счёт 17 на 100 долларов, так:

SELECT tf1(17, 100.0);

В этом примере мы выбрали имя accountno для первого аргумента, но это же имя имеет столбец в таблице bank . В команде UPDATE имя accountno относится к столбцу bank.accountno , так для обращения к аргументу нужно записать tf1.accountno . Конечно, мы могли бы избежать этого, выбрав другое имя для аргумента.

На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno; SELECT balance FROM bank WHERE accountno = tf1.accountno; $$ LANGUAGE SQL;

Эта функция изменяет баланс и возвращает полученное значение. То же самое можно сделать в одной команде, применив RETURNING :

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno RETURNING balance; $$ LANGUAGE SQL;

36.4.3. Функции SQL со сложными типами

В функциях с аргументами составных типов мы должны указывать не только, какой аргумент, но и какой атрибут (поле) этого аргумента нам нужен. Например, предположим, что emp — таблица, содержащая данные работников, и это же имя составного типа, представляющего каждую строку таблицы. Следующая функция double_salary вычисляет, каким было бы чьё-либо жалование в случае увеличения вдвое:

CREATE TABLE emp ( name text, salary numeric, age integer, cubicle point ); INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)'); CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$ SELECT $1.salary * 2 AS salary; $$ LANGUAGE SQL; SELECT name, double_salary(emp.*) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)'; name | dream ------+------- Bill | 8400

Обратите внимание на запись $1.salary позволяющую выбрать одно поле из значения строки аргумента. Также заметьте, что в вызывающей команде SELECT указание имя_таблицы .* выбирает всю текущую строку таблицы как составное значение. На строку таблицы можно сослаться и просто по имени таблицы, например так:

SELECT name, double_salary(emp) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)';

Однако это использование считается устаревшим, так как провоцирует путаницу. (Подробнее эти две записи составных значений строки таблицы описаны в Подразделе 8.16.5.)

Иногда бывает удобно образовать составное значение аргумента на лету. Это позволяет сделать конструкция ROW . Например, так можно изменить данные, передаваемые функции:

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream FROM emp;

Также возможно создать функцию, возвращающую составной тип. Например, эта функция возвращает одну строку emp :

CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT text 'None' AS name, 1000.0 AS salary, 25 AS age, point '(2,2)' AS cubicle; $$ LANGUAGE SQL;

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

Учтите два важных требования относительно определения функции:

Порядок в списке выборки внутреннего запроса должен в точности совпадать с порядком следования столбцов в таблице, связанной с составным типом. (Имена столбцов, как показывает пример выше, для системы значения не имеют.)

Вы должны привести выражения в соответствие с определением составного типа, либо вы получите такие ошибки:

 ERROR: function declared to return emp returns varchar instead of text at column 1 

Ту же функцию можно определить другим способом:

CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT ROW('None', 1000.0, 25, '(2,2)')::emp; $$ LANGUAGE SQL;

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

Мы можем вызывать эту функцию напрямую, либо указав её в выражении значения:

SELECT new_emp(); new_emp -------------------------- (None,1000.0,25,"(2,2)")

либо обратившись к ней, как к табличной функции:

SELECT * FROM new_emp(); name | salary | age | cubicle ------+--------+-----+--------- None | 1000.0 | 25 | (2,2)

Второй способ более подробно описан в Подразделе 36.4.7.

Когда используется функция, возвращающая составной тип, может возникнуть желание получить из её результата только одно поле (атрибут). Это можно сделать, применяя такую запись:

SELECT (new_emp()).name; name ------ None

Дополнительные скобки необходимы во избежание неоднозначности при разборе запроса. Если вы попытаетесь выполнить запрос без них, вы получите ошибку:

SELECT new_emp().name; ERROR: syntax error at or near "." LINE 1: SELECT new_emp().name; ^

(ОШИБКА: синтаксическая ошибка (примерное положение: "."))

Функциональную запись также можно использовать и для извлечения атрибутов:

SELECT name(new_emp()); name ------ None

Как рассказывалось в Подразделе 8.16.5, запись с указанием поля и функциональная запись являются равнозначными.

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

CREATE FUNCTION getname(emp) RETURNS text AS $$ SELECT $1.name; $$ LANGUAGE SQL; SELECT getname(new_emp()); getname --------- None (1 row)

36.4.4. Функции SQL с выходными параметрами

Альтернативный способ описать результаты функции — определить её с выходными параметрами, как в этом примере:

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int) AS 'SELECT x + y' LANGUAGE SQL; SELECT add_em(3,7); add_em -------- 10 (1 row)

Это по сути не отличается от версии add_em , показанной в Подразделе 36.4.2. Действительная ценность выходных параметров в том, что они позволяют удобным способом определить функции, возвращающие несколько столбцов. Например:

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS 'SELECT x + y, x * y' LANGUAGE SQL; SELECT * FROM sum_n_product(11,42); sum | product -----+--------- 53 | 462 (1 row)

Фактически здесь мы определили анонимный составной тип для результата функции. Показанный выше пример даёт тот же конечный результат, что и команды:

CREATE TYPE sum_prod AS (sum int, product int); CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod AS 'SELECT $1 + $2, $1 * $2' LANGUAGE SQL;

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

Заметьте, что выходные параметры не включаются в список аргументов при вызове такой функции из SQL. Это объясняется тем, что PostgreSQL определяет сигнатуру вызова функции, рассматривая только входные параметры. Это также значит, что при таких операциях, как удаление функции, в ссылках на функцию учитываются только типы входных параметров. Таким образом, удалить эту конкретную функцию можно любой из этих команд:

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int); DROP FUNCTION sum_n_product (int, int);

Параметры функции могут быть объявлены как IN (по умолчанию), OUT , INOUT или VARIADIC . Параметр INOUT действует как входной (является частью списка аргументов при вызове) и как выходной (часть типа записи результата). Параметры VARIADIC являются входными, но обрабатывается специальным образом, как описано далее.

36.4.5. Функции SQL с переменным числом аргументов

Функции SQL могут быть объявлены как принимающие переменное число аргументов, с условием, что все « необязательные » аргументы имеют один тип данных. Необязательные аргументы будут переданы такой функции в виде массива. Для этого в объявлении функции последний параметр помечается как VARIADIC ; при этом он должен иметь тип массива. Например:

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$ SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL; SELECT mleast(10, -1, 5, 4.4); mleast -------- -1 (1 row)

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

SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- это не будет работать

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

Но иногда бывает полезно передать функции с переменными параметрами уже подготовленный массив; особенно когда одна функция с переменными параметрами хочет передавать свой массив параметров другой. Также это более безопасный способ вызывать такую функцию, существующую в схеме, где могут создавать объекты недоверенные пользователи; см. Раздел 10.3. Это можно сделать, добавив VARIADIC в вызов:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

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

Также указание VARIADIC даёт единственную возможность передать пустой массив функции с переменными параметрами, например, так:

SELECT mleast(VARIADIC ARRAY[]::numeric[]);

Простой вызов SELECT mleast() не будет работать, так как переменным параметрам должен соответствовать минимум один фактический аргумент. (Можно определить вторую функцию с таким же именем mleast , но без параметров, если вы хотите выполнять такие вызовы.)

Элементы массива, создаваемые из переменных параметров, считаются не имеющими собственных имён. Это означает, что передать функции с переменными параметрами именованные аргументы нельзя (см. Раздел 4.3), если только при вызове не добавлено VARIADIC . Например, этот вариант будет работать:

SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);

А эти варианты нет:

SELECT mleast(arr => 10); SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);

36.4.6. Функции SQL со значениями аргументов по умолчанию

Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются, когда функция вызывается с недостаточным количеством фактических аргументов. Так как аргументы можно опускать только с конца списка фактических аргументов, все параметры после параметра со значением по умолчанию также получат значения по умолчанию. (Хотя запись с именованными аргументами могла бы ослабить это ограничение, оно всё же остаётся в силе, чтобы позиционные ссылки на аргументы оставались действительными.) Независимо от того, используете вы эту возможность или нет, она требует осторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3.

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3) RETURNS int LANGUAGE SQL AS $$ SELECT $1 + $2 + $3; $$; SELECT foo(10, 20, 30); foo ----- 60 (1 row) SELECT foo(10, 20); foo ----- 33 (1 row) SELECT foo(10); foo ----- 15 (1 row) SELECT foo(); -- не работает из-за отсутствия значения по умолчанию для первого аргумента ERROR: function foo() does not exist

(ОШИБКА: функция foo() не существует) Вместо ключевого слова DEFAULT можно использовать знак = .

36.4.7. Функции SQL , порождающие таблицы

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

CREATE TABLE foo (fooid int, foosubid int, fooname text); INSERT INTO foo VALUES (1, 1, 'Joe'); INSERT INTO foo VALUES (1, 2, 'Ed'); INSERT INTO foo VALUES (2, 1, 'Mary'); CREATE FUNCTION getfoo(int) RETURNS foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper -------+----------+---------+------- 1 | 1 | Joe | JOE (1 row)

Как показывает этот пример, мы можем работать со столбцами результата функции так же, как если бы это были столбцы обычной таблицы.

Заметьте, что мы получаем из данной функции только одну строку. Это объясняется тем, что мы не использовали указание SETOF . Оно описывается в следующем разделе.

36.4.8. Функции SQL , возвращающие множества

Когда SQL-функция объявляется как возвращающая SETOF некий_тип , конечный запрос функции выполняется до завершения и каждая строка выводится как элемент результирующего множества.

Это обычно используется, когда функция вызывается в предложении FROM . В этом случае каждая строка, возвращаемая функцией, становится строкой таблицы, появляющейся в запросе. Например, в предположении, что таблица foo имеет то же содержимое, что и раньше, мы выполняем:

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

Тогда в ответ мы получим:

fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows)

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

CREATE TABLE tab (y int, z int); INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8); CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int) RETURNS SETOF record AS $$ SELECT $1 + tab.y, $1 * tab.y FROM tab; $$ LANGUAGE SQL; SELECT * FROM sum_n_product_with_tab(10); sum | product -----+--------- 11 | 10 13 | 30 15 | 50 17 | 70 (4 rows)

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

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

SELECT * FROM nodes; name | parent -----------+-------- Top | Child1 | Top Child2 | Top Child3 | Top SubChild1 | Child1 SubChild2 | Child1 (6 rows) CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$ SELECT name FROM nodes WHERE parent = $1 $$ LANGUAGE SQL STABLE; SELECT * FROM listchildren('Top'); listchildren -------------- Child1 Child2 Child3 (3 rows) SELECT name, child FROM nodes, LATERAL listchildren(name) AS child; name | child --------+----------- Top | Child1 Top | Child2 Top | Child3 Child1 | SubChild1 Child1 | SubChild2 (5 rows)

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

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

SELECT listchildren('Top'); listchildren -------------- Child1 Child2 Child3 (3 rows) SELECT name, listchildren(name) FROM nodes; name | listchildren --------+-------------- Top | Child1 Top | Child2 Top | Child3 Child1 | SubChild1 Child1 | SubChild2 (5 rows)

Заметьте, что в последней команде SELECT для Child2 , Child3 и т. д. строки не выдаются. Это происходит потому, что listchildren возвращает пустое множество для этих аргументов, так что строки результата не генерируются. Это же поведение мы получаем при внутреннем соединении с результатом функции с применением LATERAL .

Примечание

Если последняя команда функции — INSERT , UPDATE или DELETE с RETURNING , эта команда будет всегда выполняться до завершения, даже если функция не объявлена с указанием SETOF или вызывающий запрос не выбирает все строки результата. Все дополнительные строки, выданные предложением RETURNING , просто игнорируются, но соответствующие изменения в таблице всё равно произойдут (и будут завершены до выхода из функции).

Примечание

Ключевая проблема использования функций, возвращающих множества, в списке выборки, а не в предложении FROM , заключается в том, что при вызове в одном списке выборки нескольких таких функций, результат будет не вполне разумным. (На самом деле, если вы сделаете это, вы получите выходные строки в количестве, равном наименьшему общему кратному чисел строк, которые будут выданы всеми функциями, возвращающими множества.) Синтаксис LATERAL даёт более ожидаемые результаты при вызове нескольких таких функций и поэтому рекомендуется использовать его.

36.4.9. Функции SQL , возвращающие таблицы ( TABLE )

Есть ещё один способ объявить функцию, возвращающую множества, — использовать синтаксис RETURNS TABLE( столбцы ) . Это равнозначно использованию одного или нескольких параметров OUT с объявлением функции как возвращающей SETOF record (или SETOF тип единственного параметра, если это применимо). Этот синтаксис описан в последних версиях стандарта SQL, так что этот вариант может быть более портируемым, чем SETOF .

Например, предыдущий пример с суммой и произведением можно также переписать так:

CREATE FUNCTION sum_n_product_with_tab (x int) RETURNS TABLE(sum int, product int) AS $$ SELECT $1 + tab.y, $1 * tab.y FROM tab; $$ LANGUAGE SQL;

Запись RETURNS TABLE не позволяет явно указывать OUT и INOUT для параметров — все выходные столбцы необходимо записать в списке TABLE .

36.4.10. Полиморфные функции SQL

Функции SQL могут быть объявлены как принимающие и возвращающие полиморфные типы anyelement , anyarray , anynonarray , anyenum и anyrange . За более подробным объяснением полиморфизма функций обратитесь к Подразделу 36.2.5. В следующем примере полиморфная функция make_array создаёт массив из двух элементов произвольных типов:

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray; intarray | textarray ----------+----------- | (1 row)

Обратите внимание на приведение типа 'a'::text , определяющее, что аргумент имеет тип text . Оно необходимо, если аргумент задаётся просто строковой константой, так как иначе он будет воспринят как имеющий тип unknown , а массив типов unknown является недопустимым. Без этого приведения вы получите такую ошибку:

 ERROR: could not determine polymorphic type because input has type "unknown" 

(ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип "unknown")

Функция с полиморфными аргументами может иметь фиксированный тип результата, однако обратное не допускается. Например:

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$ SELECT $1 > $2; $$ LANGUAGE SQL; SELECT is_greater(1, 2); is_greater ------------ f (1 row) CREATE FUNCTION invalid_func() RETURNS anyelement AS $$ SELECT 1; $$ LANGUAGE SQL; ERROR: cannot determine result data type DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.

(ОШИБКА: не удалось определить тип результата; ПОДРОБНОСТИ: Функция, возвращающая полиморфный тип, должна иметь минимум один полиморфный аргумент.)

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

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS 'select $1, array[$1,$1]' LANGUAGE SQL; SELECT * FROM dup(22); f2 | f3 ----+--------- 22 | (1 row)

Полиморфизм также можно применять с функциями с переменными параметрами. Например:

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL; SELECT anyleast(10, -1, 5, 4); anyleast ---------- -1 (1 row) SELECT anyleast('abc'::text, 'def'); anyleast ---------- abc (1 row) CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$ SELECT array_to_string($2, $1); $$ LANGUAGE SQL; SELECT concat_values('|', 1, 4, 2); concat_values --------------- 1|4|2 (1 row)

36.4.11. Функции SQL с правилами сортировки

Когда функция SQL принимает один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции, в зависимости от правил сортировки, связанных с фактическими аргументами, как описано в Разделе 23.2. Если правило сортировки определено успешно (то есть не возникло конфликтов между неявно установленными правилами сортировки аргументов), оно неявно назначается для всех сортируемых параметров. Выбранное правило будет определять поведение операций, связанных с сортировкой, в данной функции. Например, для показанной выше функции anyleast , результат

SELECT anyleast('abc'::text, 'ABC');

будет зависеть от правила сортировки по умолчанию, заданного в базе данных. С локалью C результатом будет строка ABC , но со многими другими локалями это будет abc . Нужное правило сортировки можно установить принудительно, добавив предложение COLLATE к одному из аргументов функции, например:

SELECT anyleast('abc'::text, 'ABC' COLLATE "C");

С другой стороны, если вы хотите, чтобы функция работала с определённым правилом сортировки, вне зависимости от того, с каким она была вызвана, вставьте предложения COLLATE где требуется в определении функции. Эта версия anyleast всегда будет сравнивать строки по правилам локали en_US :

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i); $$ LANGUAGE SQL;

Но заметьте, что при попытке применить правило к несортируемому типу данных, возникнет ошибка.

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

Поведение сортируемых параметров можно воспринимать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.

Пред.	Наверх	След.
36.3. Пользовательские функции	Начало	36.5. Перегрузка функций