Re: HEAD \df doesn't show functions with no arguments

In HEAD psql, \df doesn't show functions that have no arguments:

test=> CREATE FUNCTION foo() RETURNS integer AS 'SELECT 1' LANGUAGE sql;
CREATE FUNCTION
test=> \df foo
List of functions
Schema | Name | Result data type | Argument data types
--------+------+------------------+---------------------
(0 rows)

The problem appears to be that proargtypes[0] is now NULL instead
of 0. Here's a simplified version of the \df query:

SELECT proname
FROM pg_catalog.pg_proc p
WHERE p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proname ~ '^foo$';
proname
---------
(0 rows)

SELECT proargtypes[0], proargtypes[0] IS NULL
FROM pg_proc
WHERE proname = 'foo';
proargtypes | ?column?
-------------+----------
| t
(1 row)

Here's the latter query in 8.0.2beta1:

proargtypes | ?column?
-------------+----------
0 | f
(1 row)

I'm not sure when this broke -- perhaps with the recent oidvector
changes?

http://archives.postgresql.org/pgsql-committers/2005-03/msg00423.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> The problem appears to be that proargtypes[0] is now NULL instead
> of 0. Here's a simplified version of the \df query:

> SELECT proname
> FROM pg_catalog.pg_proc p
> WHERE p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
> AND p.proname ~ '^foo$';

We could fix it by changing <> to IS DISTINCT FROM ... but I've never
been very happy with the idea that \df tries to suppress I/O functions
anyway. How do you feel about removing the cstring test altogether?

regards, tom lane

On Thu, Mar 31, 2005 at 01:06:39AM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > The problem appears to be that proargtypes[0] is now NULL instead
> > of 0. Here's a simplified version of the \df query:
>
> > SELECT proname
> > FROM pg_catalog.pg_proc p
> > WHERE p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
> > AND p.proname ~ '^foo$';
>
> We could fix it by changing <> to IS DISTINCT FROM ... but I've never
> been very happy with the idea that \df tries to suppress I/O functions
> anyway. How do you feel about removing the cstring test altogether?

Wouldn't bother me -- I'd rather see what's there and make the
"uninteresting" call myself, if that's the only reason for not
showing the I/O functions. It's not like they'd overwhelm the
output.

CREATE DATABASE foo TEMPLATE = template0;
\c foo

SELECT count(*) FROM pg_proc WHERE proargtypes[0] = 'cstring'::regtype;
count
-------
63
(1 row)

SELECT count(*) FROM pg_proc;
count
-------
1760
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Wouldn't bother me -- I'd rather see what's there and make the
> "uninteresting" call myself, if that's the only reason for not
> showing the I/O functions. It's not like they'd overwhelm the
> output.

This could work out well with my upcoming patch to have \df only
show non-system functions. Since the user will in the future have
to explicitly call \dfS to see the system functions, 60 extra
out of 1700 should not matter too much.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200503311907
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCTJESvJuQZxSWSsgRAkT+AKC7sDDWkXskD1O+ZkLpsY9U22sSuACeNR6b
jXv/PkOJBQ03j/JX5NBNkIc=
=5Pzo
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> This could work out well with my upcoming patch to have \df only
> show non-system functions. Since the user will in the future have
> to explicitly call \dfS to see the system functions, 60 extra
> out of 1700 should not matter too much.

Uh, who exactly agreed to that? I know when I do \df it's generally
to check out built-in functions not my own. I don't see this as an
improvement.

regards, tom lane

Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > The problem appears to be that proargtypes[0] is now NULL instead
> > of 0. Here's a simplified version of the \df query:
>
> > SELECT proname
> > FROM pg_catalog.pg_proc p
> > WHERE p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
> > AND p.proname ~ '^foo$';
>
> We could fix it by changing <> to IS DISTINCT FROM ... but I've never
> been very happy with the idea that \df tries to suppress I/O functions
> anyway. How do you feel about removing the cstring test altogether?

I like the cstring test. I don't think users want to see functions they
can't call from SQL, and they will ask about them if we show them. Now,
if you want \dfS to show them and \df to not show them, that is OK with
me.

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> We could fix it by changing <> to IS DISTINCT FROM ... but I've never
>> been very happy with the idea that \df tries to suppress I/O functions
>> anyway. How do you feel about removing the cstring test altogether?

