PostgreSQL: Jak wyświetlić listę wszystkich przechowywanych funkcji, które uzyskują dostęp do określonej tabeli

Wprowadzenie:

Baza danych PostgreSQL zawierająca kilkaset zapisanych funkcji, w tym przestarzałe, nieużywane itp.

Problem

Muszę znaleźć wszystkie przechowywane funkcje, które mają jakikolwiek związek z tabelą X - ponieważ chcę zmienić strukturę tabeli. Niektóre z nich mogą nie być używane, więc nie mogę tego zrobić, przeglądając kod.

Rozwiązaniem, które mam ATM, jest uruchomienie psql \df+i grepping, ale wolałbym rozwiązanie bardziej podobne do bazy danych, tj. Przy użyciu schematu informacji. To z pewnością będzie powtarzalne zadanie i chciałbym, aby było ładnie i czysto.

Jakieś sugestie?

Ciało funkcji jest po prostu przechowywane jako ciąg . Nie ma listy obiektów, do których istnieją odniesienia. (Różni się to na przykład od widoków, w których zapisywane są rzeczywiste linki do tabel, do których istnieją odniesienia).

To zapytanie dla Postgres 10 lub starszej wykorzystuje funkcję informacji katalogu systemowegopg_get_functiondef() do zrekonstruowania CREATE FUNCTIONskryptu dla odpowiednich funkcji i wyszukuje nazwę tabeli za pomocą wyrażenia regularnego bez rozróżniania wielkości liter:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  NOT p.proisagg
AND    n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
AND    pg_get_functiondef(p.oid) ~* '\mbig\M';

Powinien wykonać zadanie, ale oczywiście nie jest kuloodporny. Może się nie powieść w przypadku dynamicznego SQL, gdzie nazwa tabeli jest generowana dynamicznie i może zwrócić dowolną liczbę fałszywych alarmów - szczególnie jeśli nazwa tabeli jest wspólnym słowem.

Funkcje agregujące i wszystkie funkcje ze schematów systemowych są wykluczone.

\mi\M zaznacz początek i koniec słowa w wyrażeniu regularnym.

Katalog systemu pg_proczmieniony w Postgres 11. proisaggzostał zastąpiony przez prokind, dodano prawdziwe procedury składowane. Musisz się dostosować. Związane z:

• Jak usunąć wszystkie moje funkcje z PostgreSQL?

To zapytanie jest dość łatwe w użyciu:

SELECT proname, proargnames, prosrc FROM pg_proc WHERE prosrc ILIKE '%Text to search%';