Модуль 08 — Функции, процедуры и триггеры

Простейший вид функций — тело состоит из одного SQL-запроса. Функция может принимать параметры и возвращать скаляр, строку или набор строк.

-- Функция без параметров: возвращает всех пользователей-женщин
CREATE OR REPLACE FUNCTION fnc_users()
RETURNS SETOF users
LANGUAGE sql
AS $$
    SELECT * FROM users WHERE gender = 'female';
$$;

-- Вызов
SELECT * FROM fnc_users();

Теперь научим функцию принимать пол параметром. CREATE OR REPLACE здесь не заменит старую функцию: у новой другой список параметров, и PostgreSQL создаст вторую функцию с тем же именем — перегрузку. Тогда вызов fnc_users() подойдёт к обеим, и сервер откажется выбирать: ERROR: function fnc_users() is not unique. Поэтому старую версию сначала удаляем.

-- Старая версия без параметров мешает новой — удаляем
DROP FUNCTION IF EXISTS fnc_users();

-- Функция с параметром и DEFAULT-значением
CREATE OR REPLACE FUNCTION fnc_users(pgender varchar DEFAULT 'female')
RETURNS SETOF users
LANGUAGE sql
AS $$
    SELECT * FROM users WHERE gender = pgender;
$$;

-- Без аргумента — вернёт женщин (DEFAULT)
SELECT * FROM fnc_users();

-- С явным аргументом — вернёт мужчин
SELECT * FROM fnc_users('male');

VARIADIC позволяет передать любое количество значений одного типа — внутри функции они доступны как массив.

-- Функция возвращает минимальное из любого числа значений
CREATE OR REPLACE FUNCTION func_minimum(VARIADIC arr numeric[])
RETURNS numeric
LANGUAGE sql
AS $$
    SELECT MIN(x) FROM UNNEST(arr) AS x;
$$;

-- Вызовы с разным числом аргументов
SELECT func_minimum(10, 5, 8);           -- → 5
SELECT func_minimum(900, 600, 1200, 750);  -- → 600

PL/pgSQL позволяет писать функции с переменными, условиями, циклами и обработкой ошибок. Тело функции оборачивается в блок DECLARE … BEGIN … END.

-- Функция: числа Фибоначчи до pstop включительно
CREATE OR REPLACE FUNCTION fnc_fibonacci(pstop integer)
RETURNS integer[]
LANGUAGE plpgsql
AS $$
DECLARE
    a      integer := 0;
    b      integer := 1;
    tmp    integer;
    result integer[] := ARRAY[0];
BEGIN
    WHILE b <= pstop LOOP
        result := result || b;
        tmp := b;
        b   := a + b;
        a   := tmp;
    END LOOP;
    RETURN result;
END
$$;

SELECT fnc_fibonacci(100);
-- → {0,1,1,2,3,5,8,13,21,34,55,89}

Функция на PL/pgSQL может возвращать несколько строк через RETURN QUERY. Удобно для инкапсуляции сложной логики с условиями.

-- Возвращает сообщества, которые пользователь просматривал в дату pdate,
-- и где есть посты с score < pscore
CREATE OR REPLACE FUNCTION
    fnc_user_views_and_likes_on_date(
        puser  integer,
        pscore integer,
        pdate  date
    )
RETURNS SETOF varchar
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT DISTINCT c.name
        FROM   views v
        JOIN   communities c ON c.id = v.community_id
        JOIN   posts p       ON p.community_id = c.id
        WHERE  v.user_id   = puser
          AND  v.viewed_at = pdate
          AND  p.score     < pscore;
END
$$;

-- Сообщества, которые Dmitriy (id=4) просматривал 8 января 2026,
-- и где есть посты со score < 800
SELECT * FROM fnc_user_views_and_likes_on_date(4, 800, '2026-01-08');

Dmitriy просматривал TechTalks 8 января 2026, и в TechTalks есть пост «tutorial Python basics» со score = 600 < 800.

Триггер — процедура, которая автоматически выполняется при INSERT, UPDATE или DELETE на таблице. Классический сценарий — аудиторский лог: фиксируем каждое изменение строки.

-- 1. Таблица аудита
CREATE TABLE users_audit (
    id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- порядок событий
    created    timestamptz DEFAULT NOW(),
    type_event char(1)     NOT NULL,   -- 'I' | 'U' | 'D'
    row_id     bigint,
    name       varchar(100),
    age        integer,
    gender     varchar(10),
    city       varchar(100)
);

-- 2. Триггерная функция
CREATE OR REPLACE FUNCTION fnc_trg_users_audit()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO users_audit(type_event, row_id, name, age, gender, city)
        VALUES ('I', NEW.id, NEW.name, NEW.age, NEW.gender, NEW.city);
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO users_audit(type_event, row_id, name, age, gender, city)
        VALUES ('U', NEW.id, NEW.name, NEW.age, NEW.gender, NEW.city);
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO users_audit(type_event, row_id, name, age, gender, city)
        VALUES ('D', OLD.id, OLD.name, OLD.age, OLD.gender, OLD.city);
    END IF;
    RETURN NULL;
END
$$;

-- 3. Создание триггера
CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION fnc_trg_users_audit();

Выполним три операции над таблицей users и проверим, что аудитная таблица их зафиксировала.

-- INSERT: добавляем нового пользователя, id назначит база
INSERT INTO users(name, age, gender, city)
VALUES ('Alex', 28, 'male', 'Yekaterinburg');

-- UPDATE: меняем город
UPDATE users SET city = 'Novosibirsk' WHERE name = 'Alex';

-- DELETE: удаляем пользователя
DELETE FROM users WHERE name = 'Alex';

-- Проверяем лог
SELECT type_event, row_id, name, age, gender, city
FROM   users_audit
ORDER BY id;

-- Посмотреть список функций схемы
SELECT proname, prokind, pronargs
FROM   pg_proc
WHERE  proname LIKE 'fnc_%';

-- Удалить функцию (нужно указать сигнатуру из-за перегрузки)
DROP FUNCTION IF EXISTS fnc_users();
DROP FUNCTION IF EXISTS fnc_users(varchar);
DROP FUNCTION IF EXISTS fnc_user_views_and_likes_on_date(integer, integer, date);
DROP FUNCTION IF EXISTS func_minimum(VARIADIC numeric[]);
DROP FUNCTION IF EXISTS fnc_fibonacci(integer);

-- Удалить триггер
DROP TRIGGER IF EXISTS trg_users_audit ON users;

-- Удалить триггерную функцию
DROP FUNCTION IF EXISTS fnc_trg_users_audit();