Geek asks and answers

Get all procedural, user-defined functions

How to get a list of all user functions through a request SQL?

I find this code here

SELECT p.proname, p.pronargs, t.typname
 FROM pg_proc p, pg_language l, pg_type t
 WHERE p.prolang = l.oid
 and p.prorettype = t.oid
 and l.lanname = 'c'
ORDER BY proname;

but it gets C functions

How to get custom procedural language functions, for example, in a language plpgsql?

+4
source share
1 answer

Consider:

select 
    pp.proname,
    pl.lanname,
    pn.nspname,
    pg_get_functiondef(pp.oid)
from pg_proc pp
inner join pg_namespace pn on (pp.pronamespace = pn.oid)
inner join pg_language pl on (pp.prolang = pl.oid)
where pl.lanname NOT IN ('c','internal') 
  and pn.nspname NOT LIKE 'pg_%'
  and pn.nspname <> 'information_schema';

See also: What is a command to find a script for an existing function in postgresql?

Use columns to pg_get_functiondefor prosrcfrom pg_procdirectly. The basic idea is to join pg_namespaceand filter out the PostgreSQL directory functions, which are likely to be adequate for most purposes:

FROM pg_proc pp INNER JOIN pg_namespace ON (pp.pronamespace = pn.oid)
WHERE pn.nspname <> 'pg_catalog'

, , - , . :

  • , CREATE EXTENSION.
  • , PostgreSQL.
  • , .

pg_proc, .

C, . , .

+17

More articles:


All Articles