Вкладені виклики процедур з EXECUTE: динамічне виконання SQL-коду

Перш ніж перейти до практики, давай відповімо на питання: що таке динамічний SQL? Уяви, що тобі треба створити таблицю з унікальним імʼям, яке передається як параметр. Або виконати запит до таблиці, імʼя якої визначається під час виконання програми. Тут простого статичного SQL вже не вистачає — ось де виручає динамічне виконання.

PL/pgSQL дає команду EXECUTE, яка виконує SQL-запит, переданий як рядок. Це дає можливість будувати й запускати SQL-код "на льоту", створюючи запити, які змінюються залежно від параметрів.

Чому динамічний SQL може бути корисним:

1. Гнучкість: Можливість будувати запити динамічно залежно від вхідних даних. Наприклад, виконання операцій над таблицями чи колонками, імена яких невідомі заздалегідь.
2. Автоматизація: Створення таблиць або індексів з унікальними іменами.
3. Універсальність: Можливість працювати з різною структурою даних без переписування процедури.

Приклад з життя: уяви, що ти розробляєш аналітичну систему, і для кожного нового клієнта треба створити окрему таблицю для їхніх даних. Все це можна автоматизувати через EXECUTE.

Синтаксис EXECUTE

Використання динамічного SQL через EXECUTE виглядає так:

EXECUTE 'SQL-рядок';

Приклад простого запиту:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

Цей блок коду створить таблицю test_table. Все просто, але давай розглянемо складніші сценарії.

Приклади використання EXECUTE

1. Створення таблиці з динамічним імʼям

Припустимо, у тебе є задача створювати таблиці з іменами, які залежать від поточної дати. Ось як це зробити:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Генеруємо імʼя таблиці
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Створюємо таблицю з динамічним імʼям
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Виводимо повідомлення для перевірки
  RAISE NOTICE 'Таблиця % успішно створена', table_name;
END $$;

Тут динамічне імʼя генерується з поточної дати, а фінальний SQL-рядок передається в EXECUTE.

2. Виконання запиту з динамічними параметрами

Допустимо, треба витягнути дані з таблиці, імʼя якої передається як параметр. Давай напишемо функцію для цього:

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

Виклик функції:

SELECT * FROM get_data_from_table('employees');

Такий підхід ідеально підходить для створення універсальних утиліт, наприклад, динамічних звітних систем.

Проблеми та обмеження динамічного SQL

Динамічне виконання SQL-коду дає багато свободи, але, як і в житті, за свободу треба відповідати. Ось де можуть бути нюанси:

1. SQL-інʼєкції: якщо ти передаєш рядкові параметри в запит без обробки, зловмисник може виконати будь-який SQL-код.

   Приклад уразливого коду:

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   Якщо user_input містить рядок '; DROP TABLE users; --, то запит знищить таблицю users.

2. Складність дебагу: динамічний код важче аналізувати й дебажити, бо запит будується й виконується під час виконання.

3. Втрата продуктивності: динамічні запити обходять кешування плану виконання в PostgreSQL, що може знизити продуктивність.

Як захиститися від SQL-інʼєкцій

Щоб уникнути атак SQL-інʼєкцій, використовуй параметризацію в динамічних запитах замість простої конкатенації рядків. У PL/pgSQL це робиться через quote_literal() для рядкових параметрів і quote_ident() для ідентифікаторів (наприклад, імен таблиць чи колонок).

Приклад безпечного коду:

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

Реалізація: динамічне оновлення таблиць

Ось приклад процедури, яка оновлює значення в таблиці з імʼям, переданим як параметр:

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

Виклик функції:

SELECT update_table_data('test_table', 1, 'Оновлене значення');

Приклад: створення звіту для клієнта

Уяви, ти ведеш облік замовлень по клієнтах і хочеш автоматизувати процес створення таблиці звітів для кожного клієнта.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Формуємо імʼя таблиці звіту
  table_name := 'client_report_' || client_id;

  -- Створюємо таблицю для звіту
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Заповнюємо таблицю даними
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Звіт для клієнта % створено: таблиця %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Динамічний SQL з EXECUTE — це потужний інструмент, який відкриває неймовірні можливості для автоматизації й гнучкості в PL/pgSQL. Використовуй його обережно, памʼятаючи про ризики SQL-інʼєкцій. Якщо хочеш, щоб твої запити були надійними й безпечними, юзай quote_ident() і quote_literal().

На наступній лекції ми заглибимося у створення комплексних процедур, що включають валідацію даних, оновлення записів і логування операцій. Готуйся до того, що робота з динамічними запитами стане основою для таких задач!

1

Опитування

Вкладені транзакції, рівень 53, лекція 4

Недоступний

Вкладені транзакції

Вкладені транзакції

Відкрити