feature request: \qf datatype - Mailing list pgsql-hackers

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 zoneabstime             | abstime                     |
timestampwithout time zoneisfinite            | boolean                     | timestamp with time zoneisfinite
 | boolean                     | timestamp without time zoneoverlaps            | boolean                     |
timestampwith time zone, interval, timestamp with time zone, intervaloverlaps            | boolean
|timestamp with time zone, interval, timestamp with time zone, timestamp with time zoneoverlaps            | boolean
                | timestamp with time zone, timestamp with time zone, timestamp with time zone, intervaloverlaps
   | boolean                     | timestamp with time zone, timestamp with time zone, timestamp with time zone,
timestampwith time zoneoverlaps            | boolean                     | timestamp without time zone, interval,
timestampwithout time zone, intervaloverlaps            | boolean                     | timestamp without time zone,
interval,timestamp without time zone, timestamp without time zoneoverlaps            | boolean                     |
timestampwithout time zone, timestamp without time zone, timestamp without time zone, intervaloverlaps            |
boolean                    | timestamp without time zone, timestamp without time zone, timestamp without time zone,
timestampwithout time zonetimestamp_eq        | boolean                     | timestamp without time zone, timestamp
withouttime zonetimestamp_ge        | boolean                     | timestamp without time zone, timestamp without time
zonetimestamp_gt       | boolean                     | timestamp without time zone, timestamp without time
zonetimestamp_le       | boolean                     | timestamp without time zone, timestamp without time
zonetimestamp_lt       | boolean                     | timestamp without time zone, timestamp without time
zonetimestamp_ne       | boolean                     | timestamp without time zone, timestamp without time
zonetimestamptz_eq     | boolean                     | timestamp with time zone, timestamp with time zonetimestamptz_ge
    | boolean                     | timestamp with time zone, timestamp with time zonetimestamptz_gt      | boolean
               | timestamp with time zone, timestamp with time zonetimestamptz_le      | boolean                     |
timestampwith time zone, timestamp with time zonetimestamptz_lt      | boolean                     | timestamp with
timezone, timestamp with time zonetimestamptz_ne      | boolean                     | timestamp with time zone,
timestampwith time zonedate                | date                        | timestamp with time zonedate
|date                        | timestamp without time zonedate_part           | double precision            | text,
timestampwith time zonedate_part           | double precision            | text, timestamp without time
zonetimestamp_cmp      | integer                     | timestamp without time zone, timestamp without time
zonetimestamptz_cmp    | integer                     | timestamp with time zone, timestamp with time zoneage
    | interval                    | timestamp with time zoneage                 | interval                    |
timestampwith time zone, timestamp with time zoneage                 | interval                    | timestamp without
timezoneage                 | interval                    | timestamp without time zone, timestamp without time
zonetimestamp_mi       | interval                    | timestamp without time zone, timestamp without time
zonetimestamptz_mi     | interval                    | timestamp with time zone, timestamp with time zonetimezone
    | interval                    | interval, timestamp with time zonetext                | text
|timestamp with time zonetext                | text                        | timestamp without time zoneto_char
   | text                        | timestamp with time zone, textto_char             | text                        |
timestampwithout time zone, texttimetz              | time with time zone         | timestamp with time zonetime
       | time without time zone      | timestamp with time zonetime                | time without time zone      |
timestampwithout time zonedate_trunc          | timestamp with time zone    | text, timestamp with time zonetimestamptz
       | timestamp with time zone    | timestamp with time zone, integertimestamptz         | timestamp with time zone
 | timestamp without time zonetimestamptz_larger  | timestamp with time zone    | timestamp with time zone, timestamp
withtime zonetimestamptz_mi_span | timestamp with time zone    | timestamp with time zone, intervaltimestamptz_pl_span
|timestamp with time zone    | timestamp with time zone, intervaltimestamptz_smaller | timestamp with time zone    |
timestampwith time zone, timestamp with time zonetimezone            | timestamp with time zone    | interval,
timestampwithout time zonetimezone            | timestamp with time zone    | text, timestamp without time
zonedate_trunc         | timestamp without time zone | text, timestamp without time zonetimestamp           | timestamp
withouttime zone | timestamp with time zonetimestamp           | timestamp without time zone | timestamp without time
zone,integertimestamp_larger    | timestamp without time zone | timestamp without time zone, timestamp without time
zonetimestamp_mi_span  | timestamp without time zone | timestamp without time zone, intervaltimestamp_pl_span   |
timestampwithout time zone | timestamp without time zone, intervaltimestamp_smaller   | timestamp without time zone |
timestampwithout time zone, timestamp without time zonetimezone            | timestamp without time zone | text,
timestampwith 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

--
alex@posixnap.net
Alex J. Avriette, Professional Something-or-Other
"Premature optimization is the root of all evil! BAD PROGRAMMER! No COOKIE!!!" - Mark-Jason Dominus