Re: [PATCHES] Consistent \d commands in psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions I don't like this for two reasons: the items returned changes based on the existence of args, rather than on the command itself, and more importantly, this would make it inconsistent with the other backslash commands. One question: should \df really list *all* nonsystem functions? Or just the ones that are visible in your search path? I'd be inclined to say the second. Agreed, we should respect the search path like the other commands do. Although I wonder if a long-term idea would be to at least indicate that there are other same-named things in your path? - -- Greg Sabino Mullane [EMAIL PROTECTED] [EMAIL PROTECTED] End Point Corporation 610-983-9073 PGP Key: 0x14964AC8 200804020915 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfzh0IACgkQvJuQZxSWSsgTCgCdEv1vl7c0uIpIU3pSiH/VLvEg m5wAni5/gJ0K2w1gpaNaWpVFCRJX200f =ZnTm -END PGP SIGNATURE- -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Greg Sabino Mullane [EMAIL PROTECTED] writes: \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions I don't like this for two reasons: the items returned changes based on the existence of args, rather than on the command itself, and more importantly, this would make it inconsistent with the other backslash commands. I think you misunderstood the context of the discussion. Whatever we do will be done to the whole family of \d commands --- we are just using \df as an exemplar. 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
Re: [PATCHES] Consistent \d commands in psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/04/2008, Greg Sabino Mullane wrote: \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions I don't like this for two reasons: the items returned changes based on the existence of args, rather than on the command itself, and more importantly, this would make it inconsistent with the other backslash commands. To address your first complaint, I think it makes perfect sense to behave differently based on whether an argument has been supplied. The existence of a pattern tells you something about the user's intention in issuing the command. If they provide a pattern, they probably want to /search/ for something. If they don't provide a pattern they probably want to get a /listing/ of some set. Regards, BJ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: http://getfiregpg.org iD8DBQFH86u15YBsbHkuyV0RAmcMAKCy44zQaEcPA+QDpXr2+3vrSPucDwCgg3FG 8cq7P2DvI/ogqrHwM9Zpzx0= =IEvi -END PGP SIGNATURE- -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
The patch author has been given feedback, so this has been saved for the next commit-fest: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Greg Sabino Mullane wrote: [ There is text before PGP section. ] -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions I don't like this for two reasons: the items returned changes based on the existence of args, rather than on the command itself, and more importantly, this would make it inconsistent with the other backslash commands. One question: should \df really list *all* nonsystem functions? Or just the ones that are visible in your search path? I'd be inclined to say the second. Agreed, we should respect the search path like the other commands do. Although I wonder if a long-term idea would be to at least indicate that there are other same-named things in your path? - -- Greg Sabino Mullane [EMAIL PROTECTED] [EMAIL PROTECTED] End Point Corporation 610-983-9073 PGP Key: 0x14964AC8 200804020915 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfzh0IACgkQvJuQZxSWSsgTCgCdEv1vl7c0uIpIU3pSiH/VLvEg m5wAni5/gJ0K2w1gpaNaWpVFCRJX200f =ZnTm -END PGP SIGNATURE- -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Tom Lane [EMAIL PROTECTED] writes: Greg Sabino Mullane [EMAIL PROTECTED] writes: \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions I don't like this for two reasons: the items returned changes based on the existence of args, rather than on the command itself, and more importantly, this would make it inconsistent with the other backslash commands. I think you misunderstood the context of the discussion. Whatever we do will be done to the whole family of \d commands --- we are just using \df as an exemplar. Hm, I didn't realize that. I thought the reason \df was special was that users often need to refer to system functions. Whereas they never need to refer to system tables or system sequences etc unless they know that's what they're looking for. However, now that I look at the list of \d commands that argument kind of falls flat. Users also need to find system operators, data types, etc. And I think the same logic as \df applies for those other things too. \dt pg_class should just work. And if you create a macaddr data type it doesn't seem like too much of an imposition to play it safe and have \dT macaddr show the user that there are now two matching data types. So I guess I should just play along and pretend that's what I meant all along :) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
On Tue, Apr 01, 2008 at 12:58:44AM -0400, Tom Lane wrote: Gregory Stark [EMAIL PROTECTED] 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. Another way we could approach this is ... That doesn't satisfy the original source of the annoyance which is that \df spams your terminal with ten screens of system functions with your user functions hidden amongst them. Sure, but I think the core objection there is that there is no easy way to see only the user-defined functions. Given your point quoted first above, I'm unconvinced that should be the default behavior. When we have a bad default--and I'd argue that for anyone not developing PostgreSQL itself, showing system functions is a bad default--we should change it to something sane. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
David Fetter [EMAIL PROTECTED] writes: When we have a bad default--and I'd argue that for anyone not developing PostgreSQL itself, showing system functions is a bad default--we should change it to something sane. I disagree with your parenthetical argument here, mainly on the strength of Greg's point about how that might hide the existence of conflicts. But in any case the discussion here is first about what set of behaviors we need to provide, and only second about which one should be default. 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
Re: [PATCHES] Consistent \d commands in psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/04/2008, Tom Lane wrote: David Fetter writes: When we have a bad default--and I'd argue that for anyone not developing PostgreSQL itself, showing system functions is a bad default--we should change it to something sane. I disagree with your parenthetical argument here, mainly on the strength of Greg's point about how that might hide the existence of conflicts. But in any case the discussion here is first about what set of behaviors we need to provide, and only second about which one should be default. If I read Greg's latter proposal correctly, he was suggesting \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions This doesn't provide is all system functions only, but: 1. That list is way too long to be of much use in a psql context 2. You can still do a \df pg_catalog.* if you're really that keen. It also doesn't provide only user functions matching [pattern], but is that really a problem? I suppose you could conceive of a situation where somebody is looking for all the user funcs matching int* and getting annoyed by having to scroll past ~200 system funcs, but you can always refine your pattern, or clamp it to a particular schema. Regards, BJ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: http://getfiregpg.org iD8DBQFH8qtm5YBsbHkuyV0RAkXlAKCH8lL9H8XInLRvlbKnh84XafXyZwCg2Qom a3TuUMKHH7Yq/zZaA4MI7hk= =yLQJ -END PGP SIGNATURE- -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Brendan Jurd [EMAIL PROTECTED] writes: If I read Greg's latter proposal correctly, he was suggesting \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions Hmm, I must've misread it, because I didn't understand it quite like that. That seems like a nice simple minimal-featuritis approach. One question: should \df really list *all* nonsystem functions? Or just the ones that are visible in your search path? I'd be inclined to say the second. 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
Re: [PATCHES] Consistent \d commands in psql
Tom Lane wrote: One question: should \df really list *all* nonsystem functions? Or just the ones that are visible in your search path? I'd be inclined to say the second. +1 (although maybe that discussion belongs on -hackers, or even -general) cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Tom Lane [EMAIL PROTECTED] writes: Brendan Jurd [EMAIL PROTECTED] writes: If I read Greg's latter proposal correctly, he was suggesting \df Lists all user functions \df [pattern] Lists both system and user functions matching [pattern] \df * Lists all system and user functions Hmm, I must've misread it, because I didn't understand it quite like that. That seems like a nice simple minimal-featuritis approach. Sorry if was confusing but yes, that is what I intended by my second proposal. I prefer it to my own first proposal or any of the others. I admit I was thinking primarily of non-globbing cases for pattern. As in, I would want \df rtrim to work. I suppose it could be annoying to have to type \df public.* -- there's nothing stopping us from having \dfU and \dfS too I suppose, though I doubt most people would find them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Tom Lane [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
On 31/03/2008, Gregory Stark [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
Gregory Stark [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
On 01/04/2008, Tom Lane [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
Gregory Stark [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
Tom Lane wrote: 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. +1 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] 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. That doesn't satisfy the original source of the annoyance which is that \df spams your terminal with ten screens of system functions with your user functions hidden amongst them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] Consistent \d commands in psql
Gregory Stark [EMAIL PROTECTED] 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. Another way we could approach this is ... That doesn't satisfy the original source of the annoyance which is that \df spams your terminal with ten screens of system functions with your user functions hidden amongst them. Sure, but I think the core objection there is that there is no easy way to see only the user-defined functions. Given your point quoted first above, I'm unconvinced that should be the default behavior. 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
Re: [PATCHES] Consistent \d commands in psql
Greg Sabino Mullane [EMAIL PROTECTED] 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
Re: [PATCHES] Consistent \d commands in psql
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
Re: [PATCHES] Consistent \d commands in psql
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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200711042003 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]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
[PATCHES] Consistent \d commands in psql
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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200711042003 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 Index: doc/src/sgml/ref/psql-ref.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.196 diff -c -r1.196 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 29 Oct 2007 17:29:59 - 1.196 --- doc/src/sgml/ref/psql-ref.sgml 5 Nov 2007 00:44:49 - *** *** 874,885 varlistentry termliteral\da [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para ! Lists all available aggregate functions, together with their ! return type and the data types they operate on. If replaceable ! class=parameterpattern/replaceable is specified, only aggregates whose names match the pattern are shown. /para /listitem --- 874,887 varlistentry termliteral\da [ replaceable class=parameterpattern/replaceable ]/literal/term + termliteral\daS [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para ! Lists all available aggregate functions, together with the data ! types they operate on. The letter literalS/literal restricts ! the listing to system aggregates; without literalS/literal, ! only non-system aggregates are shown. If replaceable class=parameterpattern/replaceable is specified, only aggregates whose names match the pattern are shown. /para /listitem *** *** 904,912 --- 906,917 varlistentry termliteral\dc [ replaceable class=parameterpattern/replaceable ]/literal/term + termliteral\dcS [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para Lists all available conversions between character-set encodings. + The letter literalS/literal restricts the listing to system conversions; + without literalS/literal, only non-system conversions are shown. If replaceable class=parameterpattern/replaceable is specified, only conversions whose names match the pattern are listed. *** *** 927,943 varlistentry termliteral\dd [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para Shows the descriptions of objects matching the replaceable class=parameterpattern/replaceable, or of all visible objects if no argument is given. But in either case, only objects that have ! a description are listed. (quoteObject/quote covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences, large objects), rules, and triggers.) For example: programlisting ! =gt; userinput\dd version/userinput Object descriptions Schema | Name | Object |Description +-+--+--- --- 932,951 varlistentry termliteral\dd [ replaceable class=parameterpattern/replaceable ]/literal/term + termliteral\ddS [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para Shows the descriptions of objects matching the replaceable class=parameterpattern/replaceable, or of all visible objects if no argument is given. But in either case, only objects that have ! a description are listed. The letter literalS/literal restricts ! the listing to system objects; without literalS/literal, ! only non-system objects are shown. (quoteObject/quote covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences,