Thread: feature request: \qf datatype
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
"Alex J. Avriette" <alex@posixnap.net> wrote: > 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 know this is just syntactical nit-picking, but I'm wondering where this functionality belongs. A few possibilities: * Part of \dT (describing data types) * Part of \df (for functions) * A new \ command * Non-empty subsets of the above * And, of course, the all-important Stuff I Haven't Thought Of. Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Just my $0.02 :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 cell: +1 415 235 3778 Who is wise? He who learns from all. Ben Zoma, Pirkei Avot 4:1
On Fri, Dec 26, 2003 at 11:49:20AM -0600, David Fetter wrote: > > 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. > * Part of \df (for functions) This is my initial feeling. It really is "just another \df". However, I don't see a good way to merge the functionality of the new function and the old function, as the parameter for df is the function name, not its arguments. > * Part of \dT (describing data types) This, too would work, but again, I have a hard time figuring out where to put the arguments. > * A new \ command The problem with this is that few people are going to notice it immediately whereas I would contend that many people already know aobut \df and \dT. The goal here is to get people to use the tools they have. If they're not already seeking out tools, it doesn't help to add new ones. > Duplication--especially for help systems--is not a bad thing, as long > as it's only duplication of access and not of code bases. Duplication of help systems that are never going to be used is a waste of everyone's time. Alex -- alex@posixnap.net Alex J. Avriette, Unix Systems Gladiator "Shut down some of the bullshit the government is spending money on and use it to buy all the Microsoft stock. If we did that, we could ... just bronze Gates, turn him into a statue, and stick him in front of the Commerce Department." - Scott McNealy
On Fri, Dec 26, 2003 at 06:34:47PM -0500, Alex J. Avriette wrote: > On Fri, Dec 26, 2003 at 11:49:20AM -0600, David Fetter wrote: > > > 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. > > * Part of \df (for functions) > This is my initial feeling. It really is "just another \df". > However, I don't see a good way to merge the functionality of the > new function and the old function, as the parameter for df is the > function name, not its arguments. True. > > * Part of \dT (describing data types) > This, too would work, but again, I have a hard time figuring out > where to put the arguments. Weelll, there's already a + operator, as in \df+. Perhaps there could be a T operator for data types and an f operator for functions, &c. \dfT integer might bring back all the functions that operate on (or return) integers. > > * A new \ command > > The problem with this is that few people are going to notice it > immediately whereas I would contend that many people already know > aobut \df and \dT. The goal here is to get people to use the tools > they have. If they're not already seeking out tools, it doesn't help > to add new ones. > > > Duplication--especially for help systems--is not a bad thing, as > > long as it's only duplication of access and not of code bases. > > Duplication of help systems that are never going to be used is a > waste of everyone's time. Perhaps I didn't make clear what I was trying to say. :) We should continue to avoid the "The docs for any given thing are findable in exactly one way. If you don't divine it, you are S.O.L." model of documentation. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 cell: +1 415 235 3778 There is nothing more difficult to take in hand, more perilous to conduct, or more uncertain in its success, than to take the lead in the introduction of a new order of things. Because the innovator has for enemies all those who have done well under the old conditions, and lukewarm defenders in those who may do well under the new. Niccolo Machiavelli The Prince, 1513
On Fri, Dec 26, 2003 at 04:07:50PM -0800, David Fetter wrote: > \dfT integer > > might bring back all the functions that operate on (or return) > integers. I like this the best so far. My only concern is that there is a whole lot of output generated by this (see thread parent). > > > Duplication--especially for help systems--is not a bad thing, as > > > long as it's only duplication of access and not of code bases. > > > > Duplication of help systems that are never going to be used is a > > waste of everyone's time. > > Perhaps I didn't make clear what I was trying to say. :) > > We should continue to avoid the "The docs for any given thing are > findable in exactly one way. If you don't divine it, you are S.O.L." > model of documentation. Well, I'm perfectly happy to see it in psql. I'm just unable to actually do the C part myself. If somebody would step up to the plate, we could see this in the next release. I haven't heard anyone say they didn't like it. alex -- alex@posixnap.net Alex J. Avriette, Windows Systems Defenestrator "Object-oriented programming is an exceptionally bad idea which could only have originated in California." - Edsger Dijkstra
I added a mention of how to use the pager to lookup datatype mentions in
psql \df:
To look up functions taking argument or returning values of a specific type, use your pager's search
capabilityto scroll through the \df output.
No one could come up with a good API to make this easier, so I think
mentioning a simple solution is best.
---------------------------------------------------------------------------
Alex J. Avriette wrote:
> On Fri, Dec 26, 2003 at 04:07:50PM -0800, David Fetter wrote:
>
> > \dfT integer
> >
> > might bring back all the functions that operate on (or return)
> > integers.
>
> I like this the best so far. My only concern is that there is a
> whole lot of output generated by this (see thread parent).
>
> > > > Duplication--especially for help systems--is not a bad thing, as
> > > > long as it's only duplication of access and not of code bases.
> > >
> > > Duplication of help systems that are never going to be used is a
> > > waste of everyone's time.
> >
> > Perhaps I didn't make clear what I was trying to say. :)
> >
> > We should continue to avoid the "The docs for any given thing are
> > findable in exactly one way. If you don't divine it, you are S.O.L."
> > model of documentation.
>
> Well, I'm perfectly happy to see it in psql. I'm just unable to
> actually do the C part myself. If somebody would step up to the plate,
> we could see this in the next release. I haven't heard anyone say they
> didn't like it.
>
> alex
>
> --
> alex@posixnap.net
> Alex J. Avriette, Windows Systems Defenestrator
> "Object-oriented programming is an exceptionally bad idea which could only have originated in California." - Edsger
Dijkstra
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
We have encountered a pretty oddball situation involving an "unknown" type.
mydb=# select version(); version
----------------------------------------------------------------------------------------------------------PostgreSQL
7.4.2on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-24)
(1 row)
mydb=# \d redact_current24248 Table "public.redact_current24248" Column | Type | Modifiers
-------------------------+-----------+-----------n_posted_transaction_id | integer | n_year_u |
"unknown"| n_month_u | "unknown" | n_breakdown_config_id | integer | n_amount |
numeric |
We'd like to turn those "unknown" values into plain integers (e.g. -
years and months); apparently it's not so simple...
mydb=# select n_year_u::integer, n_month_u::integer from redact_current24248 limit 10;
ERROR: failed to find conversion function from "unknown" to integer
How this was generated was with Perl code where the prepared query
looks something like the following:
"CREATE TEMP TABLE $tableName AS ".
"SELECT a.id as n_posted_transaction_id, ". "? as n_year_u, ". "? as n_month_u, ". "c.id as
n_breakdown_config_id,". "calc_revenue( various_parameters ) as n_amount ".
"FROM ". "transactions_posted a, ". "items b, ". "transaction_breakdown_config c; ";
I wasn't aware of there being an "unknown" type, and it's rather
bizarre that this is happening.
I imagine that specifying
"SELECT a.id as n_posted_transaction_id, ". "?::integer as n_year_u, ". "?::integer as n_month_u, ".
would likely clear this up, but where "unknown" came from is something
of a mystery. The source types shouldn't be any mystery.
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/x.html
str->str_pok |= SP_FBM; /* deep magic */
s = (unsigned char*)(str->str_ptr); /* deeper magic */
-- Larry Wall in util.c from the perl source code
Chris Browne <cbbrowne@acm.org> writes:
> We have encountered a pretty oddball situation involving an "unknown" type.
The way you get this sort of thing is with
CREATE VIEW foo AS SELECT ... , 'literal', ...
The undecorated literal is initially of type UNKNOWN, and there's
nothing to cause it to get coerced to some more-specific type, so
UNKNOWN ends up actually showing in the view's column type. The
CREATE command will bleat ineffectually about this, but create the
view anyway.
The cure is to cast the literal to some specific type when you
do the CREATE.
One could perhaps argue that we should default to assuming that TEXT
type was meant, as we do in some similar cases such as UNION. But
it's not done at the moment.
regards, tom lane
tgl@sss.pgh.pa.us (Tom Lane) wrote:
> The cure is to cast the literal to some specific type when you
> do the CREATE.
I figured that was the case, and pointed that cure back to the
developer.
What I was hoping to hear was some way of coercing the data into a
more usable type after the fact. The query is actually generating a
TEMP table, so the cast is certainly the right answer.
--
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/emacs.html
"In the case of CAPP, an EAL4 evaluation tells you everything you need
to know. It tells you that Microsoft spent millions of dollars
producing documentation that shows that Windows 2000 meets an
inadequate set of requirements, and that you can have reasonably
strong confidence that this is the case." -- Jonathan S. Shapiro