Thread: feature request: \qf datatype

feature request: \qf datatype

From
"Alex J. Avriette"
Date:
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


Re: feature request: \qf datatype

From
david@fetter.org (David Fetter)
Date:
"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


Re: feature request: \qf datatype

From
"Alex J. Avriette"
Date:
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


Re: feature request: \qf datatype

From
David Fetter
Date:
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
 


Re: feature request: \qf datatype

From
"Alex J. Avriette"
Date:
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


Re: feature request: \qf datatype

From
Bruce Momjian
Date:
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
 


The Tomb of the Unknown Type?

From
Chris Browne
Date:
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


Re: The Tomb of the Unknown Type?

From
Tom Lane
Date:
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


Re: The Tomb of the Unknown Type?

From
Christopher Browne
Date:
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