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


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?


75
2017-08-28 14:17


origen


Respuestas:


\df <schema>.*

en psql da la información necesaria.

Para ver la consulta que se usa internamente, conéctese a una base de datos con psql y proporcionar un extra "-E"(o"--echo-hidden") opción y luego ejecutar el comando anterior.


108
2017-08-28 16:27



Después de buscar, pude encontrar el information_schema.routines mesa y el information_schema.parameters mesas. 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;

55
2017-08-28 15:11



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;

Puedes obtener lo que psql se ejecuta para un comando de barra invertida ejecutando psql con el -E bandera.


22
2017-12-12 17:18



Hay una función útil, oidvectortypes, eso 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';

Crédito a Leo Hsu y Regina Obe en Postgres Online para 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';

DARSE CUENTA: utilizar p.proname||'_'||p.oid AS specific_name  para obtener nombres únicos, o para UNIRSE con information_schema tablas - ver routines y parameters en la respuesta de @ RuddZwolinski.


Las funciones OID (verpg_catalog.pg_proc) y la función nombre_específico (verinformation_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;

18
2018-06-04 10:08



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

11
2018-05-28 18:48



Es una buena idea nombrada 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 reemplazarlo con su esquema

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

6
2017-12-23 10:03



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)

3
2017-09-24 11:58