Шпаргалка по функціям SQL

У статті розглядається:

• Створення функцій: синтаксис створення функцій в SQL та PostgreSQL, включаючи визначення аргументів та типу поверненого значення.

• Скалярні функції: як створювати функції, які повертають єдине значення, наприклад, загальну кількість товарів на складі.

• Функції з вихідними параметрами: як використовувати функції з вихідними параметрами для отримання даних з бази даних.

• Повернення наборів даних: як створювати функції, що повертають набір значень, наприклад, середні ціни за категоріями товарів.

Створення функцій

Загалом, синтаксис створення функції виглядає наступним чином:

CREATE [OR REPLACE] FUNCTION func_name([arg1, arg2...]) RETURNS data_type AS $$ 
--логіка
$$ LANGUAGE lang;

SELECT func_name();

Приклад:

CREATE OR REPLACE FUNCTION fix_customer_origin() RETURNS void AS $$
	UPDATE customers_tmp SET region='unknown' WHERE region IS NULL
$$ LANGUAGE SQL;

Функція fix_customer_origin оновлює дані у таблиці customers_tmp, встановлюючи значення region на 'unknown', якщо воно дорівнює NULL:

SELECT fix_customer_origin();

Скалярні функції

Скалярні функції в SQL - це функції, які призначені для повернення одного єдиного значення. Це може бути будь-яке значення, таке як числове, рядкове, логічне тощо. Скалярні функції використовуються для обробки даних та виконання обчислень в межах запитів SQL.

Основні характеристики скалярних функцій:

1. Повернення одного значення: Скалярна функція завжди повертає лише одне значення.

2. Використання в SELECT-запитах: Скалярні функції можуть бути використані в списку вибірки SELECT-запита для отримання додаткових даних або результатів обчислень.

3. Обробка даних: Вони дозволяють виконувати різноманітні операції над даними, такі як обчислення, форматування даних, операції порівняння тощо.

4. Використання у фільтрах і умовах: Скалярні функції можуть бути використані для фільтрації даних в WHERE-умовах або у виразах CASE.

5. Універсальність: Вони можуть бути написані таким чином, щоб вони можна було використовувати в різних контекстах і запитах SQL.

Приклад:

CREATE OR REPLACE FUNCTION get_total_number_of_goods() RETURNS bigint AS $$
SELECT SUM(units_in_stock) FROM products
$$ LANGUAGE SQL;

SELECT get_total_number_of_goods() AS goods;

Аргументи IN, OUT, INOUT, VARIADIC, DEFAULT

Аргументи в функціях в SQL можуть мати різні ролі і можуть бути вказані з різними ключовими словами для визначення їх поведінки. Основні ключові слова для аргументів функцій в SQL:

• IN - цей аргумент є вхідним для функції. Це означає, що значення передається в функцію, але функція не може змінювати його значення. Це типовий спосіб передачі параметрів в функцію.
• OUT - цей аргумент є вихідним для функції. Функція може змінювати його значення, а змінене значення буде доступне поза функцією, після її виклику.
• INOUT - цей аргумент є вхідним і вихідним для функції. Значення передається в функцію, і функція може змінювати його значення. Змінене значення також буде доступне поза функцією після її виклику.
• VARIADIC - цей аргумент дозволяє функції приймати довільну кількість аргументів. Використовується у випадках, коли кількість аргументів може варіюватися.
• DEFAULT - цей аргумент встановлює значення за замовчуванням для параметра функції. Якщо значення для цього параметра не вказано при виклику функції, використовується значення за замовчуванням.

CREATE OR REPLACE FUNCTION example_func(arg1 INT, OUT arg2 INT, INOUT arg3 INT DEFAULT 0)
RETURNS INT
LANGUAGE SQL AS $$
BEGIN
    arg2 := arg1 * 2;
    arg3 := arg3 + 1;
    RETURN arg1 + arg3;
END;
$$;

У цьому прикладі arg1 є вхідним параметром, arg2 - вихідним, а arg3 - вхідним і вихідним зі значенням за замовчуванням.

CREATE OR REPLACE FUNCTION get_product_price_by_name(prod_name varchar) RETURNS real AS $$
SELECT unit_price FROM products WHERE product_name=prod_name 
$$ LANGUAGE SQL;

SELECT get_product_price_by_name('Chang');

В цьому прикладі ми отримуємо ціну товару по назві

Повернення даних

Повернення наборів даних у функціях SQL дозволяє отримати набір рядків або стовпців як результат виконання функції. Це дозволяє зручно повертати багато рядків з таблиці або іншого джерела даних для подальшої обробки.

В SQL є кілька способів повернення наборів даних:

• RETURNS SETOF data_type: Цей варіант використовується для повернення набору значень конкретного типу data_type. Наприклад, RETURNS SETOF integer поверне набір цілих чисел.
• RETURNS SETOF table_name: Цей варіант використовується для повернення всіх стовпців з таблиці table_name. Це може бути корисно для отримання всієї інформації про певний набір даних.
• RETURNS SETOF RECORD: Цей варіант використовується, коли типи стовпців в результуючому наборі даних невідомі заздалегідь. Він дозволяє повертати набір даних з будь-якою кількістю і типом стовпців.
• RETURNS TABLE (column_name data_type, ...): Цей варіант дозволяє явно вказати стовпці, які повертаються в наборі даних, разом із їх типами. Наприклад, RETURNS TABLE (id integer, name varchar).

CREATE OR REPLACE FUNCTION get_average_prices_by_prod_categories()
RETURNS SETOF double precision AS $$
SELECT AVG(unit_price) FROM products GROUP BY category_id
$$ LANGUAGE SQL;

SELECT * FROM get_average_prices_by_prod_categories();

Функція повертає середню ціну продукту (колонка unit_price таблиці products).

CREATE OR REPLACE FUNCTION get_employee_data(employee_id INT)
RETURNS SETOF RECORD
LANGUAGE SQL AS $$
    SELECT id, first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
$$;

SELECT * FROM get_employee_data(1);

У цьому прикладі функція get_employee_data приймає employee_id і повертає набір рядків із стовпців id, first_name, last_name, і department_id таблиці employees. Тип RECORD вказує, що функція повертає рядки, але точна структура цих рядків відома тільки в момент виклику функції.