query - postgresql functions returns

¿Cómo puedo obtener una lista de todas las funciones almacenadas en la base de datos de un esquema particular en PostgreSQL?

Question

Quiero poder conectarme a una base de datos PostgreSQL y encontrar todas las funciones para un esquema particular.

Pensé que podría hacer una consulta a pg_catalog o information_schema y obtener una lista de todas las funciones, pero no puedo averiguar dónde están almacenados los nombres y los parámetros. Estoy buscando una consulta que me proporcione el nombre de la función y los tipos de parámetros que toma (y el orden en que los recibe).

¿Hay alguna forma de hacer esto?

Answer 1

Después de algunas búsquedas, pude encontrar la tabla information_schema.routines y las tablas information_schema.parameters . Utilizándolos, uno puede construir una consulta para este propósito. LEFT JOIN, en lugar de JOIN, es necesario para recuperar funciones sin 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;

Answer 2

Ejecutar debajo de la consulta SQL para crear una vista que mostrará todas las funciones:

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

Answer 3

Ejemplo:

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)

Answer 4

Es una buena idea nombrar las funciones con alias de commun en las primeras palabras para filtre el nombre con LIKE Ejemplo con esquema público en Postgresql 9.4, asegúrese de reemplazar con su esquema

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

Answer 5

Hay una función práctica, oidvectortypes , que hace que esto sea mucho más 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'';

Gracias a Leo Hsu y Regina Obe en Postgres Online por señalar oidvectortypes . Escribí funciones similares anteriormente, pero utilicé expresiones complejas anidadas que esta función elimina de la necesidad.

Ver respuesta relacionada .

(editar en 2016)

Resumiendo las opciones típicas de informe:

-- 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 : utilice p.proname||''_''||p.oid AS specific_name para obtener nombres únicos, o UNIRSE con tablas information_schema - vea routines y parameters en @ RuddZwolinski''s answer.

El OID la función (consulte pg_catalog.pg_proc ) y el nombre específico de la función (consulte information_schema.routines ) son las principales opciones de referencia para las funciones. A continuación, algunas funciones útiles en informes y otros 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;

Answer 6

Si alguien está interesado aquí es qué consulta es ejecutada por psql en 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;

Puede obtener lo que psql ejecuta para un comando de barra psql ejecutando psql con el indicador -E .

Answer 7

/df <schema>.*

en psql proporciona la información necesaria.

Para ver la consulta que se usa internamente, conéctese a una base de datos con psql y proporcione una psql adicional " -E " (o " --echo-hidden ") y luego ejecute el comando anterior.