SHOW TABLES
in [PgSql]
|
From: Stefan Kaltenbrunner on 18 Jul 2010 15:44 On 07/18/2010 09:00 PM, Kevin Grittner wrote: > Dimitri Fontaine<dfontaine(a)hi-media.com> wrote: > >> So what we'd need first is a series of named queries, which I >> think psql provides for. > > Any solution which only works within psql isn't a solution for a > large part of the problem space people are trying to address. One > important goal is that if someone spends a day to whip up a GUI > query tool (as I did when I first started working in Java), it's > easy to get displays like we get from the psql backslash commands > (as it was in Sybase, which is what we were using at the time, > through sp_help and related stored procedures). yeah but having to call a SP is basically the same as formulating a query - the point really is that it is completely up to the client to think of a suitable representation for the information and the interface for the user to select data. Just implementing something in the server that either shows "everything" (whatever that really is in practice) will very often not match to what the tool really wants. And once we are into "providing something that can do arbitrary stuff like filtering or output manipulation" we are back to where we are - issueing an SQL-query against the catalog. > > While the four DBAs use psql heavily, the twenty-some programmers > and the business analysts all use various GUI tools which either tie > in to their normal environments (for example, eclipse) or are web > based hacks which probably didn't take much more effort than the > above-mentioned GUI hack which I used for about ten years. > Backslash commands do them no good whatsoever, nor will any solution > which requires psql. > > It would be nice if when I display information about a table or some > other database object, I could copy from my psql session, paste it > into an email, and they could replicate the behavior in squirrel (or > whatever the heck else they happen to be running). In that case you are not really using the tool per it's primary purpose (ie say a webgui that provides a graphical interpretation of something) but you are back to merely using it as an SQL-commandline client. I really doubt that there is any solution to the general problem as soon as you want filtering and related stuff - and if you only do the limited version people will soon come back and tell you it's not as flexible as was we had before (like backslash commands can do some limited filtering) or reimplementing SQL. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andres Freund on 18 Jul 2010 15:44 Hi Kevin, On Sunday 18 July 2010 21:24:25 Kevin Grittner wrote: > Stefan Kaltenbrunner <stefan(a)kaltenbrunner.cc> wrote: > > On 07/18/2010 08:58 PM, Andres Freund wrote: > >> I am quite a bit surprised about all this discussion. I have a > >> very hard time we will find anything people agree about and can > >> remember well enough to be usefull for both manual and automatic > >> processing. > >> > >> I agree that the internal pg_* tables are not exactly easy to > >> query. And that the information_schema. ones arent complete > >> enough and have enough concept mismatch to be confusing. But why > >> all this? > > > > exactly my thoughts - but as I said earlier maybe this is actually > > an opportunity to look at newsysviews again? > > I can't picture anything which could be done with views which would > allow me to issue one statement and see everything of interest about > a table (etc.). You know: tablespace, owner, permissions, columns, > primary key, foreign keys, check constraints, exclusion constraints, > ancestor tables, child tables, and whatever interesting features I > missed or we later add. Other products allow that to be generated > server-side, so that it is available to any and all clients. I > think we should join the crowd in this respect. Such tables sure do not fit queries as in On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: > SHOW ANY TABLE > GROUP BY tablename > HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; At least I dont see any way how you could define aggregation or such sensibly here. Thats the part which scares me quite a bit. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Stephen Frost on 18 Jul 2010 17:16 Kevin, * Kevin Grittner (Kevin.Grittner(a)wicourts.gov) wrote: > I can't picture anything which could be done with views which would > allow me to issue one statement and see everything of interest about > a table (etc.). You know: tablespace, owner, permissions, columns, > primary key, foreign keys, check constraints, exclusion constraints, > ancestor tables, child tables, and whatever interesting features I > missed or we later add. You think that the users of the libpq() interface (or even the protocol itself) are going to handle getting \dt-type output back somehow..? As what, a single-column result of type text? And then they'll use non-fixed-width fonts, undoubtably, which means the results will end up looking rather ugly, even if we put in the effort to format the results. I'm becoming more and more inclined to just address this with newsysviews and encouraging use of the existing TABLE top-level command for people who have issue with 'SELECT *'. > Other products allow that to be generated > server-side, so that it is available to any and all clients. I > think we should join the crowd in this respect. I could see some things being done this way, but the entire \dt output for a given table strikes me as stretching it pretty far.. And only doing it half-way doesn't strike me as a very good idea. Thanks, Stephen
From: "Greg Sabino Mullane" on 19 Jul 2010 10:12 -----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > 1. \d isn't exactly the most intuitive thing ever > Seems fairly mnemomic to me (d=describe) and it packs a *lot* of information into a single letter (see below). Things that are done often should have short keystrokes, and not require learning Yet Another Meta-Language. > And it's pretty clear that we have been heading into some > increasingly cryptic bits of fruit salad of > \dfzb+-meta-bucky-alt-foo No arguments there, but that's the nature of the beast. I don't think it's as bad as is made out, however, as \d covers 99% of everyday usage and certainly the "show tables" that started this thread. > Having SHOW THIS and SHOW THAT which are a bit more readily > guessed would be somewhat nice. I'm not sure why "easily guessed" is thrown out in this thread as such a great thing. To achieve that goal, we simply need the help system that has been proposed many times: entering in "SHOW <anything>" gives you a quick rundown of the backslash system. As far as SHOW THIS, there is a big difference from a plain "\dt" and "\d <tablename>". The former could be emulated quite easily with a SHOW command (although even our \dt prints out more information than mysql's SHOW TABLES), but the latter includes a crazy amount of information that would lead to quite a large "SHOW..." statement. Also, if it were made a server-side thing, how would you return things like indexes on a table in a SRF? Have a meta-column describing what the other columns represent? Ugly. > information_schema doesn't have some useful things that we'd like > ait to have .... > Alas, I don't see a good way to improve on this :-( newsysviews seems the way out of that particular mess. I'm also not particularly opposed to adding new views or columns to information_schema. We would still support the standard by having all the required views and columns. > The \? commands are *solely* for psql, and it would be nice to > have the Improvement work on server side so it's not only usable > with the one client. Agreed, but is there some other command-line client? If it's not command-line, free-form SQL typing, it inevitably already has support for querying the catalogs built in. At least, every GUI, app, and driver I can think of does. > I've seen too many QA scripts that do awk parsing of output of > psql "\d" commands that are vulnerable to all kinds of awfulness. They should be querying information_schema. > I'd sure like to be able to write queries that *don't* involve > array smashing or using "grep" on \z output to analyze object > permissions. Yeah, that would be a better information_schema. :) - -- Greg Sabino Mullane greg(a)turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007191011 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkxEXS0ACgkQvJuQZxSWSshLKwCffkfe0T3tELInxRqG7yCDS5Vr Ku8AoLUtOu7tTplGZZLPOEuDfKHt+EEm =Oubu -----END PGP SIGNATURE----- -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Greg Sabino Mullane" on 19 Jul 2010 10:25
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Robert Haas (robertmhaas(a)gmail.com) wrote: > I think "LIST COMMENTS ON SYSTEM AGGREGATES" would be an epic > step forward in usability. Perhaps. But it would behoove you to come up with a less er... arcane example. I've been using Postgres a long time, and I can count the number of times I've needed to see comments on system aggregates on my hand. With at least four fingers left over. .... > in the "alphabet soup" paragraph above. I don't think there's > anything WRONG with letting "\dFp" show text search dictionaries and > "\dfwS+" list system window functions with additional detail - but I'd > like an alternative that emphasizes ease of remembering over brevity, > works in every client, and can be extended in whatever reasonable ways > the community decides are worth having. .... I don't know that I'd necessarily remember all those any better, and would certainly not enjoy typing out: LIST TEST SEARCH DICTIONARIES I don't have to remember \dFp - all I have to remember is \?. For the more common ones that I use day to day and don't have to look up (\d \dt \df \l etc.) the advantage of a two or three character string is strong. (There is some devil's advocate in there - a standard cross client (and dare I say it, cross RDBMS?) way would be nice) .... > being powerful rings totally hollow for me. For ordinary, day to day > tasks like listing all my tables, or looking at the details of a > particular table, they're great. I use them all the time and would > still use them even if some other syntax were available. But there is > no reasonable way to pass options to them, and that to me is a pretty > major drawback. Well, there's the rub. You're arguing this from a hacker's persepective, while the SHOW syntax seems to be overwhelmingly agreed upon to be either helpful for clueless noobs, or some nice syntactic sugar for average users. > I'm not sure where to draw the line but implementing a proper shortcut > interface for cammands is something taht should be done on the client side > because not every client is the same and the needs of psql might be > radically different from any other client (like pgadmin or a fancy Web 2.0 > AJAX thingy - those will likely always use custom catalog queries). > Maybe a differnet way to look at the whole thing is to reconsider our own > catalogs (anyone remember newsysview?) and add a bunch of views to abstract > away most of the current complexity for these usecases? Yep, agreed. Now, if we can just agree to put information_schema in the default search_path, because nobody enjoys having to type out "information_schema"... - -- Greg Sabino Mullane greg(a)turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007191021 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkxEYDgACgkQvJuQZxSWSsikFwCdGo88Ehdcm8OHi2+VxISTG60Y b9sAoLsetxcpdMSconsCwj+3Xa1fCCzo =3aM1 -----END PGP SIGNATURE----- -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |