Re: [GENERAL] psql \df+ [pattern] with overloaded functions

Richard Broersma escribió:
> Using psql, how can I use specify a pattern for \df+ to only show a
> single overloaded function. The following is a list of my overloaded
> functions, and following this is an example what what happens when I
> try to limit the list by including the function signature.

There's no way to do what you want, because the pattern you give to \df
is only used to constrain the name of the function, not the arguments:

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"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND p.proname ~ '^(date_par.*)$'
and p.oid::regclass = 'date_part(text,abstime)' AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

You can alter it to use the "regprocedure" to only get the function that
matches a particular signature:

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"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND p.proname ~ '^(date_par.*)$'
and p.oid::regproc = 'date_part(text,abstime)'::regprocedure -- <-- here
AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

To do what you want you need something like this (note the cast to
regprocedure and from there to text):

alvherre=# 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"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND p.proname ~ '^(date_par.*)$'
and p.oid::regprocedure::text like 'date_part(text,time%' AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;
Schema | Name | Result data type | Argument data types
------------+-----------+------------------+-----------------------------------
pg_catalog | date_part | double precision | text, timestamp without time zone
pg_catalog | date_part | double precision | text, timestamp with time zone
pg_catalog | date_part | double precision | text, time without time zone
pg_catalog | date_part | double precision | text, time with time zone
(4 filas)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general