> I like the cstring test. I don't think users want to see functions they
> can't call from SQL, and they will ask about them if we show them.

What makes you think you can't call 'em from SQL?

regression=# select int4in('345');
int4in
--------
345
(1 row)

regards, tom lane

> Uh, who exactly agreed to that? I know when I do \df it's generally
> to check out built-in functions not my own. I don't see this as an
> improvement.

I only ever use \df to look at my own functions...

I'd prefer if no system functions were listed :)

Chris

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> We could fix it by changing <> to IS DISTINCT FROM ... but I've never
> >> been very happy with the idea that \df tries to suppress I/O functions
> >> anyway. How do you feel about removing the cstring test altogether?
>
> > I like the cstring test. I don't think users want to see functions they
> > can't call from SQL, and they will ask about them if we show them.
>
> What makes you think you can't call 'em from SQL?
>
> regression=# select int4in('345');
> int4in
> --------
> 345
> (1 row)

Yes, I guess I mean does it make sense to call them from SQL? Their
purpose is for internal use, no?

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

I use df to see what functions are available. I want to see them all.

--elein

On Fri, Apr 01, 2005 at 12:59:43PM +0800, Christopher Kings-Lynne wrote:
> >Uh, who exactly agreed to that? I know when I do \df it's generally
> >to check out built-in functions not my own. I don't see this as an
> >improvement.
>
> I only ever use \df to look at my own functions...
>
> I'd prefer if no system functions were listed :)
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> What makes you think you can't call 'em from SQL?

> Yes, I guess I mean does it make sense to call them from SQL? Their
> purpose is for internal use, no?

People have actually used them for purposes of cross-type conversion
where there's I/O compatibility but no built-in cast. For instance
you can't
regression=# select '(1,2)'::point::text;
ERROR: cannot cast type point to text
but you can
regression=# select textin(point_out('(1,2)'::point));
textin
--------
(1,2)
(1 row)
Before you look down your nose at that, consider it's *exactly* what
plpgsql does whenever it needs to do a type conversion.

I think this decision was taken many years ago when indeed you couldn't
use the things from SQL, but it's an obsolete point of view. It's not
like the functions are typically named in a way that conflicts with
other functions. If I do "\df int4in", what exactly do you think I'm
looking for, and why should psql not show it to me?

regards, tom lane

I have applied the following attached patch to CVS to fix the \df
display problem you reported. We might remove the test it at some point
but at least now it works as in previous releases.

---------------------------------------------------------------------------

Michael Fuhr wrote:
> On Thu, Mar 31, 2005 at 01:06:39AM -0500, Tom Lane wrote:
> > Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > > The problem appears to be that proargtypes[0] is now NULL instead
> > > of 0. Here's a simplified version of the \df query:
> >
> > > SELECT proname
> > > FROM pg_catalog.pg_proc p
> > > WHERE p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
> > > AND p.proname ~ '^foo$';
> >
> > We could fix it by changing <> to IS DISTINCT FROM ... but I've never
> > been very happy with the idea that \df tries to suppress I/O functions
> > anyway. How do you feel about removing the cstring test altogether?
>
> Wouldn't bother me -- I'd rather see what's there and make the
> "uninteresting" call myself, if that's the only reason for not
> showing the I/O functions. It's not like they'd overwhelm the
> output.
>
> CREATE DATABASE foo TEMPLATE = template0;
> \c foo
>
> SELECT count(*) FROM pg_proc WHERE proargtypes[0] = 'cstring'::regtype;
> count
> -------
> 63
> (1 row)
>
> SELECT count(*) FROM pg_proc;
> count
> -------
> 1760
> (1 row)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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

> I use df to see what functions are available. I want to see them all.

But half of the postgresql "functions" are in the grammar anyway -
they're not even listed.

