Как вызвать функцию в postgresql

SQL-Ex blog

Здесь мы узнаем об определяемых пользователем функциях в PostgreSQL, а именно, что такое пользовательская функция, как ее создать и использовать в запросах и PgAdmin GUI, об их преимуществах. Будут рассмотрены некоторые примеры.

Что такое функция пользователя?

Функция пользователя представляет собой группу произвольных операторов SQL, предназначенных для выполнения некоторой задачи. Эти функции не поставляются из коробки и обычно создаются для обработки специфичных сценариев. Можно выполнять операции select, insert, update, delete внутри функции. Функция в PostgreSQL может создаваться на любом языке, таком как SQL, C, PL/pgSQL, Python и т.д.

Важно отметить, что функция не может обрабатывать транзакцию, т.е. нельзя использовать COMMIT или ROLLBACK.

Как создать функцию пользователя?

Функцию можно создать двумя способами — либо с помощью кода PostgreSQL, либо в PgAdmin GUI. Давайте рассмотрим оба варианта.

Базовый синтаксис

В этом синтаксисе после предложения CREATE OR REPLACE FUNCTION указывается имя функции (function_name) со списком аргументов или параметров. Затем после ключевого слова RETURNS объявляется тип данных возвращаемого значения (return_datatype ). return_datatype может быть одним из типов данных PostgreSQL, например, character, integer, double и т.п. Так же из функции PostgreSQL можно вернуть таблицу.

Далее после ключевого слова DECLARE объявляются используемые в функции переменные IN, OUT. Далее в блоке BEGIN-END задается тело функции (function_body). function_body обычно содержит бизнес-логику функции. Затем после ключевого слова RETURN указывается имя переменной (variable_name), которая содержит возвращаемое из функции значение.

Наконец, после ключевого слова LANGUAGE указывается язык (language_name), на котором написана функция.

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$ 
DECLARE 
declaration; 
[. ] 
BEGIN 
< function_body > 
[.. logic] 
RETURN < variable_name | value > 
END; 
LANGUAGE language_name;

Запросы можно выполнять либо в оболочке PostgreSQL (PSQL), либо в среде PgAdmin (query tool).

Пример

Тут мы создаем функцию, которая возвращает текущую дату и время сервера.

CREATE FUNCTION getTimestamp() RETURNS timestamp AS $$ 
BEGIN 
RETURN CURRENT_TIMESTAMP; 
END; $$ 
LANGUAGE PLPGSQL;

Для получения списка всех функций в пределах схемы базы данных:

Давайте выполним нашу функцию:

PgAdmin GUI

Давайте создадим ту же функцию в программе PgAdmin.

Шаг 1 -> В навигаторе перейти к серверам->Database->Schema->Function->Create->Function, как показано ниже:

Шаг 2 -> Указать имя, владельца, схему и написать комментарий, если нужно.

Шаг 3 -> Задать аргументы функции, тип возвращаемого значения и язык, что объяснялось в предыдущем разделе.

Шаг 4 -> Ввести бизнес-логику на вкладке code

Шаг 5 -> Затем ввести параметры на вкладке Parameters и нажать кнопку Save для создания функции.

Примеры пользовательских функций

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

Замечание. Для выполнения запросов можно использовать либо инструмент запросов в PgAdmins, либо консоль Psql. В этом руководстве мы использовали инструмент запросов PgAdmin для этой цели. Оба этих редактора входят в состав последних версий PgAdmin.

Пример 1: сложить два числа

Здесь создается функция с именем addNumbers. Эта функция принимает два целочисленных параметра на входе и возвращает одно целое число на выходе.

CREATE FUNCTION addNumbers(val1 integer, val2 integer) RETURNS integer AS $$ 
BEGIN 
RETURN val1 + val2; 
END; $$ 
LANGUAGE PLPGSQL;

Вот эта функция, созданная в PgAdmin:

Функция появляется слева в ветке Functions:

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

Пример 2: В этом примере мы увидим, как выполнить условный оператор SELECT в функции

Эта функция используется для выполнения операции выборки из таблицы Stocks. Функция возвращает количество тех акций, чья стоимость меньше, чем входной параметр price_cap. Переменная, объявленная в блоке declare содержит выходное значение.

create function get_stocks(price_cap int) 
returns int 
language plpgsql 
as 
$$ 
declare 
stock_count integer; 
begin 
select count(*) 
into stock_count 
from public."Stocks" 
where stock_price < price_cap;
 
