Перейти к основному содержимому

Практика: Хранимые процедуры и функции (PL/pgSQL)


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Простые функции и RETURNS TABLE​

Задание 1: Простые функции + RETURNS TABLE / SETOF

⏱️ Примерное время: 15-30 минут

Напиши функции с разными способами возврата данных.


Требования:

  • используй минимум 3 разных способа возврата: RETURNS тип, RETURNS TABLE, SETOF, OUT-параметры
  • добавь атрибуты: IMMUTABLE / STABLE / STRICT где уместно
  • сделай 1–2 функции с обработкой NULL / ошибок
  • протестируй вызовы в SELECT

Пример:

CREATE OR REPLACE FUNCTION get_top_employees(p_limit INT DEFAULT 5)
RETURNS TABLE(emp_id INT, name TEXT, salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT e.emp_id, e.full_name, e.salary
        FROM employees e
        ORDER BY e.salary DESC
        LIMIT p_limit;
END;
$$;

SELECT * FROM get_top_employees(3);

Задание 2 — Управляющие конструкции и циклы​

Задание 2: IF/CASE, LOOP/WHILE/FOR, CONTINUE/EXIT

⏱️ Примерное время: 25-45 минут

Напиши функции с логикой ветвления и циклами.


Требования:

  • используй минимум 2–3 конструкции: IF/ELSIF/ELSE, CASE, LOOP, WHILE, FOR, CONTINUE/EXIT
  • добавь хотя бы одну рекурсивную функцию (или имитацию через цикл)
  • обработай краевые случаи (0, NULL, отрицательные значения)
  • протестируй на разных входных данных

Пример:

CREATE OR REPLACE FUNCTION classify_order(p_amount NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $$
BEGIN
    RETURN CASE
        WHEN p_amount < 100 THEN 'Small'
        WHEN p_amount < 1000 THEN 'Medium'
        WHEN p_amount < 5000 THEN 'Large'
        ELSE 'VIP'
    END;
END;
$$;

Задание 3 — Исключения, динамический SQL, курсоры​

Задание 3: Исключения, динамический SQL, курсоры

⏱️ Примерное время: 35-60 минут

Работа с ошибками, EXECUTE и построчной обработкой.


Требования:

  • используй EXCEPTION WHEN ... THEN
  • хотя бы одна функция с динамическим SQL (format + EXECUTE ... USING)
  • хотя бы одна функция с явным курсором (OPEN / FETCH / CLOSE)
  • обработай минимум 2–3 вида ошибок (NO_DATA_FOUND, division_by_zero, unique_violation и т.д.)

Пример:

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero attempted';
        RETURN NULL;
END;
$$;

Задание 4 — Процедуры + управление транзакциями​

Задание 4: Процедуры с COMMIT/ROLLBACK внутри

⏱️ Примерное время: 40-70 минут

Реализуй процедуры с атомарными операциями.


Требования:

  • используй CALL (не SELECT!)
  • управляй транзакциями внутри процедуры (COMMIT / ROLLBACK)
  • добавь батчевую обработку + логирование
  • обработай ошибки (EXCEPTION WHEN OTHERS THEN ROLLBACK;)
  • протестируй на 100–1000 строках

Пример:

CREATE OR REPLACE PROCEDURE bulk_raise_salary(p_dept_id INT, p_pct NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INT := 0;
BEGIN
    FOR rec IN SELECT emp_id FROM employees WHERE dept_id = p_dept_id LOOP
        UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE emp_id = rec.emp_id;
        v_count := v_count + 1;
        IF v_count % 100 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
END;
$$;

CALL bulk_raise_salary(1, 10);

Задание 5 — Итоговый проект: система бонусов / заказов / логов​

Задание 5: Полноценная система на PL/pgSQL (итоговое)

⏱️ Примерное время: 90-180 минут

Создай мини-систему с бизнес-логикой в базе.


Требования:

  • минимум 4–6 функций + 2–3 процедуры
  • комбинация техник: RETURNS TABLE, курсоры, динамический SQL, исключения, батчи, COMMIT внутри процедур
  • реализуй 1–2 триггера, вызывающих функции/процедуры
  • добавь логирование операций (таблица logs или RAISE NOTICE)
  • протестируй на 500–2000 строках (INSERT + вызовы)
  • напиши документацию: что делает каждая функция/процедура

Это задание — полноценный мини-проект для портфолио или собеседования на позицию Backend / DBA.

подсказка

PL/pgSQL — это когда бизнес-логика живёт в базе и не боится сетевых задержек.
Главные правила успеха:

  1. Короткие транзакции и явные COMMIT/ROLLBACK в процедурах
  2. Обработка исключений — спасает от сюрпризов
  3. Динамический SQL — только с format('%I') и USING
  4. Курсоры и RETURN NEXT — для больших наборов данных
  5. STRICT / IMMUTABLE / SECURITY DEFINER — для безопасности и производительности