Re: [HACKERS] Request for qualified column names
Peter Eisentraut wrote: Dave Cramer writes: The method in question is ResultSetMetaDate.getTableName(int column) and while were at it ResultSetMetaData.getSchemaName(int column) and FWIW, the return value if not applicable is Not applicable sounds fine to me. It's like taking a file descriptor and asking what file it belongs to. That information simply doesn't exist, and if you design an application around it you lose. Yes, but in cases we can supply the info with the proper GUC variable enabled, why not do it? I realize most people don't want it, but if jdbc does, and it is something folks would use, maybe we should enable it for the easy cases. However, the number of cases where we would not be able to easily report it may make the feature useless. -- 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] Request for qualified column names
I'm certainly not trying to be difficult, I just don't know a lot about the internals of PostgreSQL. I'm developing some interfaces to various databases and certainly wanted to include PostgreSQL. From my less-than-qualified viewpoint, I would have thought including the base table name and bit pattern indicating certain features (nullability, primary index, etc) for each column in the RowDescriptor message would have been the best. Since my driver will need to support current and previous versions of PostgreSQL, my plan is to write some code to parse a SQL statement and extract the table names. (ugh!) One approach might be to add the tables's oid to the RowDescriptor message. Would not be perfect since I still would have many roundtrips to the database to get metadata, but since I don't need metadata in every case I can leave that step out until someone requests it. Reggie -Original Message- From: Neil Conway [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 11:47 PM To: Reggie Burnett Cc: 'Tom Lane'; 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' Subject: Re: [HACKERS] Request for qualified column names On Mon, 2003-01-27 at 10:44, Reggie Burnett wrote: Well, certainly the driver could parse the sql and extract what it thinks is the table name. It just seems quite foreign to me to have a database engine go through the motions of determining column location and have ready access to all the metadata for all the columns in a resultset and then intentionally leave all that out of the FE/BE. I think the issue is that no one has yet proposed a consistent set of behaviour for this feature, particularly in the cases that Tom raised. If you would like this feature, I'd suggest that you outline some behaviour that everyone can agree upon. Griping about intentionally left out features when the feature itself is not even well defined doesn't strike me as very productive. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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] Request for qualified column names
Dave Cramer says it is needed for the jdbc spec, somehow. It seems kind of odd so I don't want to make too complex an implementation. --- Peter Eisentraut wrote: Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
Could someone point me to this standard? Is that the standard for SQL syntax? I wasn't aware there was a standard for RDBMS functionality. I always assumed the features provided by the RDBMS were up to the implementers. Reggie -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 3:59 PM To: Bruce Momjian Cc: Tom Lane; Reggie Burnett; 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' Subject: Re: [HACKERS] Request for qualified column names Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
What is needed, at least from my perspective, is a way to determine proper meta data for a given column. Is it updatable? Is it nullable? Is it part of a primary key or index? Without either the base table name or attrelid,indrelid of the table, I can't get this info. Reggie -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 4:01 PM To: Peter Eisentraut Cc: Tom Lane; Reggie Burnett; 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' Subject: Re: [HACKERS] Request for qualified column names Dave Cramer says it is needed for the jdbc spec, somehow. It seems kind of odd so I don't want to make too complex an implementation. -- - Peter Eisentraut wrote: Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [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 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] Request for qualified column names
Bruce Momjian [EMAIL PROTECTED] writes: Dave Cramer says it is needed for the jdbc spec, somehow. Does the JDBC spec really require the database to provide functionality that's not in the SQL spec? I kinda doubt that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
The method in question is ResultSetMetaDate.getTableName(int column) and while were at it ResultSetMetaData.getSchemaName(int column) and FWIW, the return value if not applicable is Dave On Tue, 2003-01-28 at 17:21, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Dave Cramer says it is needed for the jdbc spec, somehow. Does the JDBC spec really require the database to provide functionality that's not in the SQL spec? I kinda doubt that. regards, tom lane -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(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] Request for qualified column names
On Mon, 2003-01-27 at 10:44, Reggie Burnett wrote: Well, certainly the driver could parse the sql and extract what it thinks is the table name. It just seems quite foreign to me to have a database engine go through the motions of determining column location and have ready access to all the metadata for all the columns in a resultset and then intentionally leave all that out of the FE/BE. I think the issue is that no one has yet proposed a consistent set of behaviour for this feature, particularly in the cases that Tom raised. If you would like this feature, I'd suggest that you outline some behaviour that everyone can agree upon. Griping about intentionally left out features when the feature itself is not even well defined doesn't strike me as very productive. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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] Request for qualified column names
Reggie Burnett [EMAIL PROTECTED] writes: When talking about expressions,views, or any other construct that could combine values from multiple tables I think it is reasonable to provide null as the table name. Any one or any process requesting the table name has to understand that not all SQL parameters have a base table name. However, in the case where a single table is involved, table and schema names should be available. That seems quite pointless. You hardly need the backend's help to determine which column belongs to which table in a single-table query. AFAICS this facility is only of interest if it does something useful in not-so-trivial cases. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
Well, certainly the driver could parse the sql and extract what it thinks is the table name. It just seems quite foreign to me to have a database engine go through the motions of determining column location and have ready access to all the metadata for all the columns in a resultset and then intentionally leave all that out of the FE/BE. Now, for us driver writers, if I have a select statement that has 20 columns I will need to extract the tablename myself (and hope I got it right) and then execute 20 separate queries to the database in order to implement any type of schema generation. I guess I don't understand this when just a few extra bytes in the RowDescriptor message would have fixed all this. Reggie -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED]] On Behalf Of Tom Lane Sent: Monday, January 27, 2003 9:21 AM To: Reggie Burnett Cc: 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' Subject: Re: [HACKERS] Request for qualified column names Reggie Burnett [EMAIL PROTECTED] writes: When talking about expressions,views, or any other construct that could combine values from multiple tables I think it is reasonable to provide null as the table name. Any one or any process requesting the table name has to understand that not all SQL parameters have a base table name. However, in the case where a single table is involved, table and schema names should be available. That seems quite pointless. You hardly need the backend's help to determine which column belongs to which table in a single-table query. AFAICS this facility is only of interest if it does something useful in not-so-trivial cases. regards, tom lane ---(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] Request for qualified column names
My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. (That's so easy, I think even I could do it.) If they over-ride it with AS, or if it is an aggregate or FROM subquery, we just return the default label as we do now --- we could return no label for those cases, but that seems too drastic. I am not overly excited about doing this at the protocol level unless there is major need for it. --- Tom Lane wrote: Reggie Burnett [EMAIL PROTECTED] writes: When talking about expressions,views, or any other construct that could combine values from multiple tables I think it is reasonable to provide null as the table name. Any one or any process requesting the table name has to understand that not all SQL parameters have a base table name. However, in the case where a single table is involved, table and schema names should be available. That seems quite pointless. You hardly need the backend's help to determine which column belongs to which table in a single-table query. AFAICS this facility is only of interest if it does something useful in not-so-trivial cases. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Request for qualified column names
--On Monday, January 27, 2003 15:49:06 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. (That's so easy, I think even I could do it.) If they over-ride it with AS, or if it is an aggregate or FROM subquery, we just return the default label as we do now --- we could return no label for those cases, but that seems too drastic. I am not overly excited about doing this at the protocol level unless there is major need for it. DONT DEFAULT TO THE NEW ONE WITHOUT NOTICE! You will ***BREAK*** people. LER - -- Tom Lane wrote: Reggie Burnett [EMAIL PROTECTED] writes: When talking about expressions,views, or any other construct that could combine values from multiple tables I think it is reasonable to provide null as the table name. Any one or any process requesting the table name has to understand that not all SQL parameters have a base table name. However, in the case where a single table is involved, table and schema names should be available. That seems quite pointless. You hardly need the backend's help to determine which column belongs to which table in a single-table query. AFAICS this facility is only of interest if it does something useful in not-so-trivial cases. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
Bruce Momjian [EMAIL PROTECTED] writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. And will you quotify things so that names containing dots, spaces, etc are unambiguous? I think the above is a very poor substitute for doing it properly, namely returning the values in separate fields. We should not allow ourselves to get lured into a dead end just because we can do it without obviously breaking the protocol. (I would argue that this breaks the protocol anyway, though.) I am not overly excited about doing this at the protocol level unless there is major need for it. I'm not excited about doing it at all, unless we do it right. We can already have half-baked solutions on the client side ;-) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Request for qualified column names
Larry Rosenman wrote: --On Monday, January 27, 2003 15:49:06 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. (That's so easy, I think even I could do it.) If they over-ride it with AS, or if it is an aggregate or FROM subquery, we just return the default label as we do now --- we could return no label for those cases, but that seems too drastic. I am not overly excited about doing this at the protocol level unless there is major need for it. DONT DEFAULT TO THE NEW ONE WITHOUT NOTICE! You will ***BREAK*** people. Of course we are not going to default this to ON. -- 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 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] Request for qualified column names
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. And will you quotify things so that names containing dots, spaces, etc are unambiguous? I think the above is a very poor substitute for doing it properly, namely returning the values in separate fields. We should not allow ourselves to get lured into a dead end just because we can do it without obviously breaking the protocol. (I would argue that this breaks the protocol anyway, though.) I don't see how it is worth modifying the client or protocol unless we have more demand for it. I would quote the labels, yes. I am not overly excited about doing this at the protocol level unless there is major need for it. I'm not excited about doing it at all, unless we do it right. We can already have half-baked solutions on the client side ;-) It is easy on the server, quite hard on the client. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Request for qualified column names
On Mon, 2003-01-27 at 15:50, Larry Rosenman wrote: --On Monday, January 27, 2003 15:49:06 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. (That's so easy, I think even I could do it.) If they over-ride it with AS, or if it is an aggregate or FROM subquery, we just return the default label as we do now --- we could return no label for those cases, but that seems too drastic. I am not overly excited about doing this at the protocol level unless there is major need for it. DONT DEFAULT TO THE NEW ONE WITHOUT NOTICE! You will ***BREAK*** people. Agreed. This is the way we probably want to go -- but we'll need a guc for a release or 2 -- One release with default as current, one with default as new way, 7.6 can remove Guc. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Request for qualified column names
Dave Cramer [EMAIL PROTECTED] writes: So for a select a, b, a+b as sum from c returns c.a, c.b, ?table?.sum This might be something to consider as part of the planned protocol overhaul. We cannot simply change the returned column names --- at least not without breaking a lot of application code. But if we return table name (and schema name too!) as separate fields of the 'T' message, and make them accessible through new PQfoo accessor functions, then no existing applications would break. But there are more than a few definitional issues to be settled before you'll convince me this idea is fully baked. Some things that come to mind immediately: What happens with views? Given create view v as select col as vcol from tab; select vcol from v; are you expecting to get back v.vcol? Or tab.col? What happens with FROM-clause aliases? Supposing tab really has a column col, what do you expect to see from select * from tab AS a(t1), tab AS b(t2) WHERE ... You could make a case for either tab.col, tab.col or a.t1, b.t2 (in the latter case, you can't realistically return a schema name). But you will probably break existing code if you do the former, since currently the output columns are labeled t1, t2. What happens with join aliases (similar issues to above)? Do you think select col as foo from tab should return tab.foo, or just foo? I'd lean to the latter; tab.foo seems awfully misleading. Or maybe you're wanting it to ignore the AS and return tab.col? Don't think that will fly. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