return stock_count; 
end; 
$$;

Давайте создадим ее в PgAdmin.

Давайте взглянем на данные в таблице:

Теперь выполним функцию, чтобы проверить ее поведение:

Что касается нашей таблицы, то имеется 3 акции, чья стоимость меньше 1000, и функция их и возвращает.

Пример 3: возвращение таблицы

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

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

CREATE OR REPLACE FUNCTION get_allStocks (price_cap int) 
RETURNS TABLE ( 
stock_serial_no int, 
stock varchar, 
price_of_stock bigint 
) 
AS $$ 
BEGIN 
RETURN QUERY SELECT 
stock_id, 
stock_name, 
stock_price 
FROM 
public."Stocks" 
WHERE 
stock_price < price_cap;
END; $$ 
LANGUAGE 'plpgsql';

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

Преимущества использования функций пользователя

1. Легко вызывать
2. Может использоваться в разных местах без ограничений, которые есть у хранимых процедур
3. Допускается перегрузка функции, функции, имеющие различные параметры, могут иметь одинаковое имя

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Как вызвать функцию в postgresql

PostgreSQL позволяет вызывать функции с именованными параметрами, используя запись с позиционной или именной передачей аргументов. Именная передача особенно полезна для функций со множеством параметров, так как она делает связь параметров и аргументов более явной и надёжной. В позиционной записи значения аргументов функции указываются в том же порядке, в каком они описаны в определении функции. При именной передаче аргументы сопоставляются с параметрами функции по именам и указывать их можно в любом порядке. Для каждого варианта вызова также учитывайте влияние типов аргументов функций, описанное в Разделе 10.3.

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

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

Мы рассмотрим все три варианта записи на примере следующей функции:

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT;

Функция concat_lower_or_upper имеет два обязательных параметра: a и b . Кроме того, есть один необязательный параметр uppercase , который по умолчанию имеет значение false . Аргументы a и b будут сложены вместе и переведены в верхний или нижний регистр, в зависимости от параметра uppercase . Остальные тонкости реализации функции сейчас не важны (подробнее о них рассказано в Главе 36).

4.3.1. Позиционная передача

Позиционная передача — это традиционный механизм передачи аргументов функции в PostgreSQL . Пример такой записи:

SELECT concat_lower_or_upper('Hello', 'World', true); concat_lower_or_upper ----------------------- HELLO WORLD (1 row)

Все аргументы указаны в заданном порядке. Результат возвращён в верхнем регистре, так как параметр uppercase имеет значение true . Ещё один пример:

SELECT concat_lower_or_upper('Hello', 'World'); concat_lower_or_upper ----------------------- hello world (1 row)

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

4.3.2. Именная передача

При именной передаче для аргумента добавляется имя, которое отделяется от выражения значения знаками => . Например:

SELECT concat_lower_or_upper(a => 'Hello', b => 'World'); concat_lower_or_upper ----------------------- hello world (1 row)

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

SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true); concat_lower_or_upper ----------------------- HELLO WORLD (1 row) SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World'); concat_lower_or_upper ----------------------- HELLO WORLD (1 row)

Для обратной совместимости поддерживается и старый синтаксис с «:=»:

SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World'); concat_lower_or_upper ----------------------- HELLO WORLD (1 row)

4.3.3. Смешанная передача

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

SELECT concat_lower_or_upper('Hello', 'World', uppercase => true); concat_lower_or_upper ----------------------- HELLO WORLD (1 row)

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

Примечание

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

Пред.	Наверх	След.
4.2. Выражения значения	Начало	Глава 5. Определение данных

35.4. Функции на языке запросов ( 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).

35.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.

35.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;

35.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)';

Однако это использование считается устаревшим, так как провоцирует путаницу.

Иногда бывает удобно образовать составное значение аргумента на лету. Это позволяет сделать конструкция 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)

Второй способ описан более подробно в Подразделе 35.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

-- Это то же самое: -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30; SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30; youngster ----------- Sam Andy

Подсказка: Равнозначность функциональной записи и записи с атрибутом позволяет использовать функции с составными типами для имитации "вычисляемых полей" . Например, используя предыдущее определение double_salary(emp), мы можем записать

SELECT emp.name, emp.double_salary FROM emp;

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

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

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

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

И ещё один способ использовать функцию, возвращающую составной тип — вызвать её как табличную функцию, как описано в Подразделе 35.4.7.

