Home > Fragmenty szkoleń > Dynamiczny SQL w Pl/pgSQL

Dynamiczny SQL w Pl/pgSQL

dynamiczny sql w pl/pgsql

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!!!

Może Cię również zainteresować: