PostgreSQL | Как получить исходные коды всех функций базы данных?

PostgreSQL | Как получить исходные коды всех функций базы данных?

Если в базе данных много функций, то однажды в них будет сложно искать информацию. Речь идёт о составе исходных кодов самих функций. Например, нас может интересовать место (или места) где в функции делается вставка в определённую таблицу или где идёт обновление значений какого-нибудь столбца таблицы.

Для поиска по исходным кодам:

  • мы либо используем терминальный клиент,
  • либо делаем дамп структуры базы данных,
  • либо используем SQL-запросник для получения исходных кодов функций внутри программ-клиентов к PostgreSQL (pgadmin или DBeaver).

Терминальный клиент — это неудобно. Дамп структуры — это много лишнего. А вот третий вариант нам подходит, разберём его.

-- Получаем исходные коды всех функций БД
select 
   n.nspname as function_schema,
   p.proname as function_name,
   l.lanname as function_language,
   case when l.lanname = 'internal' then p.prosrc
        else pg_get_functiondef(p.oid)
        end as definition,
   pg_get_function_arguments(p.oid) as function_arguments,
   t.typname as return_type
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype
where n.nspname not in ('pg_catalog', 'information_schema')
order by function_schema, function_name;

Пример данных, которые получаются из запроса:

Получили исходные коды всех функций базы данных через pg_proc в PostgreSQL
Получили исходные коды всех функций базы данных через pg_proc в PostgreSQL

Могут возникнуть проблемы с символами переносов строки. Можно воспользоваться регулярными выражениями и привести «definition» к нужному виду. Но для поиска по вхождению в исходный код это не повлияет. Чисто для себя — для корректного отображения исходного кода функций.

 

Как работает запрос на получение исходных кодов всех функций базы данных PostgreSQL?

Нам нужен системный каталог «pg_proc». В каталоге «pg_proc» хранится информация об обычных функциях, процедурах, агрегатных и оконных функциях (в совокупности также называемых подпрограммами). В этом каталоге перечислено всё что может быть, в том числе и функции из пространства имён самого PostgreSQL — «pg_catalog» и «information_schema«. Нас они не интересуют.

Чтобы их исключить из итогового результата и оставить только собственные разработки, нужно предварительно выполнить соединение с другим системным каталогом «pg_namespace». Мы связываем:

left join pg_namespace n on p.pronamespace = n.oid

Теперь у нас будет возможность отфильтровать результаты через условие:

where n.nspname not in ('pg_catalog', 'information_schema')

Останутся только функции из всех схем нашей базы данных, которые мы создали. Если вам нужны прям ВСЕ ВСЕ функции, то можете не выполнять эту фильтрацию.

 

Куда устанавливаются функции расширений PostgreSQL?

Важно учитывать тот факт, что функции установленных расширений также устанавливаются в схемы этой базы данных. Если вы устанавливали какие-то расширения (например, btree_gin или btree_gist), то их функции также попадут в результат запроса.

Ниже пример отбора всех функций кроме пространства имён ‘pg_catalog‘.

Функции расширений и информационной схемы в PostgreSQL
Функции расширений и информационной схемы в PostgreSQL