Re: 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 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

--
alex(at)posixnap(dot)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(at)posixnap(dot)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(at)fetter(dot)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(at)posixnap(dot)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(at)fetter(dot)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(at)posixnap(dot)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 capability to 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(at)posixnap(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

We have encountered a pretty oddball situation involving an "unknown" type.

mydb=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.2 on 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(at)acm(dot)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(at)sss(dot)pgh(dot)pa(dot)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