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@posixnap.net
Alex J. Avriette, Professional Something-or-Other
"Premature optimization is the root of all evil! BAD PROGRAMMER! No COOKIE!!!" - Mark-Jason Dominus

Thu, 25 Dec 2003 12:59:33 -0500
"Alex J. Avriette" <alex@posixnap.net>
feature request: \qf datatype
"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

Fri, 26 Dec 2003 11:49:20 -0600
david@fetter.org (David Fetter)
Re: feature request: \qf datatype
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

Fri, 26 Dec 2003 18:34:47 -0500
"Alex J. Avriette" <alex@posixnap.net>
Re: feature request: \qf datatype
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

Fri, 26 Dec 2003 16:07:50 -0800
David Fetter <david@fetter.org>
Re: feature request: \qf datatype
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

Fri, 26 Dec 2003 21:41:45 -0500
"Alex J. Avriette" <alex@posixnap.net>
Re: feature request: \qf datatype

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@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, Pennsylvania 19073

Tue, 30 Mar 2004 11:22:12 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: feature request: \qf datatype
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

Tue, 06 Apr 2004 15:34:08 -0400
Chris Browne <cbbrowne@acm.org>
The Tomb of the Unknown Type?
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

Fri, 09 Apr 2004 01:05:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: The Tomb of the Unknown Type? 
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

Fri, 09 Apr 2004 08:07:31 -0400
Christopher Browne <cbbrowne@acm.org>
Re: The Tomb of the Unknown Type?