Attached is an updated version of my psql patch that makes the \d
backslash commands perform in an intuitive, consistent way.
Specifically, the following objects will be treated as first class
citizens (as tables and indexes currently are) by showing all the
non-system objects by default and requiring a "S" to see the system
ones.

aggregates
conversions
comments
domains
operators
functions
types

Currently, there is no way to view all the non-system functions in a
database using backslash commands, as you can with \dt, unless all of
the functions happen to be in a single schema ("\df myschema."). With
this patch, it would be as simple as "\df", and the current behavior
would be done with "\dfS".

This patch also adds a few new things to the tab-completion table, such
as comments and conversions.

Comments welcome. Last time the patch withered on the vine, despite
strong support from -general, hence I'm going to try again, as I really
want a way to view my functions without querying the pg_proc tables
directly.

--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200711042003
http://biglumber.com/x/web?pk=2529DF...9B906714964AC8



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

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

Greg Sabino Mullane wrote:
> Attached is an updated version of my psql patch that makes the \d
> backslash commands perform in an intuitive, consistent way.
> Specifically, the following objects will be treated as first class
> citizens (as tables and indexes currently are) by showing all the
> non-system objects by default and requiring a "S" to see the system
> ones.
>
> aggregates
> conversions
> comments
> domains
> operators
> functions
> types
>
> Currently, there is no way to view all the non-system functions in a
> database using backslash commands, as you can with \dt, unless all of
> the functions happen to be in a single schema ("\df myschema."). With
> this patch, it would be as simple as "\df", and the current behavior
> would be done with "\dfS".
>
> This patch also adds a few new things to the tab-completion table, such
> as comments and conversions.
>
> Comments welcome. Last time the patch withered on the vine, despite
> strong support from -general, hence I'm going to try again, as I really
> want a way to view my functions without querying the pg_proc tables
> directly.
>
> --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200711042003
> http://biglumber.com/x/web?pk=2529DF...9B906714964AC8
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Greg Sabino Mullane wrote:
> Attached is an updated version of my psql patch that makes the \d
> backslash commands perform in an intuitive, consistent way.
> Specifically, the following objects will be treated as first class
> citizens (as tables and indexes currently are) by showing all the
> non-system objects by default and requiring a "S" to see the system
> ones.
>
> aggregates
> conversions
> comments
> domains
> operators
> functions
> types
>
> Currently, there is no way to view all the non-system functions in a
> database using backslash commands, as you can with \dt, unless all of
> the functions happen to be in a single schema ("\df myschema."). With
> this patch, it would be as simple as "\df", and the current behavior
> would be done with "\dfS".

Yes, that seems like a good idea. \df in particular has been too noisy
to be usable. Not sure about conversions and domains; I doubt anyone
creates custom conversions in practice, and there's no system domains in
a standard installation.

Does anyone want to argue that there's a backward-compatibility problem
with changing \df? I don't think there is; you shouldn't be using psql
backslash commands in an application.

> This patch also adds a few new things to the tab-completion table, such
> as comments and conversions.

There's a bunch of merge conflicts in the diff.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com


--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Greg Sabino Mullane <greg@turnstep.com> writes:
> Attached is an updated version of my psql patch that makes the \d
> backslash commands perform in an intuitive, consistent way.
> Specifically, the following objects will be treated as first class
> citizens (as tables and indexes currently are) by showing all the
> non-system objects by default and requiring a "S" to see the system
> ones.

> aggregates
> conversions
> comments
> domains
> operators
> functions
> types

> Currently, there is no way to view all the non-system functions in a
> database using backslash commands, as you can with \dt, unless all of
> the functions happen to be in a single schema ("\df myschema."). With
> this patch, it would be as simple as "\df", and the current behavior
> would be done with "\dfS".

Hmm. Personally, most of my uses of \df are for the purpose of looking
for built-in functions, and so this'd be a step backwards for my usage.
Likewise for operators. Maybe I'm in the minority or maybe not.
The only one of these things for which the argument seems entirely
compelling is comments. I do understand the appeal of consistency but
sometimes it's not such a great thing.

Also, if we're going to try to enforce such a policy consistently,
shouldn't the text-search-related \d commands be changed likewise?

In any case, this patch fails to apply cleanly, and did even when it
was generated, because it contains merge conflict reports, eg

> "FROM pg_catalog.pg_proc p\n"
> + <<<<<<< describe.c
> + "JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
> + =======
> " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
> + >>>>>>> 1.160
> "WHERE p.proisagg\n",

I'm not going to risk trying to decipher the intended changes from this.


regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Hmm. Personally, most of my uses of \df are for the purpose of looking
> for built-in functions, and so this'd be a step backwards for my usage.
> Likewise for operators. Maybe I'm in the minority or maybe not.
> The only one of these things for which the argument seems entirely
> compelling is comments. I do understand the appeal of consistency but
> sometimes it's not such a great thing.

The problem is that there's absolutely no way to do the equivalent of a plain
\dt and get a list of just your user functions. That's a real headache and it
gets worse as we add more and more system functions too.

It might be cute to see if the pattern matches any user functions and if not
try again with system functions. So you would still get results if you did
"\df rtrim" for example.


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

On 31/03/2008, Gregory Stark <stark@enterprisedb.com> wrote:
> It might be cute to see if the pattern matches any user functions and if not
> try again with system functions. So you would still get results if you did
> "\df rtrim" for example.
>

Nice idea. +1 for this behaviour.

Cheers,
BJ

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Gregory Stark <stark@enterprisedb.com> writes:
> It might be cute to see if the pattern matches any user functions and if not
> try again with system functions. So you would still get results if you did
> "\df rtrim" for example.

Interesting idea. IIUC, \df would give you either all user functions
*or* all system functions depending on the actual catalog contents,
while \dfS would always give you just system functions. That means
there'd be no way to replicate the all-functions-of-both-types behavior
that has been the default in every prior release. That sounds like
a recipe for getting complaints --- changing the default behavior is
one thing, but making it so that that behavior isn't available at
all is surely going to break somebody's code or habitual usage.

How about

\dfS -> sys functions only
\dfU -> user functions only
\dfSU -> all functions (should allow \dfUS spelling too)
\df -> behavior proposed by Greg

(and similarly for all other \d commands of course). Then anyone
who's depending on the old behavior can still get it with a couple
more keystrokes.

BTW, should we remove the special hack that discriminates against
showing I/O functions (or really anything that touches cstring) in \df?
ISTM that was mostly there to reduce clutter, and this proposal solves
that problem more neatly. I know I've cursed that behavior under my
breath more than once, but again maybe my usage isn't typical.

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

On 01/04/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ... That means
> there'd be no way to replicate the all-functions-of-both-types behavior
> that has been the default in every prior release.

> \dfS -> sys functions only
> \dfU -> user functions only
> \dfSU -> all functions (should allow \dfUS spelling too)
> \df -> behavior proposed by Greg

How about \df* rather than (or in addition to) \dfSU & \dfUS?

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> It might be cute to see if the pattern matches any user functions and if not
>> try again with system functions. So you would still get results if you did
>> "\df rtrim" for example.
>
> Interesting idea. IIUC, \df would give you either all user functions
> *or* all system functions depending on the actual catalog contents,
> while \dfS would always give you just system functions. That means
> there'd be no way to replicate the all-functions-of-both-types behavior
> that has been the default in every prior release. That sounds like
> a recipe for getting complaints --- changing the default behavior is
> one thing, but making it so that that behavior isn't available at
> all is surely going to break somebody's code or habitual usage.

Actually on further thought I wonder if it wouldn't be simpler (and perhaps
more consistent with \d) to just list *all* matches iff a pattern is provided
but list only user functions if *no* pattern is provided.

That would effectively be exactly the current behaviour except that you would
have to do \dfS to get a list of system functions. And yeah, you wouldn't be
able to get a list of all functions whether system or user functions. I
suppose you could do \df *

One --perhaps nice, perhaps not-- property of this is that if you defined a
function named "rtrim" and then did "\df rtrim" it would show you _both_ the
system and user function and make it easier to see the conflict. Whereas the
other behaviour I proposed would hide the system function which might
exacerbate the user's confusion.

> BTW, should we remove the special hack that discriminates against
> showing I/O functions (or really anything that touches cstring) in \df?
> ISTM that was mostly there to reduce clutter, and this proposal solves
> that problem more neatly. I know I've cursed that behavior under my
> breath more than once, but again maybe my usage isn't typical.

.. o O Ohh! That's why I can never find them!

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Gregory Stark <stark@enterprisedb.com> writes:
> One --perhaps nice, perhaps not-- property of this is that if you defined a
> function named "rtrim" and then did "\df rtrim" it would show you _both_ the
> system and user function and make it easier to see the conflict. Whereas the
> other behaviour I proposed would hide the system function which might
> exacerbate the user's confusion.

Yeah, that is a very good point indeed.

Another way we could approach this is

\df -> all functions
\dfS -> sys functions only
\dfU -> user functions only

which avoids falling into the trap Greg mentions.

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches