Пошук уроків, статей та іншого контенту
Навчитеся визначати функції PostgreSQL, повертати таблиці й значення та обирати потрібну мову реалізації.
Користувацька функція PostgreSQL — це іменований об’єкт бази даних, який:
приймає вхідні параметри;
виконує SQL- або процедурну логіку;
повертає одне значення, набір рядків або таблицю;
може викликатися в SELECT, WHERE, JOIN, FROM та інших SQL-конструкціях.
Функція створюється командою CREATE FUNCTION:
CREATE FUNCTION schema_name.function_name(parameter_name data_type)
RETURNS return_type
LANGUAGE language_name
AS $function$
-- тіло функції
$function$;Наприклад:
CREATE SCHEMA IF NOT EXISTS app;
CREATE OR REPLACE FUNCTION app.discounted_price(
price numeric,
discount_percent numeric
)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT price * (1 - discount_percent / 100);
$function$;Виклик функції:
SELECT app.discounted_price(1000, 15);Результат:
850.00Кваліфіковане ім’я app.discounted_price явно вказує схему функції. Це допомагає уникати конфліктів і неоднозначності пошуку об’єктів.
Параметри описуються у форматі:
ім’я параметра тип данихНаприклад:
CREATE OR REPLACE FUNCTION app.rectangle_area(
width numeric,
height numeric
)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT width * height;
$function$;Функцію можна викликати позиційно:
SELECT app.rectangle_area(10, 5);Або за іменами параметрів:
SELECT app.rectangle_area(width => 10, height => 5);Іменований виклик корисний, коли функція має багато параметрів або значення передаються не в очевидному порядку.
Для параметра можна вказати значення за замовчуванням:
CREATE OR REPLACE FUNCTION app.discounted_price(
price numeric,
discount_percent numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT price * (1 - discount_percent / 100);
$function$;Тепер можливі обидва виклики:
SELECT app.discounted_price(1000, 15);
SELECT app.discounted_price(1000);У другому випадку discount_percent дорівнює 0.
Параметри зі значеннями за замовчуванням потрібно розміщувати наприкінці списку параметрів. Інакше виклик функції може бути неоднозначним.
Тип результату вказується після RETURNS.
CREATE OR REPLACE FUNCTION app.order_total(
subtotal numeric,
shipping numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
DECLARE
total numeric;
BEGIN
IF subtotal < 0 THEN
RAISE EXCEPTION 'Сума замовлення не може бути від’ємною';
END IF;
IF shipping < 0 THEN
RAISE EXCEPTION 'Вартість доставки не може бути від’ємною';
END IF;
total := subtotal + shipping;
RETURN total;
END;
$function$;Виклик:
SELECT app.order_total(1200, 100);У функції з RETURNS numeric команда RETURN повинна повернути одне значення сумісного типу.
Якщо результат обчислюється одним SQL-виразом, доцільно використовувати LANGUAGE sql. Якщо потрібні змінні, перевірки, цикли або обробка помилок, підходить LANGUAGE plpgsql.
STRICTМодифікатор STRICT означає: якщо будь-який вхідний параметр дорівнює NULL, PostgreSQL не виконує тіло функції, а відразу повертає NULL.
CREATE OR REPLACE FUNCTION app.add_numbers(
first_number integer,
second_number integer
)
RETURNS integer
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT first_number + second_number;
$function$;SELECT app.add_numbers(2, NULL);Результат — NULL.
Без STRICT тіло функції все одно може повернути NULL у цьому прикладі, але перевірку довелося б реалізувати самостійно.
PostgreSQL використовує інформацію про змінність функції для оптимізації запитів.
IMMUTABLE — результат залежить лише від параметрів і не змінюється для однакових значень.
STABLE — результат не змінюється в межах одного SQL-запиту, але може змінюватися між запитами.
VOLATILE — результат може змінюватися під час виконання. Це значення за замовчуванням.
Приклади:
-- Чисте математичне обчислення
CREATE OR REPLACE FUNCTION app.cube(value numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT value * value * value;
$function$;-- Функція читає дані з таблиці, тому результат може змінюватися
CREATE OR REPLACE FUNCTION app.current_order_count()
RETURNS bigint
LANGUAGE sql
STABLE
AS $function$
SELECT count(*)
FROM orders
WHERE status = 'new';
$function$;Не слід оголошувати функцію як IMMUTABLE, якщо вона читає таблиці, залежить від часу, випадкових чисел або параметрів сесії.
LANGUAGE sqlSQL-функція складається з одного або кількох SQL-виразів і добре підходить, коли логіку можна описати звичайним запитом.
CREATE OR REPLACE FUNCTION app.user_order_count(
user_id bigint
)
RETURNS bigint
LANGUAGE sql
STABLE
STRICT
AS $function$
SELECT count(*)
FROM orders
WHERE orders.user_id = user_id;
$function$;У складніших запитах варто явно кваліфікувати назви стовпців і параметрів, щоб уникати конфліктів імен. Наприклад, параметр можна назвати p_user_id:
CREATE OR REPLACE FUNCTION app.user_order_count(
p_user_id bigint
)
RETURNS bigint
LANGUAGE sql
STABLE
STRICT
AS $function$
SELECT count(*)
FROM orders AS o
WHERE o.user_id = p_user_id;
$function$;LANGUAGE sql зазвичай є найкращим вибором, якщо функція лише об’єднує або параметризує SQL-запит.
LANGUAGE plpgsqlPL/pgSQL потрібна, коли функція має процедурну логіку:
локальні змінні;
IF, CASE, цикли;
кілька послідовних SQL-команд;
RETURN QUERY;
обробку винятків через EXCEPTION.
Приклад функції з перевіркою параметрів:
CREATE OR REPLACE FUNCTION app.calculate_shipping(
order_weight numeric,
express boolean DEFAULT false
)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
DECLARE
base_price numeric;
BEGIN
IF order_weight <= 0 THEN
RAISE EXCEPTION 'Вага замовлення повинна бути більшою за нуль';
END IF;
base_price := 50 + order_weight * 10;
IF express THEN
RETURN base_price * 2;
END IF;
RETURN base_price;
END;
$function$;PostgreSQL підтримує додаткові процедурні мови, зокрема мови, які можуть бути встановлені як розширення.
Для прикладної логіки зазвичай достатньо:
sql, якщо потрібен один SQL-запит;
plpgsql, якщо потрібна процедурна логіка.
Мови на кшталт C використовують для спеціалізованих розширень або дуже низькорівневих оптимізацій. Вони потребують окремої компіляції та мають значно вищі вимоги до безпеки й супроводу.
Виконання функцій на непривілейованих мовах може бути обмежене правами доступу. Не варто вибирати складнішу мову, якщо задачу можна безпечно й зрозуміло розв’язати через sql або plpgsql.
Функція може повертати набір рядків. Найзручніший синтаксис для цього — RETURNS TABLE.
CREATE OR REPLACE FUNCTION app.generate_squares(
start_number integer,
end_number integer
)
RETURNS TABLE (
number integer,
square bigint
)
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
BEGIN
RETURN QUERY
SELECT
generated_number,
generated_number::bigint * generated_number
FROM generate_series(start_number, end_number) AS generated_number;
END;
$function$;Виклик функції в секції FROM:
SELECT *
FROM app.generate_squares(2, 5);Результат:
number | square
--------+--------
2 | 4
3 | 9
4 | 16
5 | 25RETURNS TABLE одночасно:
оголошує структуру результату;
створює вихідні параметри number і square;
описує функцію, що може повернути кілька рядків.
У PL/pgSQL команда RETURN QUERY додає до результату всі рядки, повернуті вказаним запитом.
RETURN NEXT і RETURN QUERYRETURN QUERY використовується для додавання результату цілого запиту:
CREATE OR REPLACE FUNCTION app.even_numbers(
start_number integer,
end_number integer
)
RETURNS SETOF integer
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
BEGIN
RETURN QUERY
SELECT value
FROM generate_series(start_number, end_number) AS value
WHERE value % 2 = 0;
END;
$function$;Виклик:
SELECT *
FROM app.even_numbers(1, 10);Результат:
even_numbers
--------------
2
4
6
8
10RETURN NEXT додає один рядок за один виклик. Він корисний, коли рядки формуються поступово:
CREATE OR REPLACE FUNCTION app.countdown(
start_number integer
)
RETURNS SETOF integer
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
DECLARE
current_number integer;
BEGIN
IF start_number < 1 THEN
RETURN;
END IF;
FOR current_number IN REVERSE start_number..1 LOOP
RETURN NEXT current_number;
END LOOP;
END;
$function$;SELECT *
FROM app.countdown(5);Для результату типу SETOF integer кожен виклик RETURN NEXT додає одне ціле число до набору результатів.
RETURNS TABLE і SETOFЦі два варіанти мають різне призначення.
RETURNS SETOFВикористовується, коли функція повертає багато значень одного вже відомого типу:
CREATE OR REPLACE FUNCTION app.positive_numbers(
first_number integer,
last_number integer
)
RETURNS SETOF integer
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT value
FROM generate_series(first_number, last_number) AS value
WHERE value > 0;
$function$;RETURNS TABLEВикористовується, коли потрібно повернути кілька стовпців:
CREATE OR REPLACE FUNCTION app.number_details(
first_number integer,
last_number integer
)
RETURNS TABLE (
value integer,
doubled integer,
is_even boolean
)
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT
value,
value * 2,
value % 2 = 0
FROM generate_series(first_number, last_number) AS value;
$function$;SELECT *
FROM app.number_details(1, 3);Результат:
value | doubled | is_even
-------+---------+---------
1 | 2 | false
2 | 4 | true
3 | 6 | falseТабличну функцію можна використовувати в секції FROM:
SELECT number, square
FROM app.generate_squares(1, 4)
WHERE square > 4;Її також можна об’єднувати з іншими таблицями:
SELECT
u.id,
u.name,
details.value,
details.doubled
FROM users AS u
CROSS JOIN LATERAL app.number_details(1, u.id) AS details;LATERAL дозволяє функції використовувати значення з попередніх джерел у секції FROM. Для функцій PostgreSQL часто допускає такий виклик без явного ключового слова LATERAL, але явне зазначення робить залежність зрозумілішою.
RETURNS TABLE є скороченим записом для функції з вихідними параметрами.
Наприклад:
CREATE OR REPLACE FUNCTION app.coordinates(
IN x_value integer,
IN y_value integer,
OUT x integer,
OUT y integer,
OUT distance_from_origin numeric
)
LANGUAGE plpgsql
IMMUTABLE
AS $function$
BEGIN
x := x_value;
y := y_value;
distance_from_origin := sqrt(x_value::numeric ^ 2 + y_value::numeric ^ 2);
END;
$function$;Виклик:
SELECT *
FROM app.coordinates(3, 4);Оскільки x, y і distance_from_origin є вихідними параметрами, їхні значення повертаються як один рядок.
У такій функції не потрібно писати RETURN вираз. Завершення блоку повертає значення вихідних параметрів. За потреби можна використати простий RETURN;.
CREATE OR REPLACE FUNCTION дозволяє змінити тіло функції, її мову або деякі атрибути:
CREATE OR REPLACE FUNCTION app.cube(value numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT value * value * value;
$function$;Ідентичність функції визначається її ім’ям і типами вхідних параметрів:
app.cube(numeric)Тип результату не входить до ідентичності функції. Тому CREATE OR REPLACE FUNCTION не можна використовувати для зміни типу, що повертається. Для цього функцію потрібно видалити та створити заново:
DROP FUNCTION app.cube(numeric);
CREATE FUNCTION app.cube(value integer)
RETURNS bigint
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT value::bigint * value * value;
$function$;Видалення функції може бути неможливим без CASCADE, якщо від неї залежать інші об’єкти. Використовуйте CASCADE обережно, оскільки він може видалити залежні об’єкти.
Наведений скрипт створює схему та три функції різних типів:
CREATE SCHEMA IF NOT EXISTS app;
-- SQL-функція, що повертає одне значення
CREATE OR REPLACE FUNCTION app.apply_discount(
p_price numeric,
p_discount_percent numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT p_price * (1 - p_discount_percent / 100);
$function$;
-- PL/pgSQL-функція з перевіркою параметрів
CREATE OR REPLACE FUNCTION app.final_price(
p_price numeric,
p_discount_percent numeric DEFAULT 0,
p_shipping numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
DECLARE
result numeric;
BEGIN
IF p_price < 0 THEN
RAISE EXCEPTION 'Ціна не може бути від’ємною';
END IF;
IF p_discount_percent NOT BETWEEN 0 AND 100 THEN
RAISE EXCEPTION 'Знижка повинна бути в діапазоні від 0 до 100';
END IF;
IF p_shipping < 0 THEN
RAISE EXCEPTION 'Вартість доставки не може бути від’ємною';
END IF;
result :=
app.apply_discount(p_price, p_discount_percent)
+ p_shipping;
RETURN result;
END;
$function$;
-- Таблична функція
CREATE OR REPLACE FUNCTION app.price_steps(
p_start numeric,
p_end numeric,
p_step numeric DEFAULT 10
)
RETURNS TABLE (
price numeric,
discounted_price numeric
)
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $function$
BEGIN
IF p_step <= 0 THEN
RAISE EXCEPTION 'Крок повинен бути більшим за нуль';
END IF;
RETURN QUERY
SELECT
value,
app.apply_discount(value, 10)
FROM generate_series(p_start, p_end, p_step) AS value;
END;
$function$;
-- Виклик функції зі скалярним результатом
SELECT app.final_price(1000, 15, 80);
-- Виклик функції, що повертає таблицю
SELECT *
FROM app.price_steps(100, 130, 10);Якщо функція читає таблицю або використовує поточний час, не оголошуйте її як IMMUTABLE.
-- Потенційно неправильне оголошення
CREATE FUNCTION app.get_user_count()
RETURNS bigint
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT count(*) FROM users;
$function$;Кількість користувачів може змінюватися, тому така функція не є незмінною. Для неї доречніше STABLE або VOLATILE, залежно від логіки.
RETURNФункція, що повертає скалярне значення, повинна завершитися командою RETURN зі значенням:
CREATE FUNCTION app.bad_function(value integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
BEGIN
value := value + 1;
-- Помилка: функція не повертає значення
END;
$function$;Кількість і типи стовпців у RETURN QUERY повинні відповідати RETURNS TABLE.
Якщо функція оголошена так:
RETURNS TABLE (id integer, title text)то запит у RETURN QUERY повинен повернути саме два сумісні стовпці.
Ім’я параметра може збігатися з іменем стовпця. Щоб уникати неоднозначності:
використовуйте префікс p_ для параметрів;
використовуйте псевдоніми таблиць;
явно кваліфікуйте стовпці.
WHERE o.user_id = p_user_idЦей запис зрозуміліший за використання однакового імені user_id для параметра і стовпця.
RETURNS TABLEТаблична функція викликається як джерело рядків:
SELECT *
FROM app.number_details(1, 5);Виклик лише в списку SELECT має іншу семантику й може ускладнити читання запиту. Для набору рядків краще використовувати секцію FROM.
CREATE OR REPLACEТип, що повертається, не можна змінити за допомогою CREATE OR REPLACE FUNCTION. Спочатку потрібно видалити стару функцію з правильною сигнатурою, а потім створити нову.
Користувацька функція створюється через CREATE FUNCTION.
RETURNS визначає тип результату функції.
RETURNS numeric, RETURNS integer та інші скалярні типи повертають одне значення.
RETURNS SETOF повертає набір значень одного типу.
RETURNS TABLE повертає набір рядків із визначеними стовпцями.
RETURN QUERY додає до результату всі рядки SQL-запиту.
RETURN NEXT додає один рядок за один виклик.
LANGUAGE sql підходить для функцій, що складаються з SQL-запитів.
LANGUAGE plpgsql потрібна для змінних, умов, циклів і обробки винятків.
IMMUTABLE, STABLE і VOLATILE повинні відповідати фактичній поведінці функції.
STRICT автоматично повертає NULL, якщо будь-який вхідний параметр є NULL.
Сигнатура функції визначається її ім’ям і типами вхідних параметрів, а не типом результату.