Обсуждение: psql \df+ [pattern] with overloaded functions

Поиск

Список

Период

Сортировка

psql \df+ [pattern] with overloaded functions

От

"Richard Broersma"

Дата:

21 августа 2008 г., 13:19:03

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.

instrumentation=> \df update_history*
                                            List of functions
 Schema  |      Name      | Result data type |
Argument data types
---------+----------------+------------------+------------------------------------------------------------
 history | update_history | void             | new dev.analyzers, old
dev.analyzers
 history | update_history | void             | new dev.controlvalves,
old dev.controlvalves
 history | update_history | void             | new dev.dcsgraphics,
old dev.dcsgraphics
 history | update_history | void             | new dev.devicetypes,
old dev.devicetypes
 history | update_history | void             | new dev.hardsignals,
old dev.hardsignals
 history | update_history | void             | new
dev.instrumentassemblies, old dev.instrumentassemblies
 history | update_history | void             | new
dev.instrumenttypes, old dev.instrumenttypes
 history | update_history | void             | new
dev.loopcontainment, old dev.loopcontainment
 history | update_history | void             | new dev.looptimelines,
old dev.looptimelines
 history | update_history | void             | new dev.managers, old
dev.managers
 history | update_history | void             | new dev.manufactures,
old dev.manufactures
 history | update_history | void             | new dev.miscinstrument,
old dev.miscinstrument
 history | update_history | void             | new dev.nontagdevices,
old dev.nontagdevices
 history | update_history | void             | new dev.orificeplates,
old dev.orificeplates
 history | update_history | void             | new dev.partsupplies,
old dev.partsupplies
 history | update_history | void             | new dev.plcpoints, old
dev.plcpoints
 history | update_history | void             | new dev.pressuregauges,
old dev.pressuregauges
 history | update_history | void             | new dev.proctypes, old
dev.proctypes
 history | update_history | void             | new dev.projects, old
dev.projects
 history | update_history | void             | new
dev.signalingdevices, old dev.signalingdevices
 history | update_history | void             | new dev.signals, old dev.signals
 history | update_history | void             | new dev.switches, old
dev.switches
 history | update_history | void             | new dev.systems, old dev.systems
 history | update_history | void             | new dev.tagables, old
dev.tagables
 history | update_history | void             | new dev.tags, old dev.tags
 history | update_history | void             | new dev.vendors, old dev.vendors
 history | update_history | void             | new dev.vendorsupplies,
old dev.vendorsupplies
 history | update_history | void             | new dev.xmtrctrlind,
old dev.xmtrctrlind
 history | update_history | void             | new docs."p&ids", old
docs."p&ids"
 history | update_history | void             | new docs.dcsdocs, old
docs.dcsdocs
 history | update_history | void             | new docs.documentedin,
old docs.documentedin
 history | update_history | void             | new docs.documents, old
docs.documents
 history | update_history | void             | new
docs.projectdcschanges, old docs.projectdcschanges
 history | update_history | void             | new equ.equipment, old
equ.equipment
 history | update_history | void             | new equ.panels, old equ.panels
 history | update_history | void             | new equ.processunits,
old equ.processunits
(36 rows)

instrumentation=> \df+ update_history( dev.analyzers, dev.analyzers )*
ERROR:  invalid regular expression: parentheses () not balanced
instrumentation=>

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

От

Alvaro Herrera

Дата:

24 августа 2008 г., 18:22:34

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: psql \df+ [pattern] with overloaded functions

psql \df+ [pattern] with overloaded functions

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