Thread: 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@fuhr.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@fuhr.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@turnstep.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@turnstep.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@fuhr.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@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
> 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
Bruce Momjian <pgman@candle.pha.pa.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
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@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
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@candle.pha.pa.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'tregression=# select '(1,2)'::point::text;ERROR: cannot cast type point to text
but you canregression=# 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 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@candle.pha.pa.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@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
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@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
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? 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@turnstep.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@decibel.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?"
>>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? >