Dynamiczny SQL w PL/pgSQL służy do ograniczenia powtarzających się zadań.
Przykład 1
Można użyć dynamicznego SQL, aby codziennie tworzyć partycjonowanie tabeli dla określonej tabeli, dodawać brakujące indeksy do wszystkich kluczy obcych lub dodawać możliwości audytu danych do określonej tabeli bez większych efektów kodowania.
Przykład 2
Ponieważ zapytania wykonywane przy użyciu instrukcji EXECUTE nie są buforowane, ważnym zastosowaniem dynamicznego SQL jest przezwyciężenie skutków ubocznych buforowania PL/pgSQL.
Instrukcja EXECUTE
Dynamiczny SQL uzyskuje się za pomocą instrukcji EXECUTE. Instrukcja EXECUTE akceptuje ciąg i poddaje go ocenie.
Osadzone instrukcje SQL dostępne w PL/pgSQL takie jak SELECT czy INSERT, mają stałą strukturę – tzn są poddawane analizie w czasie kompilacji.
Musimy użyć funkcji dynamicznego SQL, jeśli potrzebujemy:
- Struktury instrukcji SQL, która ma zostać zmieniona w czasie wykonywania
- Dostępu do instrukcji języka definicji danych (DDL) i innych funkcji SQL w PL/pgSQL
W przypadku dynamicznego SQL instrukcje SQL są tworzone dynamicznie w czasie wykonywania, mogą uzyskiwać dostęp do zmiennych PL/pgSQL i korzystać z nich.
Wykonywanie instrukcji DDL w dynamicznym SQL
Czasem musimy wykonywać operacje DDL na poziomie obiektów bazy danych, takich jak tabele, indeksy, kolumny, role i tak dalej. Na przykład programista bazy danych chciałby przeprowadzić proces VACUUMINGU i przeanalizować określony obiekt schematu – w celu zaktualizowania statystyk.
Przypadek użycia: Analiza tabel schematu dvdrental
Aby przeanalizować tabele schematu dvdrental, można napisać następujący skrypt:
DO $$
DECLARE
table_name text;
BEGIN
FOR table_name IN
SELECT tablename
FROM pg_tables
WHERE schemaname ='dvdrental'
LOOP
RAISE NOTICE 'Analiza %', table_name;
EXECUTE 'ANALYZE dvdrental.' || table_name;
END LOOP;
END;
$$;Wykonywanie instrukcji DML w dynamicznym SQL
Bywają aplikacje, które mają koniecznośc wchodzenia w interakcję z danymi w sposób interaktywny. Niektóre aplikacje filtrują dane według różnych kryteriów zdefiniowanych przez użytkownika. I właśnie w takich przypadkach bardzo przydaje się dynamiczny SQL. Przykład: wyszukiwanie w pewnej aplikacjiW aplikacji potrzebna jest funkcja wyszukiwania, skonstruowana w taki sposób że pozwala uzyskać informacje o Kliencie (Customer) za pomocą predykatu dynamicznego, w następujący sposób:
CREATE OR REPLACE FUNCTION wyszukiwarka_get_customer (predykat TEXT)
RETURNS SETOF customer AS
$$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM customer WHERE ' || predykat;
END;
$$ LANGUAGE plpgsql;Przykład wywołania funkcji:
SELECT * FROM wyszukiwarka_get_customer ('true') LIMIT 1;Chcesz dowiedzieć się więcej na ten temat? Zapraszamy na szkolenia!!!
