Как вызвать функцию в 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

Написал функцию, в которой пару операторов IF выполняют некоторое действие со столбцами. Как мне вызывать эту функцию постоянно? пробовал через триггерную функцию, но тогда всё ломается и перестают появляться новые строки в таблицах без вызова каких-либо ошибок, поэтому триггерные функции не подходят точно. А когда вызываю обычную функцию через select func1(), то всё прекрасно работает. Вот как этот вызов функции зациклить? Мне нужно знать только как постоянно вызывать функции и всё.

Отслеживать

задан 25 апр 2023 в 6:24

1 4 4 бронзовых знака

Ну, например, через pgpro_scheduler.

25 апр 2023 в 7:27

pgpro_scheduler это фишка платной расширенной версии PostgreSQL, а как в бесплатной версии это сделать?

25 апр 2023 в 7:32

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

Как в PostgeSQL вызвать функцию из другой схемы?

Всем привет. Подскажите, как можно в триггер функции SQL вызвать функцию из другой схемы?
Имеем PosgreSQL, основная схема public и схема amqp, которая образовалась когда подключил pg_amqp.
В схеме public есть триггер функция по отслеживанию INSERT/UPDATE/DELETE в таблице:

Код функции

BEGIN if (TG_OP = 'INSERT') then INSERT INTO table_1_audit_log ( table_1_id, old_row_data, new_row_data, dml_type, dml_timestamp ) VALUES( NEW.id, null, to_jsonb(NEW), 'INSERT', CURRENT_TIMESTAMP ); RETURN NEW; elsif (TG_OP = 'UPDATE') then INSERT INTO table_1_audit_log ( table_1_id, old_row_data, new_row_data, dml_type, dml_timestamp ) VALUES( NEW.id, to_jsonb(OLD), to_jsonb(NEW), 'UPDATE', CURRENT_TIMESTAMP ); RETURN NEW; elsif (TG_OP = 'DELETE') then INSERT INTO table_1_audit_log ( table_1_id, old_row_data, new_row_data, dml_type, dml_timestamp ) VALUES( OLD.id, to_jsonb(OLD), null, 'DELETE', CURRENT_TIMESTAMP ); RETURN OLD; end if; END;

Хочу в этой функции вызвать функцию amqp.publish из схемы amqp:
SELECT amqp.publish(1, », ‘queu’, ‘messange’)

Но мне выдает ошибку:

ERROR: function amqp.publish(integer, unknown, unknown, unknown) does not exist

• Вопрос задан более двух лет назад
• 183 просмотра

5 комментариев

Простой 5 комментариев

Как вызвать функцию в 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. Определение данных