35.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, показанной в Подразделе 35.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 являются входными, но обрабатывается специальным образом, как описано далее.

35.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 соответствуют одно или несколько вхождений типа его элемента, но не его собственного типа.

Но иногда бывает полезно передать функции с переменными параметрами уже подготовленный массив; особенно когда одна функция с переменными параметрами хочет передавать свой массив параметров другой. Это можно сделать, указав 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]);

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

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

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 можно использовать знак =.

35.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. Оно описывается в следующем разделе.

35.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 даёт более ожидаемые результаты при вызове нескольких таких функций и поэтому рекомендуется использовать его.

35.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.

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

Функции SQL могут быть объявлены как принимающие и возвращающие полиморфные типы anyelement, anyarray, anynonarray, anyenum и anyrange. За более подробным объяснением полиморфизма функций обратитесь к Подразделу 35.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)

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

Когда функция SQL принимает один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции, в зависимости от правил сортировки, связанных с фактическими аргументами, как описано в Разделе 22.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 считает, что им назначено правило сортировки по умолчанию для их типа данных (обычно это то же правило сортировки, что определено по умолчанию для базы данных, но оно может быть и другим для параметров доменных типов).

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

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

PostgreSQL | Как создать простую функцию, которая возвращает строку Hello World?

Скриншот из графического интерфейса pgAdmin 4:

Синтаксис команды CREATE FUNCTION внушительный. В нём много вариаций оформления и написания. Давайте по порядку.

Состав кода SQL

CREATE FUNCTION — сказали SQL, что будем создавать обычную функцию без проверки существования такой с таким именем

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

RETURNS — в нашем случае функция только ВОЗВРАЩАЕТ и ничего не ДЕЛАЕТ. Поэтому мы пишем RETURNS.

text — указывает тип возвращаемых данных из функции. Обычный текст произвольной длины.

LANGUAGE ‘sql’ — указывает, тело нашей функции будет написано на стандартном языке SQL.

AS — говорит о том, то дальше пойдёт код оформления тела функции.

$BODY$ — открывающий тег тела функции.

SELECT ‘Hello World!’; — Код, который выполняет наша функция при обращении к ней. Стандартный запрос на получение обычной строки.

$BODY$ ; — закрывающий тег тела функции и ТОЧКА с ЗАПЯТОЙ.

Что ещё?

Если в схеме уже существует функция с таким именем, то этот код не создаст её. Будет ошибка создания.

Есть возможность переписать её при помощи команды CREATE OR REPLACE FUNCTION. Но тут есть нюансы. Читайте официальную документацию по этому вопросу.

Волшебную команду SELECT нужно воспринимать как ГЛОБАЛЬНЫЙ ОБЪЕКТ, который умеет много чего делать.

Как вызвать функцию в PostgreSQL?

Для вызова нашей функции можно воспользоваться командой:

select test1.fun_hello_world();

Как удалить функцию в PostgreSQL?

Для удаления нашей функции, необходимо прописать команду:

drop function test1.fun_hello_world();

Информационные ссылки

Официальный сайт PostgreSQL — https://www.postgresql.org

Графический интерфейс pgAdmin — https://www.pgadmin.org

Вам также может понравиться

Опубликовано 08.11.2022

PostgreSQL | pg_dump Отказано в доступе (Permission denied) в Debian

Делая свой первый ДАМП базы данных в PostgreSQL на Debian-сервере, можно столкнуться с проблемой «Отказано в доступе (Permission denied)«. В терминале Debian […]

Опубликовано 02.12.2022

PostgreSQL | CROSS JOIN для двух таблиц

Предложение «CROSS JOIN» представляет собой обычное перемножение записей из двух разных таблиц. Каждый результат перемножения — это отдельная запись в новой результирующей […]

Опубликовано 25.05.2022

PostgreSQL | Проверка содержимого строки перед вставкой в таблицу по условию

У нас есть строка: ‘https://efim360.ru’ Мы хотим проверять валидность данной строки к URL-адресу. Наш валидатор примитивный. Мы будем просто проверять начинается ли […]

Опубликовано 13.07.2023

PostgreSQL | Как подменять NULL-значение при выборке данных через SELECT?

NULL-значения, которые база данных отдаёт на клиент могут не представлять никакой ценности для конечного пользователя. В большинстве случаев от них нужно избавляться. […]