Приложение А1 — PL/pgSQL: функции, процедуры, триггеры

Функция, процедура и триггер выполняются внутри сервера, в транзакции того запроса, который их вызвал. Отсюда и сила, и цена: правило работает для любого клиента, но спрятано от того, кто читает код приложения.

Тело функции на LANGUAGE sql — один или несколько запросов; возвращается результат последнего. Параметры доступны по имени, у параметра может быть значение по умолчанию.

CREATE OR REPLACE FUNCTION fnc_users(pgender varchar DEFAULT 'female')
RETURNS SETOF users
LANGUAGE sql
STABLE
AS $$
    SELECT * FROM users WHERE gender = pgender ORDER BY id;
$$;

SELECT * FROM fnc_users();                              -- 6 строк: значение по умолчанию
SELECT * FROM fnc_users('male');                        -- 4 строки
SELECT name FROM fnc_users() WHERE city = 'Moscow';   -- Anna, Maria

SETOF users — «произвольное число строк типа users». Такую функцию вызывают во FROM, как таблицу: результат можно отфильтровать, соединить, сгруппировать.

Перегрузка и ловушка значения по умолчанию

Функция в PostgreSQL определяется не именем, а именем и типами параметров. CREATE OR REPLACE с другим списком параметров не заменяет старую функцию, а создаёт вторую рядом.

CREATE FUNCTION fnc_users() RETURNS SETOF users
LANGUAGE sql AS $$ SELECT * FROM users $$;       -- создаётся без ошибок

SELECT * FROM fnc_users();
-- ERROR:  function fnc_users() is not unique
-- HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

DROP FUNCTION fnc_users;
-- ERROR:  function name "fnc_users" is not unique
-- HINT:  Specify the argument list to select the function unambiguously.

DROP FUNCTION fnc_users();     -- удаляется функция без параметров

Категория изменчивости

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

CREATE OR REPLACE FUNCTION func_minimum(VARIADIC arr numeric[])
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT min(x) FROM unnest(arr) AS x;
$$;

SELECT func_minimum(10, 5, 8);                  -- 5
SELECT func_minimum(900, 600, 1200, 750);       -- 600
SELECT func_minimum(VARIADIC ARRAY[3, 1, 2]);    -- 1: готовый массив передают с VARIADIC

PL/pgSQL нужен, когда в функции есть решения: условия, проверки, циклы. Тело — блок DECLARE … BEGIN … END. Функция ниже оценивает вовлечённость сообщества по среднему score постов.

CREATE OR REPLACE FUNCTION fnc_community_level(p_community_id integer)
RETURNS text
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_name  varchar(100);
    v_avg   numeric;
BEGIN
    SELECT c.name, avg(p.score)
    INTO   v_name, v_avg
    FROM   communities c
    LEFT JOIN posts p ON p.community_id = c.id
    WHERE  c.id = p_community_id
    GROUP BY c.name;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'сообщество % не найдено', p_community_id
            USING ERRCODE = 'no_data_found';
    END IF;

    IF v_avg IS NULL THEN
        RETURN v_name || ': постов нет';
    ELSIF v_avg >= 1000 THEN
        RETURN v_name || ': высокая';
    ELSIF v_avg >= 850 THEN
        RETURN v_name || ': средняя';
    ELSE
        RETURN v_name || ': низкая';
    END IF;
END;
$$;

SELECT id, fnc_community_level(id) FROM communities ORDER BY id;

SELECT fnc_community_level(99);
-- ERROR:  сообщество 99 не найдено

RETURNS TABLE объявляет столбцы результата, RETURN QUERY добавляет к нему строки запроса. Функция ниже возвращает посты сообществ, которые пользователь просматривал в указанный день, со score ниже порога. Перед запросом — проверка, ради которой здесь нужен PL/pgSQL: функция из одного запроса короче на LANGUAGE sql.

CREATE OR REPLACE FUNCTION fnc_user_views_on_date(
    p_user_id   integer,
    p_max_score integer,
    p_date      date
)
RETURNS TABLE (community_name text, post_title text, post_score integer)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM users WHERE id = p_user_id) THEN
        RAISE EXCEPTION 'пользователь % не найден', p_user_id
            USING ERRCODE = 'no_data_found';
    END IF;

    RETURN QUERY
        SELECT c.name::text, p.title::text, p.score
        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   = p_user_id
          AND  v.viewed_at = p_date
          AND  p.score     < p_max_score
        ORDER BY p.score;
END;
$$;

SELECT * FROM fnc_user_views_on_date(4, 800, '2026-01-08');

Dmitriy (id 4) открывал ленту TechTalks 8 января; из двух постов сообщества ниже порога только один.

Функция выполняется внутри транзакции вызвавшего её запроса и завершить эту транзакцию не может. Процедура вызывается командой CALL и может выполнять COMMIT и ROLLBACK прямо в теле. Это нужно пакетным операциям, которые нельзя держать одной транзакцией: долгая транзакция держит блокировки строк и не даёт очистке убирать старые версии (раздел 6).

