Re: [HACKERS] \d type queries - why not views in system catalog?!?
-Original Message- From: Robert Treat [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 22:01 To: Greg Copeland Cc: Dave Page; [EMAIL PROTECTED]; PostgresSQLHackers Mailing List Subject: Re: [HACKERS] \d type queries - why not views in system catalog?!? You have to do it in functions because some of the \ commands use multiple queries and logic inside the C code. pgAdmin lists far more comprehensive data the psql and does it from single queries 95% of the time. I'm sure someone more expert in SQL than me could get pretty much all the other 5% sorted... Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] \d type queries - why not views in system catalog?!?
On Tue, 2003-01-14 at 01:39, Christopher Kings-Lynne wrote: What about querying the information_schema? Will information_schema be strictly SQL99 or will it also have PostgreSQL specific views/fields ? -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Hannu Krosing [EMAIL PROTECTED] writes: Will information_schema be strictly SQL99 or will it also have PostgreSQL specific views/fields ? If it's not strictly conformant to the spec, I see no value in it at all. We already have plenty of ability to query the catalogs via non-standard queries. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] \d type queries - why not views in system catalog?!?
On Tue, 2003-01-14 at 15:47, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Will information_schema be strictly SQL99 or will it also have PostgreSQL specific views/fields ? If it's not strictly conformant to the spec, I see no value in it at all. I mean that it could have at least extra *views* for postgresql specific things. It could also have extra *fields* on standard views, but that might break some apps. I see no way how having extra views can break apps. We already have plenty of ability to query the catalogs via non-standard queries. But would it not be nice to have some standard ones, so that each and every DB management app does not need to invent its own ? I agree that this could be done as a project at gborg rather than in information_schema, but it would still be good to have one standard place for examples at least. And the only way to keep the examples up-to-date is by using them in live projects. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] \d type queries - why not views in system catalog?!?
Hi! I just came across a posting in this list, and a question arose from that which I'm carrying for some time. PG has *some* views in the system catalog, which make life easier, but some essential(?) things like 'list all tables in DB' has to be done in a multi-table join with special attributes. What is the rationale of that? Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Do I miss a point here? I'd be even willing to do some work here, if considered worthwhile... Greetings, Joerg -- Leading SW developer - S.E.A GmbH Mail: [EMAIL PROTECTED] WWW: http://www.sea-gmbh.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Joerg Hessdoerfer [EMAIL PROTECTED] writes: PG has *some* views in the system catalog, which make life easier, but some essential(?) things like 'list all tables in DB' has to be done in a multi-table join with special attributes. What is the rationale of that? Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Only to the extent that the views match what a particular front-end actually wants to see. Peter Eisentraut is currently working on adding the SQL-spec-mandated INFORMATION_SCHEMA views; so as long as all you want to know is what's in the spec, those should be your answer. But I do not foresee psql or pg_dump ever switching over to INFORMATION_SCHEMA, because they want to know about some things that are Postgres-specific. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] \d type queries - why not views in system catalog?!?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Do I miss a point here? Putting the \d commands into views has been on the TODO list for a long time: I think it is actually the only psql-related item left, until we change the backend protocol to indicate transaction state. I don't think a view would have helped with the psql 7.2/7.3 change: a lot more changed than simply the underlying SQL. Some of the the backslash commands are not amenable to putting inside a view, as they actually compromise multiple SQL calls and some logic in the C code, but a few could probably be made into views. Could whomever added that particular TODO item expand on this? -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200301131137 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+IuuovJuQZxSWSsgRAgQXAKCdu0+CelZ1V2bwI/HoJHIz+a3DPACgix7u pOcRXwHb+4NJLMeSpNaqzRM= =0yFo -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] \d type queries - why not views in system catalog?!?
On Mon, 2003-01-13 at 11:28, [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Do I miss a point here? Putting the \d commands into views has been on the TODO list for a long time: I think it is actually the only psql-related item left, until we change the backend protocol to indicate transaction state. I don't think a view would have helped with the psql 7.2/7.3 change: a lot more changed than simply the underlying SQL. It would be a wise idea to use the INFORMATION_SCHEMA where possible for these, as that is pretty much guaranteed to be static in format. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] \d type queries - why not views in system catalog?!?
On Mon, 2003-01-13 at 11:28, [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Do I miss a point here? Putting the \d commands into views has been on the TODO list for a long time: I think it is actually the only psql-related item left, until we change the backend protocol to indicate transaction state. I don't think a view would have helped with the psql 7.2/7.3 change: a lot more changed than simply the underlying SQL. Some of the the backslash commands are not amenable to putting inside a view, as they actually compromise multiple SQL calls and some logic in the C code, but a few could probably be made into views. Could whomever added that particular TODO item expand on this? One idea I've always thought would be nice would be to make full fledged C functions out of the \ commands and ship them with the database. This way the \ commands could just be alias to select myfunc(). This would help out all of us who write GUI interfaces since we would have standard functions we could call upon, and would also help with backward compatibility since \dv could always call select list_views(), which would already be included with each server. One of the reasons that this was not feasible in the past was that we needed functions that could return multiple rows and columns easily. Now that we have that in 7.3, it might be worth revisiting. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Oh! That's an excellent idea. Seemingly addresses the issue and has value-add. I'm not aware of any gotchas here. Is there something that is being overlooked? Greg On Mon, 2003-01-13 at 14:50, Robert Treat wrote: On Mon, 2003-01-13 at 11:28, [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Wouldn't it be easier (and more portable, see 7.3/7.2 system catalogs vs. psql) to have views for that? Do I miss a point here? Putting the \d commands into views has been on the TODO list for a long time: I think it is actually the only psql-related item left, until we change the backend protocol to indicate transaction state. I don't think a view would have helped with the psql 7.2/7.3 change: a lot more changed than simply the underlying SQL. Some of the the backslash commands are not amenable to putting inside a view, as they actually compromise multiple SQL calls and some logic in the C code, but a few could probably be made into views. Could whomever added that particular TODO item expand on this? One idea I've always thought would be nice would be to make full fledged C functions out of the \ commands and ship them with the database. This way the \ commands could just be alias to select myfunc(). This would help out all of us who write GUI interfaces since we would have standard functions we could call upon, and would also help with backward compatibility since \dv could always call select list_views(), which would already be included with each server. One of the reasons that this was not feasible in the past was that we needed functions that could return multiple rows and columns easily. Now that we have that in 7.3, it might be worth revisiting. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] \d type queries - why not views in system catalog?!?
-Original Message- From: Greg Copeland [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 20:56 To: Robert Treat Cc: [EMAIL PROTECTED]; PostgresSQL Hackers Mailing List Subject: Re: [HACKERS] \d type queries - why not views in system catalog?!? Oh! That's an excellent idea. Seemingly addresses the issue and has value-add. I'm not aware of any gotchas here. Is there something that is being overlooked? Why use functions instead of views? Most UIs will want to format the output as they see fit so a recordset would be the appropriate output. Yes, a function could do this, but surely views would be simpler to implement and maintain. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Views or C-functions, I think the idea is excellent. It's the concept that I really like. Greg On Mon, 2003-01-13 at 15:00, Dave Page wrote: -Original Message- From: Greg Copeland [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 20:56 To: Robert Treat Cc: [EMAIL PROTECTED]; PostgresSQL Hackers Mailing List Subject: Re: [HACKERS] \d type queries - why not views in system catalog?!? Oh! That's an excellent idea. Seemingly addresses the issue and has value-add. I'm not aware of any gotchas here. Is there something that is being overlooked? Why use functions instead of views? Most UIs will want to format the output as they see fit so a recordset would be the appropriate output. Yes, a function could do this, but surely views would be simpler to implement and maintain. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] \d type queries - why not views in system catalog?!?
You have to do it in functions because some of the \ commands use multiple queries and logic inside the C code. Robert Treat On Mon, 2003-01-13 at 16:42, Greg Copeland wrote: Views or C-functions, I think the idea is excellent. It's the concept that I really like. Greg On Mon, 2003-01-13 at 15:00, Dave Page wrote: -Original Message- From: Greg Copeland [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 20:56 To: Robert Treat Cc: [EMAIL PROTECTED]; PostgresSQL Hackers Mailing List Subject: Re: [HACKERS] \d type queries - why not views in system catalog?!? Oh! That's an excellent idea. Seemingly addresses the issue and has value-add. I'm not aware of any gotchas here. Is there something that is being overlooked? Why use functions instead of views? Most UIs will want to format the output as they see fit so a recordset would be the appropriate output. Yes, a function could do this, but surely views would be simpler to implement and maintain. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Robert Treat kirjutas T, 14.01.2003 kell 01:50: One of the reasons that this was not feasible in the past was that we needed functions that could return multiple rows and columns easily. Now that we have that in 7.3, it might be worth revisiting. Also, we have schemas now, so it would be easier to avoid name clashes. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Robert Treat writes: One idea I've always thought would be nice would be to make full fledged C functions out of the \ commands and ship them with the database. The psql meta-commands are not a nicely designed set of queries that one would encapsulate into a public library interface. They are created for interactive use, representing precomposed views of the database that are thought to be useful. If the ideas of usefulness change, then the commands might change. If you want to create a set of views or functions for noninteractive use by client applications, then you need to step back and create maximally decomposed views of the database that can be combined in all possible ways. The SQL information schema is such a set, but the information is perhaps too limited for some applications. (But I urge you too look first.) But any other set would have to be designed on similar principles. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Peter Eisentraut [EMAIL PROTECTED] writes: Robert Treat writes: One idea I've always thought would be nice would be to make full fledged C functions out of the \ commands and ship them with the database. The psql meta-commands are not a nicely designed set of queries that one would encapsulate into a public library interface. They are created for interactive use, representing precomposed views of the database that are thought to be useful. If the ideas of usefulness change, then the commands might change. I think that the proposal is to take describe.c more or less lock-stock-and-barrel out of psql and put it in the backend instead. It doesn't matter whether the views are orthogonal or useful for non-interactive purposes; they're defined to do whatever we think psql should show. The question is whether this gives us a useful amount of decoupling of psql from the backend version. Certainly the describe.c code is the stuff most subject to breakage across versions, but there are a lot of other aspects of psql that could still break. One fairly obvious example of backend-dependent psql code that won't be helped this way is the tab completion code. While I don't have any strong objection to moving the guts of these queries to the backend, I can't get real excited about it either; I suspect that psql will still be pretty version-dependent. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] \d type queries - why not views in system catalog?!?
What should we do with the TODO item? Add question mark? Remove? --- Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Robert Treat writes: One idea I've always thought would be nice would be to make full fledged C functions out of the \ commands and ship them with the database. The psql meta-commands are not a nicely designed set of queries that one would encapsulate into a public library interface. They are created for interactive use, representing precomposed views of the database that are thought to be useful. If the ideas of usefulness change, then the commands might change. I think that the proposal is to take describe.c more or less lock-stock-and-barrel out of psql and put it in the backend instead. It doesn't matter whether the views are orthogonal or useful for non-interactive purposes; they're defined to do whatever we think psql should show. The question is whether this gives us a useful amount of decoupling of psql from the backend version. Certainly the describe.c code is the stuff most subject to breakage across versions, but there are a lot of other aspects of psql that could still break. One fairly obvious example of backend-dependent psql code that won't be helped this way is the tab completion code. While I don't have any strong objection to moving the guts of these queries to the backend, I can't get real excited about it either; I suspect that psql will still be pretty version-dependent. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] \d type queries - why not views in system catalog?!?
What about querying the information_schema? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Robert Treat Sent: Tuesday, 14 January 2003 6:01 AM To: Greg Copeland Cc: Dave Page; [EMAIL PROTECTED]; PostgresSQL Hackers Mailing List Subject: Re: [HACKERS] \d type queries - why not views in system catalog?!? You have to do it in functions because some of the \ commands use multiple queries and logic inside the C code. Robert Treat On Mon, 2003-01-13 at 16:42, Greg Copeland wrote: Views or C-functions, I think the idea is excellent. It's the concept that I really like. Greg On Mon, 2003-01-13 at 15:00, Dave Page wrote: -Original Message- From: Greg Copeland [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 20:56 To: Robert Treat Cc: [EMAIL PROTECTED]; PostgresSQL Hackers Mailing List Subject: Re: [HACKERS] \d type queries - why not views in system catalog?!? Oh! That's an excellent idea. Seemingly addresses the issue and has value-add. I'm not aware of any gotchas here. Is there something that is being overlooked? Why use functions instead of views? Most UIs will want to format the output as they see fit so a recordset would be the appropriate output. Yes, a function could do this, but surely views would be simpler to implement and maintain. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Greg Copeland [EMAIL PROTECTED] Copeland Computer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
