Вызов функции
В запросах допускается вызывать функции. Например, чтобы получить текущее время на сервере, можно вызвать функцию now() :
SELECT now() Иногда требуется приведение строки в верхний регистр, для этого используют функцию upper :
SELECT upper('elephant') Для вызова функции необходимо указать ее имя и перечислить список параметров в скобках через запятую:
имя_функции(выражение1, выражение2, . ) Параметром функции может выступать значение столбца таблицы, строковый литерал, константа, вызов функции и любое выражение над всем вышеперечисленным.
В PostgreSQL огромное количество встроенных функций, подробнее с которыми можно ознакомиться здесь. Помимо встроенных функций пользователь может реализовать и свои собственные.
Как использовать функцию в sql
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).
39.5.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 .
39.5.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 numeric 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 numeric 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 numeric AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno RETURNING balance; $$ LANGUAGE SQL;
Функция SQL должна возвращать в точности объявленный тип результата. Для этого может потребоваться добавить явное приведение. Например, предположим, что мы захотели изменить возвращаемый тип в предыдущей функции на float8 . Этот вариант не будет работать:
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$ SELECT $1 + $2; $$ LANGUAGE SQL;
несмотря на то, что в других контекстах Postgres Pro выполнил бы неявное приведение для преобразования integer в float8 . Правильный вариант выглядит так:
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$ SELECT ($1 + $2)::float8; $$ LANGUAGE SQL;
39.5.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)
Второй способ более подробно описан в Подразделе 39.5.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)
39.5.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 , показанной в Подразделе 39.5.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. Это объясняется тем, что Postgres Pro определяет сигнатуру вызова функции, рассматривая только входные параметры. Это также значит, что при таких операциях, как удаление функции, в ссылках на функцию учитываются только типы входных параметров. Таким образом, удалить эту конкретную функцию можно любой из этих команд:
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 являются входными, но обрабатывается специальным образом, как описано далее.
39.5.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]);
39.5.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 можно использовать знак = .
39.5.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 . Оно описывается в следующем разделе.
39.5.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 .
Поведение Postgres Pro с функциями, возвращающими множества, в списке выборки запроса практически не отличается от поведения с такими функциями, помещёнными в предложение LATERAL FROM . Например, запрос:
SELECT x, generate_series(1,5) AS g FROM tab;
SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
Он мог быть полностью идентичным, но в данном конкретном примере планировщик может решить перенести g во внешнюю сторону соединения, так как g не имеет фактической зависимости по времени вычисления от tab . Такое решение привело бы к изменению порядка строк. Функции, возвращающие множества, в списке выборки всегда вычисляются так, как они вычислялись бы внутри соединения с вложенным циклом с остальным предложением FROM , так что эти функции выполняются до завершения прежде чем начинается рассмотрение следующей строки из предложения FROM .
Если в списке выборки запроса используются несколько функций, возвращающих запросы, они вычисляются примерно так же, как если бы они были помещены в один элемент LATERAL ROWS FROM( . ) предложения FROM . Для каждой строки из нижележащего запроса выдаётся строка с первым результатом каждой функции, а затем строка со вторым результатом и так далее. Если какие-либо из функций, возвращающих множества, выдают меньше результатов, чем другие, то вместо недостающих данных подставляются значения NULL, так что общее число строк, выдаваемых для одной нижележащей строки, равно числу строк, которое выдаёт функция с наибольшим количеством строк в возвращаемом множестве. Таким образом, функции, возвращающие множества, выполняются совместно, пока все их множества не будут исчерпаны, а затем выполнение продолжается со следующей нижележащей строкой.
Функции, возвращающие множества, могут быть вложенными в списке выборки, но это не допускается в элементах предложения FROM . В таких случаях каждый уровень вложенности обрабатывается отдельно, как если бы это был отдельный элемент LATERAL ROWS FROM( . ) . Например, в
SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
возвращающие множества функции srf2 , srf3 и srf5 будут выполняться совместно для каждой строки tab , а затем srf1 и srf4 будут совместно применяться к каждой строке, произведённой нижними функциями.
Функции, возвращающие множества, нельзя использовать в конструкциях, вычисляемых по условию, например, CASE или COALESCE . Например, рассмотрите запрос
SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
Может показаться, что он должен выдать пять экземпляров входных строк, в которых x > 0 , и по одному экземпляру остальных строк; но на деле, так как generate_series(1, 5) будет выполняться в неявном элементе LATERAL FROM до того, как выражение CASE вообще будет рассматриваться, должно было бы выдаваться пять экземпляров абсолютно всех выходных строк. Во избежание путаницы в таких случаях выдаётся ошибка при разборе запроса.
Примечание
Если последняя команда функции — INSERT , UPDATE или DELETE с RETURNING , эта команда будет всегда выполняться до завершения, даже если функция не объявлена с указанием SETOF или вызывающий запрос не выбирает все строки результата. Все дополнительные строки, выданные предложением RETURNING , просто игнорируются, но соответствующие изменения в таблице всё равно произойдут (и будут завершены до выхода из функции).
Примечание
В Postgres Pro до версии 10 при помещении нескольких функций, возвращающих множества, в один список выборки поведение было не очень разумным, если они возвращали не одинаковое число строк. В таких случаях число выходных строк равнялось наименьшему общему множителю количеств строк, возвращаемых этими функциями. Также и вложенные функции, возвращающие множества, работали не так, как описано выше; у такой функции мог быть максимум один аргумент, возвращающий множество, и каждая вложенность вычислялась независимо. Кроме того, ранее допускалось и условное выполнение (вычисление таких функций внутри CASE и т. п.), что ещё больше всё усложняло. При написании запросов, которые должны работать и со старыми версиями Postgres Pro , рекомендуется использовать синтаксис LATERAL , так как это гарантирует одинаковый результат с разными версиями. Если в вашем запросе используется условное вычисление функции, возвращающей множество, его можно исправить, переместив проверку условия в специально созданную функцию, возвращающую множество. Например:
SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
можно заменить на
CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int) RETURNS SETOF int AS $$ BEGIN IF cond THEN RETURN QUERY SELECT generate_series(start, fin); ELSE RETURN QUERY SELECT els; END IF; END$$ LANGUAGE plpgsql; SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;
Это будет работать одинаково во всех версиях Postgres Pro .
39.5.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 .
39.5.10. Полиморфные функции SQL
Функции SQL могут быть объявлены как принимающие и возвращающие полиморфные типы anyelement , anyarray , anynonarray , anyenum и anyrange . За более подробным объяснением полиморфизма функций обратитесь к Подразделу 39.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)
39.5.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 считает, что им назначено правило сортировки по умолчанию для их типа данных (обычно это то же правило сортировки, что определено по умолчанию для базы данных, но оно может быть и другим для параметров доменных типов).
Поведение сортируемых параметров можно воспринимать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.
| Пред. | Наверх | След. |
| 39.4. Пользовательские процедуры | Начало | 39.6. Перегрузка функций |
3.3. Хранимые функции Transact-SQL
С SQL Server вы можете создавать ваши собственные функции, добавляющие и расширяющие функции, предоставляемые системой. Функции могут получать 0 или более параметров и возвращать скалярное значение или таблицу. Входные параметры могут быть любого типа, исключая timestamp, cursor, table.
Сервер SQL поддерживает три типа функций определенных пользователем:
- Скалярные функции – похожи на встроенные функции;
- Функция, возвращающая таблицу - возвращает результат единичного оператора SELECT. Он похож на объект просмотра, но имеет большую эластичность благодаря использованию параметров, и расширяет возможности индексированного объекта просмотра;
- Многооператорная функция - возвращает таблицу созданную одним или несколькими операторами Transact-SQL, чем напоминает хранимые процедуры. В отличие от процедур, на такие функции можно ссылаться в WHERE как на объект просмотра.
3.3.1. Создание хранимой функции
Создание функций очень похоже на создание процедур и объектов просмотра. Недаром мы рассматриваем все эти темы в одной главе. Для создания функции используется оператор CREATE FUNCTION. В зависимости от типа, Объявление будет отличаться. Рассмотрим все три типа объявления.
CREATE FUNCTION [ owner_name. ] function_name ( [ < @parameter_name [AS] scalar_parameter_data_type [ = default ] >[ . n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option>[ [,] . n] ] [ AS ] BEGIN function_body RETURN scalar_expression END
Функция, возвращающая таблицу:
CREATE FUNCTION [ owner_name. ] function_name ( [ < @parameter_name [AS] scalar_parameter_data_type [ = default ] >[ . n ] ] ) RETURNS TABLE [ WITH < function_option >[ [,] . n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]
CREATE FUNCTION [ owner_name. ] function_name ( [ < @parameter_name [AS] scalar_parameter_data_type [ = default ] >[ . n ] ] ) RETURNS @return_variable TABLE < table_type_definition >[ WITH < function_option >[ [,] . n ] ] [ AS ] BEGIN function_body RETURN END < function_option >::= < ENCRYPTION | SCHEMABINDING > < table_type_definition >:: = ( < column_definition | table_constraint >[ . n ] )
3.3.2. Скалярные функции в Transact-SQL
Давайте для примера создадим функцию, которая будет возвращать скалярное значение. Например, результат перемножение цены на количество указанного товара. Товар будет идентифицироваться по названию и дате, ведь мы договорились, что сочетание этих полей дает уникальность. Но будьте осторожны, при тестировании запроса, если в разделе 3.2.8 вы выполнили запрос на изменение данных и создали дубликаты покупок за 1.1.2005-го года.
Итак, посмотрим сначала на код создание скалярной функции:
CREATE FUNCTION GetSumm (@name varchar(50), @date datetime) RETURNS numeric(10,2) BEGIN DECLARE @Summ numeric(10,2) SELECT @Summ = Цена*Количество FROM Товары WHERE [Название товара]=@name AND Дата=@date; RETURN @Summ END
После оператора CREATE FUNCTION мы указываем имя функции. Далее, в скобках идут параметры, которые необходимо передать. Да, параметры должны передаваться через запятую в круглых скобках. В этом объявление отличается от процедур и эту разницу необходимо помнить.
Далее указывается ключевое слово RETURNS, за которым идет описание типа возвращаемого значения. Для скалярной функции это могут быть любые типы (строки, числа, даты и т.д.).
Код, который должна выполнять функция пишется между ключевыми словами BEGIN (начало) и END (конец). В коде можно использовать любые операторы Transact-SQL, которые мы изучали ранее. Итак, объявление нашей функции в упрощенном виде можно описать следующим образом:
CREATE FUNCTION GetSumm (@name varchar(50), @date datetime) RETURNS numeric(10,2) BEGIN -- Код функции END
Между ключевыми словами BEGIN и END у нас выполняется следующий код:
-- Объявление переменной DECLARE @Summ numeric(10,2) -- Выполнение запроса на выборку суммы SELECT @Summ = Цена*Количество FROM Товары WHERE [Название товара]=@name AND Дата=@date; -- Возврат результата RETURN @Summ
В первой строке объявляется переменная @Summ. Она нужна для хранения промежуточного результата расчетов. Далее выполняется запрос SELECT, в котором происходит поиск строки по дате и названию товара в таблице товаров. В найденной строке перемножаются поля цены и количества, и результат записывается в переменную @Summ.
Обратите внимание, что в конце запроса стоит знак точки с запятой. Каждый запрос должен заканчиваться этим символом, но в большинстве примеров мы этим пренебрегали, но в функции отсутствие символа ";" может привести к ошибке.
В последней строке возвращаем результат. Для этого нужно написать ключевое слово RETURN, после которого пишется возвращаемое значение или переменная. В данном случае, возвращаться будет содержимое переменной @Summ.
Так как функция скалярная, то и возвращаемое значение должно быть скалярным и при этом соответствовать типу, описанному после ключевого слова RETURNS.
3.3.3. Использование функций
Как выполнить такую функцию? Да также, как и многие другие системные функции (например, GETDATE()). Например, следующий пример использует функцию в операторе SELECT:
SELECT dbo.GetSumm('Картофель', '03.03.2005')
В этом примере, оператор SELECT возвращает результат выполнения функции GetSumm. Функция принадлежит пользователю dbo, поэтому перед именем я указал владельца. После имени в скобках должны быть перечислены параметры в том же порядке, что и при объявлении функции. В данном примере я запрашиваю затраты на картофель, купленный 3.3.2005.
Выполните следующий запрос и убедитесь, что он вернул тот же результат, что и созданная нами функция:
SELECT Цена*Количество FROM Товары WHERE [Название товара]='Картофель' AND Дата='03.03.2005'
Функции можно использовать не только в операторе SELECT, но и напрямую, присваивая значение переменной. Например:
DECLARE @Summ numeric(10,2) SET @Summ=dbo.GetSumm('Картофель', '03.03.2005') PRINT @Summ
В этом примере мы объявили переменную @Summ типа numeric(10,2). Именно такой тип возвращает функция. В следующей строке переменной присваивается результат выполнения Summ, с помощью SET.
Давайте посмотрим, что произойдет, если передать функции такие параметры, при которых запрос функции вернет более одной строки. В нашей таблице товаров сочетание даты и название не дает уникальности, потому что мы ее нарушили. Первичного ключа в таблице также нет, и среди товаров у меня есть четыре строки, которые имеют свои точные копии. Это нарушает правило уникальности строк в реляционных базах, но очень наглядно показывает, что в реальной жизни нарушать его нельзя.
Итак, в моей таблице есть две покупки хлеба 1.1.2005-го числа. Попробую запросить у функцию сумму:
SELECT dbo.GetSumm('Хлеб', '01.01.2005')
Результатом будет только одно число, хотя строки две. А какую строку из двух вернул сервер? Никто точно сказать не может, потому что они обе одинаковые и без единого различия. Поэтому сервер скорей всего вернул первую из строк.
3.3.4. Функция, возвращающая таблицу
В следующем примере мы создаем функцию, которая будет возвращать в качестве результата таблицу. В качестве примера, создадим функцию, которая будет возвращать таблицу товаров, и для каждой строки рассчитаем произведение колонок количества и цены:
CREATE FUNCTION GetPrice() RETURNS TABLE AS RETURN ( SELECT Дата, [Название товара], Цена, Количество, Цена*Количество AS Сумма FROM Товары )
Начало функции такое же, как у скалярной – указываем оператор CREATE FUNCTION и имя функции. Я специально создал эту функцию без параметров, чтобы вы увидели, как это делается. Не смотря на то, что параметров нет, после имени должны идти круглые скобки, в которых не надо ничего писать. Если не указать скобок, то сервер вернет ошибку и функция не будет создана.
Разница есть и в секции RETURNS, после которой указывается тип TABLE, что говорит о необходимости вернуть таблицу. После этого идет ключевое слово AS и RETURN, после которого должно идти возвращаемое значение. Для функции данного типа в секции RETURN нужно в скобках указать запрос, результат которого и будет возвращаться функцией.
Когда пишете запрос, то все его поля должны содержать имена. Если одно из полей не имеет имени, то результатом выполнения оператора CREATE FUNCTION будет ошибка. В нашем примере последнее поле является результатом перемножения полей "Цена" и "Количество", а такие поля не имеют имени, поэтому мы его задаем с помощью ключевого слова AS.
Посмотрим, как можно использовать такую функцию с помощью оператора SELECT:
SELECT * FROM GetPrice()
Так как мы используем простой оператор SELECT, то мы можем и ограничивать вывод определенными строками, с помощью ограничений в секции WHERE. Например, в следующем примере выбираем из результата функции только те строки, в которых поле "Количество" содержит значение 1:
SELECT * FROM GetPrice() WHERE Количество=1
Функция возвращает в качестве результата таблице, которую вы можете использовать как любую другую таблицу базы данных. Давайте создадим пример в котором можно будет увидеть использование функции в связи с таблицами. Для начала создадим функцию, которая будет возвращать идентификатор работников таблицы tbPeoples и объединенные в одно поле ФИО:
CREATE FUNCTION GetPeoples() RETURNS TABLE AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM tbPeoples )
Функция возвращает нам идентификатор строки, с помощью которого мы легко можем связать результат с таблицей телефонов. Попробуем сделать это с помощью простого SQL запроса:
SELECT * FROM GetPeoples() p, tbPhoneNumbers pn WHERE p.idPeoples=pn.idPeoples
Как видите, функции, возвращающие таблицы очень удобны. Они больше, чем процедуры похожи на объекты просмотра, но при этом позволяют принимать параметры. Таким образом, можно сделать так, чтобы сама функция возвращала нам только то, что нужно. Вьюшки такого не могут делать по определению. Чтобы получить нужные данные, вьюшка должна выполнить свой SELECT запрос, а потом уже во внешнем запросе мы пишем еще один оператор SELECT, с помощью которого ограничивается вывод до необходимого. Таким образом, выполняется два запроса SELECT, что для большой таблицы достаточно накладно. Функция же может сразу вернуть только то, что нужно.
Рассмотрим пример, функция GetPeoples у нас возвращает все строки таблицы. Чтобы получить только нужную фамилию, нужно писать запрос типа:
SELECT * FROM GetPeoples() WHERE FIO LIKE 'ПОЧЕЧКИН%'
В этом случае будут выполняться два запроса: этот и еще один внутри функции. Но если передавать фамилию в качестве параметра в функцию и там сделать секцию WHERE, то можно обойтись и одним запросом SELECT:
CREATE FUNCTION GetPeoples1(@Famil varchar(50)) RETURNS TABLE AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM tbPeoples WHERE vcFamil=@Famil )
3.3.5. Много операторная функция возвращающая таблицу
Все функции, созданные в разделе 3.3.5 могут возвращать таблицу, сгенерированную только одним оператором SQL. А как же тогда сделать возможность выполнять несколько операций? Например, вы можете захотеть выполнять дополнительные проверки входных параметров для обеспечения безопасности. Проверки лишними не бывает, особенно входных данных и особенно, если эти входные данные указываются пользователем.
Следующий пример показывает, как создать функцию, которая может вернуть в качестве результата таблицу, и при этом, в теле функции могут выполняться несколько операторов:
CREATE FUNCTION имя (параметры) RETURNS имя_переменной TABLE (описание вида таблицы, в которой будет представлен результат) AS BEGIN Выполнение любого количества операций RETURN END
Это упрощенный вид создания процедуры. Более полный вид мы рассматривали в начале главы, а сейчас я упростил объявление, чтобы проще было его разбирать.
Объявление больше похоже на создание скалярных функций. Первая строка без изменений. В секции RETURNS объявляется переменная, которая имеет тип TABLE. После этого, в скобках нужно описать поля результирующей таблицы. После ключевого слова AS идtт пара операторов BEGIN и END, между которыми может выполняться какое угодно количество операций. Выполнение операций заканчивается ключевым словом RETURN.
Вот тут есть одно отличие от скалярных функций – после RETURN мы указывали имя переменной, значение которой должно стать результатом. В данном случае ничего указывать не надо. Мы уже объявили переменную в секции RETURNS и описали формат этой переменной. В теле функции мы можем и должны наполнить эту переменную значениями и именно это попадет в результат.
Теперь посмотрим на пример создания функции:
CREATE FUNCTION getFIO () RETURNS @ret TABLE (idPeoples int primary key, vcFIO varchar(100)) AS BEGIN INSERT @ret SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName FROM tbPeoples; RETURN END
В данном примере в качестве результата объявлена переменная @ret, которая является таблицей из двух полей "idPeoples" типа int и "vcFIO" типа varchar длинной в 50 символов. В теле функции в эту таблицу записываются значения из таблицы tbPeoples и выполняется оператор RETURN, завершающий выполнение функции.
В использовании, такая функция ничем не отличается от рассмотренных ранее. Например, следующий запрос выбирает все данные, которые возвращает функция:
SELECT * FROM GetFIO()
3.3.6. Опции функций
При создании функций могут использоваться следующие опции SCHEMABINDING (привязать к схеме) и/или ENCRYPTION (шифровать текст функции). Если вторая опция нам уже известна по вьюшкам и процедурам (позволяет шифровать исходный код функции в системных таблицах), то вторая встречается впервые, но при этом предоставляет удобное средство защиты данных.
Если функция создана с опцией SCHEMABINDING, то объекты базы данных, на которые ссылается функция, не могут быть изменены (с использованием оператора ALTER) или удалены (с помощью оператора DROP). Например, следующая функция использует таблицу tbPeoples и при этом используется опция SCHEMABINDING:
CREATE FUNCTION GetPeoples2(@Famil varchar(50)) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO FROM dbo.tbPeoples WHERE vcFamil=@Famil )
Функция может быть связанной со схемой, только если следующие ограничения истины:
- все функции объявленные пользователем и просмотрщики на которые ссылается функция, также связаны со схемой с помощью опции SCHEMABINDING;
- объекты, на которые ссылается функция, должны использовать имя из двух частей именования: owner.objectname. При создании функции GetPeoples2 ссылка на таблицу указана именно в таком формате – dbo.tbPeoples;
- Функция и объекты должны быть расположены в одной базе данных;
- Пользователь, который создает функцию, имеет право доступа ко всем объектам, на которые ссылается функция.
Создайте функцию и попробуйте после этого удалить таблицу tbPeoples.
DROP TABLE tbPeoples
В ответ на это сервер выдаст сообщение с ошибкой о том, что объект не может быть удален, из-за присутствия внешнего ключа. Даже если избавиться от ключа, удаление будет невозможно, потому что на таблицу ссылается функция, привязанная к схеме.
Чтобы увидеть сообщение без удаления ключа, давайте добавим к таблице колонку, а потом попробуем ее удалить:
-- Добавим колонку ALTER TABLE dbo.tbPeoples ADD vcTemp VARCHAR(30) NOT NULL default '' -- Попробуем ее удалить ALTER TABLE dbo.tbPeoples DROP COLUMN vcTemp
Создание пройдет успешно, а вот во время удаления произойдет ошибка, с сообщением о том, что существует ограничение, которое зависит от колонки. Мы же не создавали никаких ограничений, а просто добавили колонку и попытались ее удалить. Ограничение уже давно существует, но не на отдельную колонку, а на все колонки таблицы и это ограничение создано функцией GetPeoples2, которая связана со схемой.
3.3.7. Изменение функций
Вы можете изменять функцию с помощью оператора ALTER FUNCTION. Общий вид для каждого варианта функции отличается. Давайте рассмотрим каждый из них.
1. Общий вид команды изменения скалярной функции:
ALTER FUNCTION [ owner_name. ] function_name ( [ < @parameter_name scalar_parameter_data_type [ = default ] >[ . n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option>[. n] ] [ AS ] BEGIN function_body RETURN scalar_expression END
2. Общий вид изменения функции, возвращающей таблицу:
ALTER FUNCTION [ owner_name. ] function_name ( [ < @parameter_name scalar_parameter_data_type [ = default ] >[ . n ] ] ) RETURNS TABLE [ WITH < function_option >[ . n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]
3. Общий вид команды изменения функции с множеством операторов, возвращающей таблицу.
ALTER FUNCTION [ owner_name. ] function_name ( [ < @parameter_name scalar_parameter_data_type [ = default ] >[ . n ] ] ) RETURNS @return_variable TABLE < table_type_definition >[ WITH < function_option >[ . n ] ] [ AS ] BEGIN function_body RETURN END < function_option >::= < ENCRYPTION | SCHEMABINDING > < table_type_definition >:: = ( < column_definition | table_constraint >[ . n ] )
Следующий пример показывает упрощенный вариант команды, изменяющей функцию:
ALTER FUNCTION dbo.tbPeoples AS -- Новое тело функции
3.3.8. Удаления функций
Если вы внимательно читали об объектах просмотра и функциях, то не трудно догадаться, как можно удалить функцию. Конечно же для этого используется оператор DROP FUNCTION:
DROP FUNCTION dbo.GetPeoples2
Оконные функции SQL простым языком с примерами
Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений - все написано максимально простым языком для базового понимания.
P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи)))
Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом
--создание таблицы create table student_grades ( name varchar, subject varchar, grade int); -- наполнение таблицы данными insert into student_grades ( values ('Петя', 'русский', 4), ('Петя', 'физика', 5), ('Петя', 'история', 4), ('Маша', 'математика', 4), ('Маша', 'русский', 3), ('Маша', 'физика', 5), ('Маша', 'история', 3)); --запрос всех данных из таблицы select * from student_grades;
SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции.
Оконная функция в SQL - функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.
Партиции (окна из набора строк) - это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.
В чем заключается главное отличие оконных функций от функций агрегации с группировкой?
При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.
При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.
Порядок расчета оконных функций в SQL запросе
Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.
Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING
Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой.
После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки.
Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе.
Синтаксис оконных функций
Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.
Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.
Классы Оконных функций
Множество оконных функций можно разделять на 3 класса:
- Агрегирующие (Aggregate)
- Ранжирующие (Ranking)
- Функции смещения (Value)
Агрегирующие:
Можно применять любую из агрегирующих функций - SUM, AVG, COUNT, MIN, MAX
select name, subject, grade, sum(grade) over (partition by name) as sum_grade, avg(grade) over (partition by name) as avg_grade, count(grade) over (partition by name) as count_grade, min(grade) over (partition by name) as min_grade, max(grade) over (partition by name) as max_grade from student_grades;
Ранжирующие:
В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования.
ROW_NUMBER() - функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.
RANK() - функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
DENSE_RANK() - то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.
select name, subject, grade, row_number() over (partition by name order by grade desc), rank() over (partition by name order by grade desc), dense_rank() over (partition by name order by grade desc) from student_grades;
Про NULL в случае ранжирования:
Для SQL пустые NULL значения будут определяться одинаковым рангом
Функции смещения:
Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.
LAG() - функция, возвращающая предыдущее значение столбца по порядку сортировки.
LEAD() - функция, возвращающая следующее значение столбца по порядку сортировки.
На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.
--создание таблицы create table grades_quartal ( name varchar, quartal varchar, subject varchar, grade int); --наполнение таблицы данными insert into grades_quartal ( values ('Петя', '1 четверть', 'физика', 4), ('Петя', '2 четверть', 'физика', 3), ('Петя', '3 четверть', 'физика', 4), ('Петя', '4 четверть', 'физика', 5) ); --запрос всех данных из таблицы select * from grades_quartal;
select name, quartal, subject, grade, lag(grade) over (order by quartal) as previous_grade, lead(grade) over (order by quartal) as next_grade from grades_quartal;
FIRST_VALUE()/LAST_VALUE() - функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия.
В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется.