Работа с курсорами, выполнение динамических запросов
Курсоры позволяют обрабатывать результаты запроса построчно, а динамический SQL — выполнять запросы, сформированные во время выполнения. В этой теме вы изучите оба инструмента и научитесь их применять.
Курсор — это указатель на результат запроса, позволяющий итерироваться по строкам.
| Сценарий | Решение |
|---|---|
| Обработка каждой строки с разной логикой | Курсор |
| Массовое обновление однотипных данных | Обычный UPDATE |
| Построчная отправка уведомлений | Курсор |
| Агрегация данных | Обычный SELECT с GROUP BY |
Важно: Курсоры медленнее множественных операций. Используйте их только когда нужна построчная обработка с индивидуальной логикой.
CREATE OR REPLACE FUNCTION process_users_cursor()
RETURNS VOID AS $$
DECLARE
user_cursor CURSOR FOR SELECT * FROM users WHERE is_active = true;
user_record users%ROWTYPE;
BEGIN
OPEN user_cursor;
LOOP
FETCH user_cursor INTO user_record;
EXIT WHEN NOT FOUND;
-- Обработка каждой строки
RAISE NOTICE 'Processing user: % (%)', user_record.name, user_record.email;
END LOOP;
CLOSE user_cursor;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION process_users_simple()
RETURNS VOID AS $$
DECLARE
user_record users%ROWTYPE;
BEGIN
-- FOR автоматически открывает и закрывает курсор
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;CREATE OR REPLACE FUNCTION process_users_by_age(min_age INTEGER, max_age INTEGER)
RETURNS VOID AS $$
DECLARE
user_cursor CURSOR (min_a INTEGER, max_a INTEGER) FOR
SELECT * FROM users WHERE age BETWEEN min_a AND max_a;
user_record users%ROWTYPE;
BEGIN
OPEN user_cursor(min_age, max_age);
LOOP
FETCH user_cursor INTO user_record;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'User: %, Age: %', user_record.name, user_record.age;
END LOOP;
CLOSE user_cursor;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION get_user_cursor()
RETURNS REFCURSOR AS $$
DECLARE
user_cursor REFCURSOR;
BEGIN
OPEN user_cursor FOR SELECT * FROM users;
RETURN user_cursor;
END;
$$ LANGUAGE plpgsql;
-- Вызов в транзакции
BEGIN;
SELECT get_user_cursor();
FETCH ALL FROM "<имя_курсора>";
COMMIT;-- Открытие статического запроса
OPEN cursor_name FOR SELECT * FROM users;
-- Открытие с параметрами
OPEN cursor_name FOR EXECUTE 'SELECT * FROM users WHERE age > $1' USING min_age;
-- Открытие динамического запроса
OPEN cursor_name FOR EXECUTE dynamic_sql;-- Получить одну строку
FETCH cursor_name INTO record_var;
-- Получить следующую строку
FETCH NEXT FROM cursor_name INTO record_var;
-- Получить предыдущую строку
FETCH PRIOR FROM cursor_name INTO record_var;
-- Получить первую строку
FETCH FIRST FROM cursor_name INTO record_var;
-- Получить последнюю строку
FETCH LAST FROM cursor_name INTO record_var;
-- Получить N строк
FETCH FORWARD 10 FROM cursor_name INTO record_var;
-- Получить все строки
FETCH ALL FROM cursor_name INTO record_var;-- Переместить курсор без получения данных
MOVE cursor_name; -- следующая строка
MOVE FORWARD 10 FROM cursor_name; -- пропустить 10 строк
MOVE BACKWARD 5 FROM cursor_name; -- назад на 5 строк-- Проверка положения курсора
-- После FETCH можно проверить FOUND
IF FOUND THEN
-- строка получена
ELSE
-- курсор исчерпан
END IF;CLOSE cursor_name;CREATE OR REPLACE FUNCTION send_newsletter()
RETURNS VOID AS $$
DECLARE
user_cursor CURSOR FOR
SELECT id, name, email, preferences
FROM users
WHERE is_subscribed = true;
user_record RECORD;
email_content TEXT;
BEGIN
OPEN user_cursor;
LOOP
FETCH user_cursor INTO user_record;
EXIT WHEN NOT FOUND;
-- Индивидуальная логика для каждого пользователя
IF user_record.preferences LIKE '%sports%' THEN
email_content := 'Sports newsletter...';
ELSIF user_record.preferences LIKE '%tech%' THEN
email_content := 'Tech newsletter...';
ELSE
email_content := 'General newsletter...';
END IF;
-- Отправка email (псевдофункция)
PERFORM send_email(user_record.email, 'Newsletter', email_content);
-- Логирование
INSERT INTO email_log (user_id, sent_at)
VALUES (user_record.id, NOW());
END LOOP;
CLOSE user_cursor;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION recalculate_user_scores()
RETURNS VOID AS $$
DECLARE
user_cursor CURSOR FOR
SELECT id, total_orders, total_spent
FROM users
FOR UPDATE; -- блокировка строк
user_record RECORD;
new_score NUMERIC;
BEGIN
OPEN user_cursor;
LOOP
FETCH user_cursor INTO user_record;
EXIT WHEN NOT FOUND;
-- Расчёт нового scores
new_score := user_record.total_orders * 10 + user_record.total_spent * 0.01;
-- Обновление текущей строки
UPDATE users
SET score = new_score,
score_updated_at = NOW()
WHERE CURRENT OF user_cursor;
END LOOP;
CLOSE user_cursor;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION process_in_batches(batch_size INTEGER DEFAULT 100)
RETURNS VOID AS $$
DECLARE
user_cursor CURSOR FOR SELECT id FROM users WHERE processed = false;
user_ids INTEGER[];
user_id INTEGER;
counter INTEGER := 0;
BEGIN
OPEN user_cursor;
LOOP
user_ids := ARRAY[]::INTEGER[];
-- Сбор пакета
FOR i IN 1..batch_size LOOP
FETCH user_cursor INTO user_id;
EXIT WHEN NOT FOUND;
user_ids := array_append(user_ids, user_id);
END LOOP;
-- Выход если данные закончились
EXIT WHEN array_length(user_ids, 1) IS NULL;
-- Обработка пакета
UPDATE users
SET processed = true,
processed_at = NOW()
WHERE id = ANY(user_ids);
counter := counter + array_length(user_ids, 1);
RAISE NOTICE 'Processed batch, total: %', counter;
END LOOP;
CLOSE user_cursor;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION process_table_dynamic(table_name TEXT)
RETURNS VOID AS $$
DECLARE
dynamic_cursor REFCURSOR;
record_var RECORD;
query TEXT;
BEGIN
-- Динамическое создание курсора
query := format('SELECT * FROM %I WHERE created_at > NOW() - INTERVAL ''30 days''', table_name);
OPEN dynamic_cursor FOR EXECUTE query;
LOOP
FETCH dynamic_cursor INTO record_var;
EXIT WHEN NOT FOUND;
-- Обработка записи
RAISE NOTICE 'Processing: %', record_var;
END LOOP;
CLOSE dynamic_cursor;
END;
$$ LANGUAGE plpgsql;Динамический SQL позволяет выполнять запросы, сформированные во время выполнения.
CREATE OR REPLACE FUNCTION get_table_count(table_name TEXT)
RETURNS BIGINT AS $$
DECLARE
count_val BIGINT;
query TEXT;
BEGIN
query := format('SELECT COUNT(*) FROM %I', table_name);
EXECUTE query INTO count_val;
RETURN count_val;
END;
$$ LANGUAGE plpgsql;-- Плохо: уязвимо к SQL-инъекциям
CREATE OR REPLACE FUNCTION unsafe_query(table_name TEXT, column_name TEXT)
RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT * FROM ' || table_name || ' WHERE ' || column_name || ' = 1';
END;
$$ LANGUAGE plpgsql;
-- Хорошо: с использованием format()
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 | Идентификатор (таблица, столбец) | format('SELECT * FROM %I', 'users') |
%L | Литерал (значение) | format('WHERE name = %L', 'O''Brien') |
%s | Простая подстановка | format('Hello, %s', 'World') |
%% | Экранирование % | format('100%%') |
-- Примеры использования
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 123);
-- SELECT * FROM "users" WHERE id = '123'
SELECT format('UPDATE %I SET %I = %L WHERE %I = %L',
'users', 'name', 'John', 'id', '1');
-- UPDATE "users" SET "name" = 'John' WHERE "id" = '1'CREATE OR REPLACE FUNCTION get_dynamic_data(
table_name TEXT,
filter_column TEXT DEFAULT NULL,
filter_value TEXT DEFAULT NULL
)
RETURNS SETOF RECORD AS $$
DECLARE
query TEXT;
BEGIN
IF filter_column IS NOT NULL AND filter_value IS NOT NULL THEN
query := format(
'SELECT * FROM %I WHERE %I = $1',
table_name, filter_column
);
RETURN QUERY EXECUTE query USING filter_value;
ELSE
query := format('SELECT * FROM %I', table_name);
RETURN QUERY EXECUTE query;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Вызов (нужно указать структуру результата)
SELECT * FROM get_dynamic_data('users', 'status', 'active')
AS (id INTEGER, name TEXT, status TEXT);CREATE OR REPLACE FUNCTION create_index_if_not_exists(
index_name TEXT,
table_name TEXT,
column_name TEXT
)
RETURNS VOID AS $$
DECLARE
query TEXT;
BEGIN
-- Проверка существования индекса
IF NOT EXISTS (
SELECT 1 FROM pg_indexes
WHERE indexname = index_name
) THEN
query := format(
'CREATE INDEX %I ON %I (%I)',
index_name, table_name, column_name
);
EXECUTE query;
RAISE NOTICE 'Index % created', index_name;
ELSE
RAISE NOTICE 'Index % already exists', index_name;
END IF;
END;
$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION create_partition(
table_name TEXT,
partition_name TEXT,
start_date DATE,
end_date DATE
)
RETURNS VOID AS $$
DECLARE
query TEXT;
BEGIN
query := format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
FOR VALUES FROM (%L) TO (%L)',
partition_name, table_name, start_date, end_date
);
EXECUTE query;
RAISE NOTICE 'Partition % created', partition_name;
END;
$$ LANGUAGE plpgsql;-- Медленно: курсор для простого обновления
FOR rec IN SELECT * FROM users LOOP
UPDATE users SET score = score + 1 WHERE id = rec.id;
END LOOP;
-- Быстро: множественное обновление
UPDATE users SET score = score + 1;-- Использование WHERE CURRENT OF для эффективности
DECLARE
cursor_name CURSOR FOR SELECT * FROM users FOR UPDATE;
BEGIN
LOOP
FETCH cursor_name INTO rec;
EXIT WHEN NOT FOUND;
-- Обновление текущей строки без повторного поиска
UPDATE users SET score = score + 1
WHERE CURRENT OF cursor_name;
END LOOP;
END;-- Просмотр активных курсоров
SELECT * FROM pg_cursors;
-- Закрытие всех курсоров
CLOSE ALL;Теперь вы умеете работать с курсорами и динамическим SQL. В следующей теме вы изучите оконные функции и CTE — мощные инструменты для аналитических запросов.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.