0Pricing
SQL Academy · Урок

Основы функций PL/pgSQL

Пишите функции PL/pgSQL с параметрами, RETURNS TABLE, управляющими конструкциями (IF, LOOP, FOREACH) и обработкой исключений

«Основы функций PL/pgSQL» — бесплатный урок SQL Academy на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое PL/pgSQL

Встроенный процедурный язык PostgreSQL. Это SQL, расширенный переменными, управлением потоком выполнения, исключениями и возможностью динамически вызывать query. Используется для хранимых процедур и функций-триггеров.

Каркас функции

Простая функция:

CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT add(2, 3);   -- 5

Переменные

Объявляйте с помощью DECLARE, присваивайте значения с помощью := или INTO:

CREATE FUNCTION user_orders(uid BIGINT)
RETURNS INT AS $$
DECLARE
  cnt INT;
BEGIN
  SELECT COUNT(*) INTO cnt FROM orders WHERE user_id = uid;
  RETURN cnt;
END;
$$ LANGUAGE plpgsql STABLE;

Управление потоком выполнения

IF / ELSE, FOR, WHILE, CASE:

IF cnt = 0 THEN
  RAISE NOTICE 'no orders';
ELSIF cnt < 10 THEN
  RAISE NOTICE 'few';
ELSE
  RAISE NOTICE 'many';
END IF;

FOR i IN 1..10 LOOP
  RAISE NOTICE '%', i;
END LOOP;

Цикл по результатам query

Итерация в стиле курсора:

CREATE FUNCTION recalc_totals() RETURNS VOID AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT id FROM users LOOP
    UPDATE users SET
      order_count = (SELECT COUNT(*) FROM orders WHERE user_id = r.id)
    WHERE id = r.id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

RETURNS TABLE

Возврат набора результатов:

CREATE FUNCTION top_buyers(n INT)
RETURNS TABLE (user_id BIGINT, total NUMERIC) AS $$
BEGIN
  RETURN QUERY
    SELECT o.user_id, SUM(o.total)
    FROM orders o
    GROUP BY o.user_id
    ORDER BY SUM(o.total) DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM top_buyers(10);

Параметры IN / OUT / INOUT

Несколько возвращаемых значений:

CREATE FUNCTION stats(uid BIGINT,
  OUT total NUMERIC,
  OUT cnt INT) AS $$
BEGIN
  SELECT SUM(total), COUNT(*) INTO total, cnt
  FROM orders WHERE user_id = uid;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM stats(42);   -- total | cnt

Обработка исключений

BEGIN ... EXCEPTION WHEN ... THEN:

BEGIN
  INSERT INTO users (email) VALUES ($1);
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'email already exists';
  WHEN OTHERS THEN
    RAISE;
END;

RAISE для журналирования

Уровни: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION:

RAISE NOTICE 'processed % rows', cnt;
RAISE EXCEPTION 'bad state: %', state USING ERRCODE = 'check_violation';

Маркеры изменчивости

Правильно помечайте функции для планировщика:

  • IMMUTABLE — одни и те же аргументы всегда дают один и тот же результат (без ввода-вывода); допускает встраивание и использование с индексами
  • STABLE — одни и те же аргументы дают один и тот же результат в рамках query
  • VOLATILE — результат может различаться при каждом вызове (по умолчанию; правильно для NOW(), RANDOM())

SECURITY DEFINER

Выполняется с привилегиями владельца функции — используйте осторожно:

CREATE FUNCTION sensitive_op() RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$ ... $$;

-- Always set search_path explicitly to avoid hijacking:
ALTER FUNCTION sensitive_op() SET search_path = public, pg_temp;

Итоги

PL/pgSQL расширяет SQL циклами, IF, исключениями и структурированными возвратами. Используйте его для цельной многошаговой логики, которой полезно выполняться рядом с данными.

Быстрая проверка

Какой маркер изменчивости следует использовать для функции, возвращающей один и тот же результат при одинаковых аргументах, без побочных эффектов и операций ввода-вывода?

Часто задаваемые вопросы

Урок «Основы функций PL/pgSQL» бесплатный?

Да — полный текст урока «Основы функций PL/pgSQL» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Основы функций PL/pgSQL»?

Пишите функции PL/pgSQL с параметрами, RETURNS TABLE, управляющими конструкциями (IF, LOOP, FOREACH) и обработкой исключений Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Основы функций PL/pgSQL»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Устройство триггеров: BEFORE/AFTER, FOR EACH ROW
  2. Основы функций PL/pgSQL
  3. Блоки DO и анонимный код
  4. Аудит таблиц с помощью триггеров
← Назад к SQL Academy