Como posso obter uma lista de todas as funções armazenadas no database de um esquema específico no PostgreSQL?

Eu quero ser capaz de se conectar a um database PostgreSQL e encontrar todas as funções para um esquema específico.

Meu pensamento era que eu poderia fazer alguma consulta para pg_catalog ou information_schema e obter uma lista de todas as funções, mas não consigo descobrir onde os nomes e parâmetros são armazenados. Eu estou procurando uma consulta que me dará o nome da function e os tipos de parâmetro necessários (e em que ordem eles são levados).

Existe uma maneira de fazer isso?

\df .* 

no psql fornece as informações necessárias.

Para ver a consulta que é usada internamente, conecte-se a um database com o psql e forneça uma opção extra ” -E ” (ou ” --echo-hidden “) e, em seguida, execute o comando acima.

Após algumas pesquisas, consegui encontrar a tabela information_schema.routines e as tabelas information_schema.parameters . Usando esses, pode-se construir uma consulta para esse propósito. LEFT JOIN, em vez de JOIN, é necessário recuperar funções sem parâmetros.

 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; 

Se alguém está interessado aqui é o que a consulta é executada pelo psql no 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; 

Você pode obter o que o psql executa para um comando de barra invertida executando o psql com o sinalizador -E .

Há uma function útil, oidvectortypes , que torna isso muito mais fácil.

 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'; 

Agradecemos a Leo Hsu e Regina Obe, do Postgres Online, por apontarem oidvectortypes . Eu escrevi funções semelhantes antes, mas usei expressões aninhadas complexas que essa function elimina a necessidade.

Veja a resposta relacionada .


(editar em 2016)

Resumindo as opções típicas de relatório:

 -- 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'; 

AVISO : use p.proname||'_'||p.oid AS specific_name para obter nomes exclusivos, ou para JOIN com information_schema tabelas information_schema – veja routines e parameters na resposta de @ RuddZwolinski.


O OID da function (consulte pg_catalog.pg_proc ) e o nome específico da function (consulte information_schema.routines ) são as principais opções de referência para as funções. Abaixo, algumas funções úteis em relatórios e outros contextos.

 --- --- --- --- --- --- 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; 

Execute abaixo da consulta SQL para criar uma visualização que mostrará todas as funções:

 CREATE OR REPLACE VIEW show_functions AS SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public'; 

É uma boa idéia nomeada as funções com alias commun nas primeiras palavras para filtre o nome com LIKE Exemplo com esquema público no Postgresql 9.4, não se esqueça de replace por seu esquema

 SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public' AND routine_name LIKE 'aliasmyfunctions%'; 

Exemplo:

 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)