Thread: psql \df+ [pattern] with overloaded functions
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
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