How can I get a list of all functions stored in the database of a particular schema in PostgreSQL

Question

I want to be able to connect to a PostgreSQL database and find all of the functions for a particular schema   My thought was that I could make some query to pg catalog or information schema and get a list of all functions  but I can t figure out where the names and parameters are stored  I m looking for a query that will give me the function name and the parameter types it takes  and what order it takes them in    Is there a way to do this

User · Answer

If any one is interested here is what query is executed by psql on postgres 9.1:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

You can get what psql runs for a backslash command by running psql with the -E flag.

User · Answer

Run below SQL query to create a view which will show all functions   CREATE OR REPLACE VIEW show functions AS     SELECT routine name FROM information schema routines          WHERE routine type  FUNCTION  AND specific schema  public

User · Answer

Is a good idea named the functions with commun alias on the first words for filtre the name with LIKE Example with public schema in Postgresql 9 4  be sure to replace with his scheme  SELECT routine name  FROM information schema routines  WHERE routine type  FUNCTION     AND specific schema  public    AND routine name LIKE  aliasmyfunctions

User · Answer

There s a handy function  oidvectortypes  that makes this a lot easier   SELECT format   I  I  s    ns nspname  p proname  oidvectortypes p proargtypes    FROM pg proc p INNER JOIN pg namespace ns ON  p pronamespace   ns oid  WHERE ns nspname    my namespace     Credit to Leo Hsu and Regina Obe at Postgres Online for pointing out oidvectortypes  I wrote similar functions before  but used complex nested expressions that this function gets rid of the need for   See related answer      edit in 2016   Summarizing typical report options   -- Compact  SELECT format   I  I  s    ns nspname  p proname  oidvectortypes p proargtypes    -- With result data type   SELECT format           I  I  s   s           ns nspname  p proname  oidvectortypes p proargtypes          pg get function result p oid     -- With complete argument description   SELECT format   I  I  s    ns nspname  p proname  pg get function arguments p oid    --     and mixing it   -- All with the same FROM clause  FROM pg proc p INNER JOIN pg namespace ns ON  p pronamespace   ns oid  WHERE ns nspname    my namespace     NOTICE   use p proname       p oid AS specific name  to obtain unique names  or to JOIN with information schema tables  mdash  see routines and parameters at   RuddZwolinski s answer     The function s  OID  see nbsp pg catalog pg proc  and the function s specific name  see nbsp information schema routines  are the main reference options to functions  Below  some useful functions in reporting and other contexts   --- --- --- --- --- --- Useful overloads    CREATE FUNCTION oidvectortypes p oid int  RETURNS text AS        SELECT oidvectortypes proargtypes  FROM pg proc WHERE oid  1     LANGUAGE SQL IMMUTABLE   CREATE FUNCTION oidvectortypes p specific name text  RETURNS text AS        -- Extract OID from specific name and use it in oidvectortypes oid       SELECT oidvectortypes proargtypes       FROM pg proc WHERE oid regexp replace  1                    1    int     LANGUAGE SQL IMMUTABLE   CREATE FUNCTION pg get function arguments p specific name text  RETURNS text AS        -- Extract OID from specific name and use it in pg get function arguments      SELECT pg get function arguments regexp replace  1                    1    int     LANGUAGE SQL IMMUTABLE   --- --- --- --- --- --- User customization    CREATE FUNCTION pg get function arguments2 p specific name text  RETURNS text AS        -- Example of  special layout  version      SELECT trim array agg  op   -   dt    text            FROM           SELECT data type  text as dt  ordinal position as op         FROM information schema parameters          WHERE specific name   p specific name          ORDER BY ordinal position       t    LANGUAGE SQL IMMUTABLE

User · Answer

This function returns all user defined routines in current database   SELECT pg get functiondef p oid  FROM pg proc p INNER JOIN pg namespace ns ON p pronamespace   ns oid WHERE ns nspname    public

User · Answer

After some searching  I was able to find the information schema routines table and the information schema parameters tables  Using those  one can construct a query for this purpose  LEFT JOIN  instead of JOIN  is necessary to retrieve functions without parameters   SELECT routines routine name  parameters data type  parameters ordinal position FROM information schema routines     LEFT JOIN information schema parameters ON routines specific name parameters specific name WHERE routines specific schema  my specified schema name  ORDER BY routines routine name  parameters ordinal position

User · Answer

df  lt schema gt      in psql gives the necessary information   To see the query that s used internally connect to a database with psql and supply an extra  -E   or  --echo-hidden   option and then execute the above command

User · Answer

Example    perfdb-   df information schema     List of functions         Schema               Name          Result data type   Argument data types    Type    information schema    pg char max length     integer   typid oid  typmod integer   normal  information schema    pg char octet length   integer   typid oid  typmod integer   normal  information schema    pg datetime precision  integer   typid oid  typmod integer   normal         information schema    pg numeric scale       integer   typid oid  typmod integer   normal  information schema    pg truetypid           oid       pg attribute  pg type       normal  information schema    pg truetypmod          integer   pg attribute  pg type       normal  11 rows

User · Answer

Get List of function schema and function name     SELECT     n nspname AS function schema      p proname AS function name FROM     pg proc p     LEFT JOIN pg namespace n ON p pronamespace   n oid WHERE     n nspname NOT IN   pg catalog    information schema   ORDER BY     function schema      function name

[postgresql] How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

Examples related to postgresql

Examples related to function