Re: psql \df+ [pattern] with overloaded functions - Mailing list pgsql-general

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