CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Oto Shavadze
Oto Shavadze

Reputation: 42763

Get all procedural , user defined functions

How to get list of all user defined functions via SQL query ?

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 this gets C-functions

How to get user defined, procedural language functions, writen for example in plpgsql language?

Upvotes: 9

Views: 9820

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324455

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 the command to find script of a existing function in postgresql?

Use pg_get_functiondef or the prosrc column from pg_proc directly. The key idea is to join on pg_namespace and filter out the PostgreSQL catalog functions, which will probably be adequate for most purposes:

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

The trouble with obtaining the source code for user defined functions is deciding what user means. Many types of functions can be created:

  • Functions using CREATE EXTENSION.
  • Functions created by PostgreSQL.
  • Functions compiled and installed by an administrator.

Superusers with sufficent grants can define functions in pg_proc, but usually don't.

Since only superusers can create C language functions, exclude them. Such functions can be custom-installed on a particular database by the admin, but not a normal user.

Upvotes: 29

Related Questions