Chris

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> What makes you think you can't call 'em from SQL?
>
> > Yes, I guess I mean does it make sense to call them from SQL? Their
> > purpose is for internal use, no?
>
> People have actually used them for purposes of cross-type conversion
> where there's I/O compatibility but no built-in cast. For instance
> you can't
> regression=# select '(1,2)'::point::text;
> ERROR: cannot cast type point to text
> but you can
> regression=# select textin(point_out('(1,2)'::point));
> textin
> --------
> (1,2)
> (1 row)
> Before you look down your nose at that, consider it's *exactly* what
> plpgsql does whenever it needs to do a type conversion.
>
> I think this decision was taken many years ago when indeed you couldn't
> use the things from SQL, but it's an obsolete point of view. It's not
> like the functions are typically named in a way that conflicts with
> other functions. If I do "\df int4in", what exactly do you think I'm
> looking for, and why should psql not show it to me?

Interesting. I do remember them not working in SQL in the past. So it
seems they do now, and I guess we should display them.

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

Christopher Kings-Lynne wrote:
> > I use df to see what functions are available. I want to see them all.
>
> But half of the postgresql "functions" are in the grammar anyway -
> they're not even listed.

Should we look at adding stub functions into pg_proc for \df display
somehow?

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

On Fri, Apr 01, 2005 at 12:08:21AM -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> >
> > What makes you think you can't call 'em from SQL?
> >
> > regression=# select int4in('345');
> > int4in
> > --------
> > 345
> > (1 row)
>
> Yes, I guess I mean does it make sense to call them from SQL? Their
> purpose is for internal use, no?

I've used them to effect a cast where one wasn't defined; that might
not be desirable for general use, but it can be handy for quick-n-dirty.
For example, there's no standard cast from bit or varbit to text,
but you can do it with the I/O functions:

CREATE TABLE foo (b varbit, t text);
INSERT INTO foo (b) VALUES (B'10101101');
UPDATE foo SET t = textin(bit_out(b));
SELECT * FROM foo;
b | t
----------+----------
10101101 | 10101101
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Apr 01, 2005 at 12:59:43PM +0800, Christopher Kings-Lynne wrote:
>
> I only ever use \df to look at my own functions...
>
> I'd prefer if no system functions were listed :)

You can use, for example, "\df public." to see only those functions
in the public schema. That's what I do when I want to suppress the
system functions (or, more accurately, to see only the functions
in a specific schema).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

>>But half of the postgresql "functions" are in the grammar anyway -
>>they're not even listed.
>
> Should we look at adding stub functions into pg_proc for \df display
> somehow?
>

>>But half of the postgresql "functions" are in the grammar anyway -
>>they're not even listed.
>
> Should we look at adding stub functions into pg_proc for \df display
> somehow?

I'm not suggesting that at all. I was just pointing out that \df isn't
a useful view of 'what functions does postgresql have that i can use'

Chris

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> Uh, who exactly agreed to that? I know when I do \df it's generally
> to check out built-in functions not my own. I don't see this as an
> improvement.

Quoting Tom Lane:
> I thought the "S" suggestion was much better than this.
>
> Personally I am not unhappy with the existing behavior, because (unlike
> Greg I guess) I use \df and \do to look at system definitions all the
> time. However I'm willing to accept \dfS on the grounds of symmetry
> with the behavior for tables. I don't really want to put up with a less
> convenient behavior *and* a gratuitously different syntax.

http://makeashorterlink.com/?H1FC12BCA [Google groups]

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200504010705
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCTTlivJuQZxSWSsgRAn7MAJ4ntKAaoWfg02iNzGxaenwkTcQTvACgnAxM
w5eM8PTJ7G40CezTkBmS/DM=
=D7yf
-----END PGP SIGNATURE-----

On Fri, Apr 01, 2005 at 12:08:54PM -0000, Greg Sabino Mullane wrote:
> Quoting Tom Lane:
> > I thought the "S" suggestion was much better than this.
> >
> > Personally I am not unhappy with the existing behavior, because (unlike
> > Greg I guess) I use \df and \do to look at system definitions all the
> > time. However I'm willing to accept \dfS on the grounds of symmetry
> > with the behavior for tables. I don't really want to put up with a less
> > convenient behavior *and* a gratuitously different syntax.

FWIW, the pg_sysview project is doing user and all variants of system
views, ie: pg_user_tables and pg_all_tables. I also wish there was a way
to get just user functions. I like the idea of \dfS, and if you also
wanted to be able to see all functions listed together, a \dfA (for all)
doesn't seem unreasonable.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"