我想知道一个函数是内置的还是用户定义的。我试着写下面的查询,但是没有任何东西告诉我是两者之一还是两者之一。我在pgadmin中搜索了一个函数后,从postgres日志中获得了这个查询。结果列表也显示了像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
1条答案
按热度按时间zyfwsgd61#
Postgres psql反斜杠命令
\dfn
显示用户函数。\dfnS
显示内置函数。\dfnS+
将给出更详细的信息。命令psql -E
将显示psql中任何反斜杠命令后面的查询。值得注意的是,如果你不记得完整的名称,\dfnS c*
将仅限于以c开头的查询。