Verschachtelte Prozeduraufrufe mit EXECUTE: Dynamische Ausführung von SQL-Code

Bevor wir zur Praxis kommen, lass uns die Frage klären: Was ist eigentlich dynamisches SQL? Stell dir vor, du musst eine Tabelle mit einem einzigartigen Namen erstellen, der als Parameter übergeben wird. Oder du willst eine Abfrage auf eine Tabelle ausführen, deren Name erst zur Laufzeit festgelegt wird. Hier reicht einfaches statisches SQL nicht aus – genau da hilft die dynamische Ausführung.

PL/pgSQL stellt den Befehl EXECUTE bereit, der ein als String übergebenes SQL-Statement ausführt. Damit kannst du SQL-Code "on the fly" bauen und starten, also Abfragen erzeugen, die je nach Parametern unterschiedlich sind.

Warum kann dynamisches SQL nützlich sein?

1. Flexibilität: Die Möglichkeit, Abfragen dynamisch je nach Eingabedaten zu bauen. Zum Beispiel Operationen auf Tabellen oder Spalten ausführen, deren Namen du vorher nicht kennst.
2. Automatisierung: Tabellen oder Indizes mit einzigartigen Namen erstellen.
3. Universalität: Mit unterschiedlichen Datenstrukturen arbeiten, ohne die Prozedur jedes Mal neu schreiben zu müssen.

Ein Beispiel aus dem echten Leben: Stell dir vor, du entwickelst ein Analytics-System und für jeden neuen Kunden musst du eine eigene Tabelle für dessen Daten anlegen. Das kannst du alles mit EXECUTE automatisieren.

Syntax von EXECUTE

Die Verwendung von dynamischem SQL mit EXECUTE sieht so aus:

EXECUTE 'SQL-String';

Ein einfaches Beispiel:

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

Dieser Code-Block erstellt die Tabelle test_table. Ganz easy, aber lass uns mal auf komplexere Szenarien schauen.

Beispiele für die Verwendung von EXECUTE

1. Tabelle mit dynamischem Namen erstellen

Angenommen, du sollst Tabellen mit Namen erstellen, die vom aktuellen Datum abhängen. So geht das:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Tabellennamen generieren
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Tabelle mit dynamischem Namen erstellen
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Nachricht zur Kontrolle ausgeben
  RAISE NOTICE 'Tabelle % wurde erfolgreich erstellt', table_name;
END $$;

Hier wird der dynamische Name aus dem aktuellen Datum gebaut und der finale SQL-String an EXECUTE übergeben.

2. Abfrage mit dynamischen Parametern ausführen

Angenommen, du willst Daten aus einer Tabelle holen, deren Name als Parameter übergeben wird. Lass uns dafür eine Funktion bauen:

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;

Funktionsaufruf:

SELECT * FROM get_data_from_table('employees');

Dieser Ansatz ist super für universelle Tools wie dynamische Reporting-Systeme.

Probleme und Einschränkungen von dynamischem SQL

Dynamische Ausführung von SQL-Code gibt dir viel Freiheit, aber wie im echten Leben kommt mit Freiheit auch Verantwortung. Hier kann es tricky werden:

1. SQL-Injections: Wenn du String-Parameter ohne Absicherung in die Abfrage einfügst, kann ein Angreifer beliebigen SQL-Code ausführen.

   Beispiel für unsicheren Code:

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

   Wenn user_input den String '; DROP TABLE users; -- enthält, wird die Tabelle users gelöscht.

2. Schwierige Fehlersuche: Dynamischer Code ist schwerer zu analysieren und zu debuggen, weil die Abfrage erst zur Laufzeit gebaut und ausgeführt wird.

3. Performance-Verlust: Dynamische Abfragen umgehen das Caching des Ausführungsplans in PostgreSQL, was zu schlechterer Performance führen kann.

Wie schützt man sich vor SQL-Injections

Um SQL-Injection-Angriffe zu vermeiden, solltest du in dynamischen Abfragen Parameterisierung statt einfacher String-Konkatenation nutzen. In PL/pgSQL geht das mit quote_literal() für Strings und quote_ident() für Bezeichner (wie Tabellennamen oder Spaltennamen).

Beispiel für sicheren Code:

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

Implementierung: Dynamisches Update von Tabellen

Hier ein Beispiel für eine Prozedur, die Werte in einer Tabelle mit übergebenem Namen aktualisiert:

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;

Funktionsaufruf:

SELECT update_table_data('test_table', 1, 'Aktualisierter Wert');

Beispiel: Report für einen Kunden erstellen

Angenommen, du verwaltest Bestellungen pro Kunde und willst den Prozess automatisieren, für jeden Kunden eine Report-Tabelle zu erstellen.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Namen der Report-Tabelle bauen
  table_name := 'client_report_' || client_id;

  -- Tabelle für den Report erstellen
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Tabelle mit Daten füllen
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Report für Kunde % erstellt: Tabelle %', client_id, table_name;
END $$ LANGUAGE plpgsql;

Dynamisches SQL mit EXECUTE ist ein mächtiges Tool, das dir krasse Möglichkeiten für Automatisierung und Flexibilität in PL/pgSQL gibt. Nutze es mit Bedacht und denk immer an die Risiken von SQL-Injections. Wenn du willst, dass deine Abfragen sicher und stabil sind, verwende quote_ident() und quote_literal().

In der nächsten Vorlesung tauchen wir tiefer in die Erstellung komplexer Prozeduren ein, inklusive Datenvalidierung, Update von Datensätzen und Logging von Aktionen. Mach dich bereit, denn die Arbeit mit dynamischen Abfragen wird die Basis für solche Aufgaben sein!

1

Umfrage/Quiz

Verschachtelte Transaktionen, Level 53, Lektion 4

Nicht verfügbar

Verschachtelte Transaktionen

Verschachtelte Transaktionen

Öffnen