CREATE OR REPLACE PROCEDURE prc_recalc_ratings()
LANGUAGE plpgsql
AS $$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT id FROM communities ORDER BY id LOOP
        UPDATE communities
        SET    rating = (SELECT round(avg(score) / 250, 1)
                         FROM   posts WHERE community_id = r.id)
        WHERE  id = r.id;
        COMMIT;
    END LOOP;
END;
$$;

CALL prc_recalc_ratings();
SELECT id, name, rating FROM communities ORDER BY id;

BEGIN;
CALL prc_recalc_ratings();
-- ERROR:  invalid transaction termination
ROLLBACK;

Триггер связывает функцию с событием на таблице: вставкой, изменением, удалением. Функция возвращает тип trigger и создаётся отдельно от самого триггера — одну функцию можно навесить на несколько таблиц.

Правило «возраст пользователя не уменьшается» ограничением CHECK не выразить: CHECK видит только новую версию строки, а правило сравнивает старую и новую. Это работа триггера BEFORE. Заодно он убирает пробелы по краям названия города.

CREATE OR REPLACE FUNCTION trg_users_before_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.age < OLD.age THEN
        RAISE EXCEPTION 'возраст пользователя % нельзя уменьшить: % → %',
            OLD.id, OLD.age, NEW.age;
    END IF;
    NEW.city := trim(NEW.city);
    RETURN NEW;
END;
$$;

CREATE TRIGGER users_before_update
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION trg_users_before_update();

UPDATE users SET age = 20 WHERE id = 1;
-- ERROR:  возраст пользователя 1 нельзя уменьшить: 21 → 20

UPDATE users SET city = '  Moscow ' WHERE id = 1;
SELECT city, length(city) FROM users WHERE id = 1;   -- Moscow, 6

Журнал хранит старую и новую версию строки целиком в jsonb: добавление столбца в users не потребует менять ни таблицу журнала, ни функцию.

CREATE TABLE users_audit (
    id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    changed_at timestamptz NOT NULL DEFAULT now(),
    changed_by text        NOT NULL DEFAULT current_user,
    operation  char(1)     NOT NULL CHECK (operation IN ('I', 'U', 'D')),
    user_id    integer     NOT NULL,
    old_row    jsonb,
    new_row    jsonb
);

CREATE OR REPLACE FUNCTION trg_users_audit()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO users_audit (operation, user_id, old_row, new_row)
    VALUES (left(TG_OP, 1),
            CASE WHEN TG_OP = 'INSERT' THEN NEW.id ELSE OLD.id END,
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;
END;
$$;

CREATE TRIGGER users_audit_ins_del
AFTER INSERT OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION trg_users_audit();

-- изменение без фактической разницы в журнал не пишется
CREATE TRIGGER users_audit_upd
AFTER UPDATE ON users
FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION trg_users_audit();

Проверка: вставка, изменение, изменение «на то же значение», удаление.

INSERT INTO users (name, age, gender, city)
VALUES ('Alex', 28, 'male', 'Yekaterinburg')
RETURNING id;                                        -- 11

UPDATE users SET city = 'Novosibirsk' WHERE id = 11;
UPDATE users SET city = city;                        -- UPDATE 11, в журнал ничего
DELETE FROM users WHERE id = 11;

SELECT id, operation, user_id,
       old_row ->> 'city' AS old_city,
       new_row ->> 'city' AS new_city
FROM   users_audit
ORDER BY id;

SECURITY DEFINER

Функция по умолчанию выполняется с правами того, кто её вызвал. С SECURITY DEFINER — с правами владельца. Так роли дают одно ограниченное действие без прав на таблицу: роль app_user из раздела 6 может удалить лайки пользователя, но не может выполнить произвольный DELETE.

CREATE OR REPLACE FUNCTION fnc_delete_user_likes(p_user_id integer)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
    v_deleted integer;
BEGIN
    DELETE FROM public.likes WHERE user_id = p_user_id;
    GET DIAGNOSTICS v_deleted = ROW_COUNT;
    RETURN v_deleted;
END;
$$;

-- право на выполнение по умолчанию есть у всех: сначала отнять
REVOKE EXECUTE ON FUNCTION fnc_delete_user_likes(integer) FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION fnc_delete_user_likes(integer) TO app_user;

Сопровождение

-- функции и процедуры своей схемы; в psql то же — \df
SELECT proname, prokind            -- f — функция, p — процедура
FROM   pg_proc
WHERE  pronamespace = 'public'::regnamespace
ORDER BY proname;

-- триггеры таблицы; в psql они видны в выводе \d users
SELECT tgname FROM pg_trigger
WHERE  tgrelid = 'users'::regclass AND NOT tgisinternal;

-- тип результата через CREATE OR REPLACE не меняется
CREATE OR REPLACE FUNCTION fnc_community_level(p_community_id integer)
RETURNS varchar LANGUAGE sql AS $$ SELECT 'x'::varchar $$;
-- ERROR:  cannot change return type of existing function

-- функцию, на которой висит триггер, не удалить
DROP FUNCTION trg_users_audit();
-- ERROR:  cannot drop function trg_users_audit() because other objects depend on it

DROP TRIGGER users_audit_upd ON users;
DROP TRIGGER users_audit_ins_del ON users;
DROP FUNCTION trg_users_audit();