Синтаксис, переменные, управляющие конструкции, блоки кода
PL/pgSQL — процедурный язык PostgreSQL для написания хранимых процедур, функций и триггеров. В этой теме вы изучите синтаксис, переменные, управляющие конструкции и блоки кода.
PL/pgSQL (Procedural Language/PostgreSQL) — это процедурное расширение SQL, позволяющее писать сложный код с переменной логикой, циклами, обработкой исключений.
Отличия от чистого SQL:
[<<label>>] -- опциональная метка
[DECLARE
declarations;]
BEGIN
statements;
[EXCEPTION
exception_handler;]
END [label];CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
DECLARE
result INTEGER;
BEGIN
result := a + b;
RETURN result;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT add_numbers(5, 3); -- 8CREATE OR REPLACE FUNCTION demo_variables()
RETURNS VOID AS $$
DECLARE
-- Базовые типы
counter INTEGER := 0; -- с начальным значением
name TEXT DEFAULT 'Unknown'; -- с DEFAULT
price NUMERIC(10, 2);
-- Копирование типа из таблицы
user_email users.email%TYPE;
-- Копирование всей строки
user_record users%ROWTYPE;
-- Константа
pi CONSTANT NUMERIC := 3.14159;
-- NOT NULL
required_value TEXT NOT NULL := 'must have value';
BEGIN
counter := counter + 1;
name := 'New Value';
-- pi := 3.14; -- ОШИБКА: нельзя изменить константу
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS VOID AS $$
DECLARE
-- Тип переменной соответствует типу столбца
found_email users.email%TYPE;
found_name users.name%TYPE;
-- Вся строка таблицы
user_row users%ROWTYPE;
BEGIN
-- Присваивание отдельных значений
SELECT email INTO found_email
FROM users WHERE id = user_id;
-- Присваивание всей строки
SELECT * INTO user_row
FROM users WHERE id = user_id;
-- Доступ к полям строки
RAISE NOTICE 'User: %, Email: %', user_row.name, user_row.email;
END;
$$ LANGUAGE plpgsql;Преимущество: Если тип столбца изменится, тип переменной обновится автоматически.
CREATE OR REPLACE FUNCTION demo_assignment()
RETURNS VOID AS $$
DECLARE
value INTEGER;
name TEXT;
count_val INTEGER;
BEGIN
-- Оператор присваивания
value := 10;
-- SELECT INTO для получения значения
SELECT name INTO name FROM users WHERE id = 1;
-- SELECT INTO для нескольких значений
SELECT name, email INTO name, value FROM users WHERE id = 1;
-- Присваивание результата выражения
count_val := (SELECT COUNT(*) FROM users);
-- Выполнение запроса без возврата значения
PERFORM pg_sleep(1); -- как SELECT, но без результата
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_discount(age INTEGER)
RETURNS NUMERIC AS $$
BEGIN
IF age < 18 THEN
RETURN 0.10; -- 10% скидка для несовершеннолетних
ELSIF age >= 65 THEN
RETURN 0.20; -- 20% для пенсионеров
ELSIF age BETWEEN 25 AND 35 THEN
RETURN 0.05; -- 5% для молодой аудитории
ELSE
RETURN 0; -- без скидки
END IF;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_grade(score NUMERIC)
RETURNS TEXT AS $$
BEGIN
CASE
WHEN score >= 90 THEN RETURN 'A';
WHEN score >= 80 THEN RETURN 'B';
WHEN score >= 70 THEN RETURN 'C';
WHEN score >= 60 THEN RETURN 'D';
ELSE RETURN 'F';
END CASE;
END;
$$ LANGUAGE plpgsql;
-- Простой CASE (сравнение с одним значением)
CREATE OR REPLACE FUNCTION get_status_name(status_code INTEGER)
RETURNS TEXT AS $$
BEGIN
CASE status_code
WHEN 1 THEN RETURN 'Active';
WHEN 2 THEN RETURN 'Pending';
WHEN 3 THEN RETURN 'Closed';
ELSE RETURN 'Unknown';
END CASE;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION demo_loop()
RETURNS VOID AS $$
DECLARE
counter INTEGER := 1;
BEGIN
LOOP
EXIT WHEN counter > 5;
RAISE NOTICE 'Counter: %', counter;
counter := counter + 1;
END LOOP;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION demo_while()
RETURNS VOID AS $$
DECLARE
counter INTEGER := 1;
BEGIN
WHILE counter <= 5 LOOP
RAISE NOTICE 'Counter: %', counter;
counter := counter + 1;
END LOOP;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION demo_for_loop()
RETURNS VOID AS $$
DECLARE
i INTEGER;
BEGIN
-- От 1 до 5
FOR i IN 1..5 LOOP
RAISE NOTICE 'i: %', i;
END LOOP;
-- От 5 до 1 (REVERSE)
FOR i IN REVERSE 5..1 LOOP
RAISE NOTICE 'Reverse i: %', i;
END LOOP;
-- С шагом (BY)
FOR i IN 1..10 BY 2 LOOP
RAISE NOTICE 'Step i: %', i; -- 1, 3, 5, 7, 9
END LOOP;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION process_users()
RETURNS VOID AS $$
DECLARE
user_record users%ROWTYPE;
BEGIN
FOR user_record IN SELECT * FROM users WHERE is_active = true LOOP
RAISE NOTICE 'Processing user: % (%)', user_record.name, user_record.email;
-- Обработка каждого пользователя
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- С использованием RECORD (для динамических структур)
CREATE OR REPLACE FUNCTION process_dynamic()
RETURNS VOID AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT id, name, email FROM users LOOP
RAISE NOTICE 'User: % - %', rec.name, rec.email;
END LOOP;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION demo_debug()
RETURNS VOID AS $$
BEGIN
-- Разные уровни сообщений
RAISE DEBUG 'Отладочная информация'; -- только при отладке
RAISE INFO 'Полезная информация'; -- показывается всегда
RAISE NOTICE 'Внимание'; -- важно для пользователя
RAISE WARNING 'Предупреждение'; -- потенциальная проблема
RAISE EXCEPTION 'Критическая ошибка'; -- прерывает выполнение
-- Форматированный вывод
RAISE NOTICE 'User: %, Email: %', 'John', 'john@example.com';
-- Именованные параметры
RAISE NOTICE 'User: %(name)s, Email: %(email)s',
name := 'John',
email := 'john@example.com';
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Деление на ноль!';
RETURN NULL;
WHEN OTHERS THEN
RAISE NOTICE 'Произошла ошибка: %', SQLERRM;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Пример с транзакцией
CREATE OR REPLACE FUNCTION transfer_money(
from_account INTEGER,
to_account INTEGER,
amount NUMERIC
)
RETURNS BOOLEAN AS $$
BEGIN
-- Списание
UPDATE accounts SET balance = balance - amount
WHERE id = from_account;
-- Проверка, что хватило средств
IF NOT FOUND THEN
RAISE EXCEPTION 'Счёт отправитель не найден';
END IF;
-- Зачисление
UPDATE accounts SET balance = balance + amount
WHERE id = to_account;
IF NOT FOUND THEN
RAISE EXCEPTION 'Счёт получатель не найден';
END IF;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
-- Транзакция автоматически откатывается
RAISE NOTICE 'Транзакция отменена: %', SQLERRM;
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;| Переменная | Описание |
|---|---|
SQLSTATE | Код ошибки (например, '23505' — нарушение уникальности) |
SQLERRM | Текст сообщения об ошибке |
PG_EXCEPTION_DETAIL | Детали исключения |
PG_EXCEPTION_HINT | Подсказка к ошибке |
CREATE OR REPLACE FUNCTION demo_exception_info()
RETURNS VOID AS $$
BEGIN
INSERT INTO users (id, email) VALUES (1, 'test@test.com');
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'SQLSTATE: %', SQLSTATE;
RAISE NOTICE 'SQLERRM: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_table_count(table_name TEXT)
RETURNS INTEGER AS $$
DECLARE
count_val INTEGER;
BEGIN
EXECUTE format('SELECT COUNT(*) FROM %I', table_name)
INTO count_val;
RETURN count_val;
END;
$$ LANGUAGE plpgsql;
-- Безопасное использование (защита от SQL-инъекций)
CREATE OR REPLACE FUNCTION safe_query(table_name TEXT, column_name TEXT, value TEXT)
RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY EXECUTE format(
'SELECT * FROM %I WHERE %I = $1',
table_name, column_name
) USING value;
END;
$$ LANGUAGE plpgsql;-- %I - идентификатор (экранируется)
SELECT format('SELECT * FROM %I', 'users');
-- SELECT * FROM "users"
-- %L - литерал (экранируется)
SELECT format('SELECT * FROM users WHERE name = %L', 'O''Brien');
-- SELECT * FROM users WHERE name = 'O''Brien'
-- %s - простая подстановка
SELECT format('Hello, %s', 'World');
-- Hello, WorldCREATE OR REPLACE FUNCTION demo_scope()
RETURNS VOID AS $$
<<outer>>
DECLARE
value INTEGER := 1;
BEGIN
DECLARE
value INTEGER := 2;
BEGIN
RAISE NOTICE 'Inner value: %', value; -- 2
RAISE NOTICE 'Outer value: %', outer.value; -- 1
END;
RAISE NOTICE 'Outer value after: %', value; -- 1
END;
$$ LANGUAGE plpgsql;-- Функция для создания отчёта
CREATE OR REPLACE FUNCTION generate_user_report(
min_age INTEGER DEFAULT 0,
max_age INTEGER DEFAULT 150
)
RETURNS TABLE (
age_group TEXT,
user_count BIGINT,
avg_score NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT
CASE
WHEN u.age < 18 THEN 'Under 18'
WHEN u.age BETWEEN 18 AND 30 THEN '18-30'
WHEN u.age BETWEEN 31 AND 50 THEN '31-50'
ELSE '50+'
END AS age_group,
COUNT(*) AS user_count,
AVG(u.score) AS avg_score
FROM users u
WHERE u.age BETWEEN min_age AND max_age
GROUP BY
CASE
WHEN u.age < 18 THEN 'Under 18'
WHEN u.age BETWEEN 18 AND 30 THEN '18-30'
WHEN u.age BETWEEN 31 AND 50 THEN '31-50'
ELSE '50+'
END
ORDER BY age_group;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM generate_user_report(18, 65);Теперь вы знаете основы PL/pgSQL. В следующей теме вы изучите функции в PostgreSQL — создание, параметры, возвращаемые значения и перегрузку функций.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.