'How to find if a function is inbuilt function or user-defined in postgres
I would like to know if a function is inbuilt or its a user defined function in postgres. I have tried writing the query below, but there is nothing that tells me if its either or of the two. I took this query from postgres logs after i searched for a function in pgadmin. The result list also show functions like dblink.
SELECT obj_type, function_owner,obj_name,
pg_catalog.REPLACE(obj_path, '/'||sn.schema_name||'/', '/'||CASE sn.schema_name
WHEN 'pg_catalog' THEN 'PostgreSQL Catalog (pg_catalog)'
WHEN 'pgagent' THEN 'pgAgent Job Scheduler (pgagent)'
WHEN 'information_schema' THEN 'ANSI (information_schema)'
ELSE sn.schema_name
END||'/') AS obj_path,
schema_name, show_node, other_info,
CASE
WHEN sn.schema_name IN ('pg_catalog', 'pgagent', 'information_schema') THEN
CASE WHEN CASE
WHEN sn.schema_name = ANY('{information_schema}')
THEN false
ELSE true END THEN 'D' ELSE 'O' END
ELSE 'N'
END AS catalog_level
FROM (
SELECT
proowner::regrole::text function_owner,
CASE
WHEN t.typname IN ('trigger', 'event_trigger') THEN 'trigger_function'
ELSE 'function' END::text AS obj_type, p.proname AS obj_name,
':schema.'|| n.oid || ':/' || n.nspname || '/' || case when t.typname = 'trigger' then ':trigger_function.' else ':function.' end || p.oid ||':/' || p.proname AS obj_path, n.nspname AS schema_name
,
CASE WHEN t.typname IN ('trigger', 'event_trigger') THEN True ELSE True END AS show_node,
pg_catalog.pg_get_function_identity_arguments(p.oid) AS other_info
from pg_catalog.pg_proc p
left join pg_catalog.pg_namespace n on p.pronamespace = n.oid
left join pg_catalog.pg_type t on p.prorettype = t.oid
WHERE (CASE
WHEN n.nspname = ANY('{information_schema}')
THEN false
ELSE true END) AND NOT p.proisagg
) sn
where 1=1 --lower(sn.obj_name) like '%test12345%'
AND NOT (sn.schema_name IN ('pg_catalog', 'pgagent', 'information_schema'))
AND (sn.schema_name IS NOT NULL AND sn.schema_name NOT LIKE 'pg\_%')
and function_owner <> 'appuser'
ORDER BY 1, 2, 3
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
