Zagnieżdżone wywołania procedur z EXECUTE: dynamiczne wykonywanie kodu SQL

Zanim przejdziemy do praktyki, odpowiedzmy sobie na pytanie: czym właściwie jest dynamiczny SQL? Wyobraź sobie, że musisz stworzyć tabelę z unikalną nazwą przekazaną jako parametr. Albo wykonać zapytanie do tabeli, której nazwa jest ustalana w trakcie działania programu. Tutaj zwykły statyczny SQL nie wystarczy — i właśnie wtedy przydaje się dynamiczne wykonanie.

PL/pgSQL udostępnia komendę EXECUTE, która wykonuje zapytanie SQL przekazane jako string. Dzięki temu możesz budować i odpalać kod SQL "w locie", tworząc zapytania, które różnią się w zależności od parametrów.

Dlaczego dynamiczny SQL może być przydatny:

1. Elastyczność: Możliwość budowania zapytań dynamicznie w zależności od wejściowych danych. Na przykład operacje na tabelach lub kolumnach, których nazwy nie są znane z góry.
2. Automatyzacja: Tworzenie tabel lub indeksów z unikalnymi nazwami.
3. Uniwersalność: Możliwość pracy z różną strukturą danych bez konieczności przepisywania procedury.

Przykład z życia: wyobraź sobie, że tworzysz system analityczny i dla każdego nowego klienta musisz stworzyć osobną tabelę na jego dane. Wszystko to można zautomatyzować za pomocą EXECUTE.

Składnia EXECUTE

Użycie dynamicznego SQL przez EXECUTE wygląda tak:

EXECUTE 'SQL-string';

Przykład prostego zapytania:

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

Ten blok kodu stworzy tabelę test_table. Proste, ale zobaczmy bardziej zaawansowane przypadki.

Przykłady użycia EXECUTE

1. Tworzenie tabeli z dynamiczną nazwą

Załóżmy, że masz zadanie tworzyć tabele z nazwami zależnymi od aktualnej daty. Tak to można zrobić:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Generujemy nazwę tabeli
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Tworzymy tabelę z dynamiczną nazwą
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Wyświetlamy komunikat do sprawdzenia
  RAISE NOTICE 'Tabela % została pomyślnie utworzona', table_name;
END $$;

Tutaj dynamiczna nazwa jest generowana z aktualnej daty, a końcowy string SQL przekazywany do EXECUTE.

2. Wykonanie zapytania z dynamicznymi parametrami

Załóżmy, że musisz pobrać dane z tabeli, której nazwa jest przekazywana jako parametr. Stwórzmy do tego funkcję:

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;

Wywołanie funkcji:

SELECT * FROM get_data_from_table('employees');

To podejście świetnie sprawdza się przy budowie uniwersalnych narzędzi, takich jak dynamiczne systemy raportowe.

Problemy i ograniczenia dynamicznego SQL

Dynamiczne wykonywanie kodu SQL daje dużą swobodę, ale — jak w życiu — za wolność trzeba płacić. Oto gdzie mogą pojawić się trudności:

1. SQL-injection: jeśli przekazujesz stringowe parametry do zapytania bez obróbki, możesz dać atakującemu możliwość wykonania dowolnego kodu SQL.

   Przykład podatnego kodu:

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

   Jeśli user_input zawiera string '; DROP TABLE users; --, to zapytanie usunie tabelę users.

2. Trudność debugowania: dynamiczny kod trudniej analizować i debugować, bo zapytanie buduje się i wykonuje w trakcie działania.

3. Utrata wydajności: dynamiczne zapytania omijają mechanizmy cache'owania planu wykonania w PostgreSQL, co może prowadzić do spadku wydajności.

Jak się chronić przed SQL-injection

Żeby uniknąć ataków SQL-injection, używaj parametryzacji w dynamicznych zapytaniach zamiast zwykłego łączenia stringów. W PL/pgSQL robi się to przez funkcję quote_literal() dla parametrów tekstowych i quote_ident() dla identyfikatorów (np. nazw tabel lub kolumn).

Przykład bezpiecznego kodu:

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 $$;

Implementacja: dynamiczna aktualizacja tabel

Oto przykład procedury, która aktualizuje wartości w tabeli o nazwie przekazanej jako parametr:

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;

Wywołanie funkcji:

SELECT update_table_data('test_table', 1, 'Zaktualizowana wartość');

Przykład: tworzenie raportu dla klienta

Załóżmy, że prowadzisz ewidencję zamówień klientów i chcesz zautomatyzować proces tworzenia tabeli raportowej dla każdego klienta.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Tworzymy nazwę tabeli raportowej
  table_name := 'client_report_' || client_id;

  -- Tworzymy tabelę dla raportu
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Wypełniamy tabelę danymi
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Raport dla klienta % utworzony: tabela %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Dynamiczny SQL z EXECUTE to potężne narzędzie, które daje niesamowite możliwości automatyzacji i elastyczności w PL/pgSQL. Używaj go z głową, pamiętając o ryzyku SQL-injection. Jeśli chcesz, żeby Twoje zapytania były solidne i bezpieczne, stosuj funkcje quote_ident() i quote_literal().

Na następnej lekcji zagłębimy się w tworzenie złożonych procedur, obejmujących walidację danych, aktualizację rekordów i logowanie operacji. Przygotuj się na to, że praca z dynamicznymi zapytaniami stanie się podstawą do realizacji takich zadań!

1

Ankieta/quiz

Zagnieżdżone transakcje, poziom 53, lekcja 4

Niedostępny

Zagnieżdżone transakcje

Zagnieżdżone transakcje

Otwórz