I'd like to request the following feature:
Frequently when answering questions on IRC for people, questions fall
into one of two categories, "what function can I use to manipulate
datatype xyz," and "what datatype can i use for xyz."
The latter is harder to answer than the former. For the former, I
propose a macro in psql, "\qf" (query function). Obviously, the name
implies a broader scope than simply querying the datatypes
permissable.
I foresee something like this (sorry, this has a lot of output):
dbms=> \qf timestamp
Name | Result data type |
Argument data types
---------------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------
abstime | abstime | timestamp with time zone
abstime | abstime | timestamp without time zone
isfinite | boolean | timestamp with time zone
isfinite | boolean | timestamp without time zone
overlaps | boolean | timestamp with time zone,
interval, timestamp with time zone, interval
overlaps | boolean | timestamp with time zone,
interval, timestamp with time zone, timestamp with time zone
overlaps | boolean | timestamp with time zone,
timestamp with time zone, timestamp with time zone, interval
overlaps | boolean | timestamp with time zone,
timestamp with time zone, timestamp with time zone, timestamp with time zone
overlaps | boolean | timestamp without time zone,
interval, timestamp without time zone, interval
overlaps | boolean | timestamp without time zone,
interval, timestamp without time zone, timestamp without time zone
overlaps | boolean | timestamp without time zone,
timestamp without time zone, timestamp without time zone, interval
overlaps | boolean | timestamp without time zone,
timestamp without time zone, timestamp without time zone, timestamp without time zone
timestamp_eq | boolean | timestamp without time zone,
timestamp without time zone
timestamp_ge | boolean | timestamp without time zone,
timestamp without time zone
timestamp_gt | boolean | timestamp without time zone,
timestamp without time zone
timestamp_le | boolean | timestamp without time zone,
timestamp without time zone
timestamp_lt | boolean | timestamp without time zone,
timestamp without time zone
timestamp_ne | boolean | timestamp without time zone,
timestamp without time zone
timestamptz_eq | boolean | timestamp with time zone,
timestamp with time zone
timestamptz_ge | boolean | timestamp with time zone,
timestamp with time zone
timestamptz_gt | boolean | timestamp with time zone,
timestamp with time zone
timestamptz_le | boolean | timestamp with time zone,
timestamp with time zone
timestamptz_lt | boolean | timestamp with time zone,
timestamp with time zone
timestamptz_ne | boolean | timestamp with time zone,
timestamp with time zone
date | date | timestamp with time zone
date | date | timestamp without time zone
date_part | double precision | text, timestamp with time zone
date_part | double precision | text, timestamp without time zone
timestamp_cmp | integer | timestamp without time zone,
timestamp without time zone
timestamptz_cmp | integer | timestamp with time zone,
timestamp with time zone
age | interval | timestamp with time zone
age | interval | timestamp with time zone,
timestamp with time zone
age | interval | timestamp without time zone
age | interval | timestamp without time zone,
timestamp without time zone
timestamp_mi | interval | timestamp without time zone,
timestamp without time zone
timestamptz_mi | interval | timestamp with time zone,
timestamp with time zone
timezone | interval | interval, timestamp with time zone
text | text | timestamp with time zone
text | text | timestamp without time zone
to_char | text | timestamp with time zone, text
to_char | text | timestamp without time zone, text
timetz | time with time zone | timestamp with time zone
time | time without time zone | timestamp with time zone
time | time without time zone | timestamp without time zone
date_trunc | timestamp with time zone | text, timestamp with time zone
timestamptz | timestamp with time zone | timestamp with time zone, integer
timestamptz | timestamp with time zone | timestamp without time zone
timestamptz_larger | timestamp with time zone | timestamp with time zone,
timestamp with time zone
timestamptz_mi_span | timestamp with time zone | timestamp with time zone, interval
timestamptz_pl_span | timestamp with time zone | timestamp with time zone, interval
timestamptz_smaller | timestamp with time zone | timestamp with time zone,
timestamp with time zone
timezone | timestamp with time zone | interval, timestamp without time
zone
timezone | timestamp with time zone | text, timestamp without time zone
date_trunc | timestamp without time zone | text, timestamp without time zone
timestamp | timestamp without time zone | timestamp with time zone
timestamp | timestamp without time zone | timestamp without time zone,
integer
timestamp_larger | timestamp without time zone | timestamp without time zone,
timestamp without time zone
timestamp_mi_span | timestamp without time zone | timestamp without time zone,
interval
timestamp_pl_span | timestamp without time zone | timestamp without time zone,
interval
timestamp_smaller | timestamp without time zone | timestamp without time zone,
timestamp without time zone
timezone | timestamp without time zone | text, timestamp with time zone
(61 rows)
The sql required to generate that is as follows:
SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||
p.proname as "Name",
pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
pg_catalog.oidvectortypes(p.proargtypes) 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] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_function_is_visible(p.oid)
AND pg_catalog.oidvectortypes(p.proargtypes) ~ 'timestamp'
ORDER BY 2, 1, 3;
I looked in src/bin/psql/describe.c, and even found the \df macro.
However, the C stuff was beyond my ability. Hopefully, this is a direct
"clone \df" item. I really think this would be useful for people who
haven't yet becomes familiar with postgres' (very rich) function base.
Hm. On second thought, \qf is a bad name for it, as \q is quit, and 'f'
is an unexpected "extra argument." Perhaps \dfq?
Thanks,
alex
--
[EMAIL PROTECTED]
Alex J. Avriette, Professional Something-or-Other
"Premature optimization is the root of all evil! BAD PROGRAMMER! No COOKIE!!!" -
Mark-Jason Dominus
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
