Обсуждение: RETURNING clause: how to specifiy column indexes?
I am attempting to implement (in a driver)(PG JDBC) support for specifying which column indexes (that generated keys) to return, so I'm searching for a way to get the server to return the values of the columns by their index, not name. By name, it is simply to append the RETURNING clause and column names to the query: INSERT... RETURNING foo,bar Does anyone know how (if) this is possible by index? A standard or server-specific syntax is fine since this is being implemented in a server-driver. Something like?: INSERT... RETURNING [1],[2] (obviously this will not work) Would I otherwise need to?: INSERT... RETURNING * then extract the user-requested columns? This seems inefficient as it returns all columns / non-key ones (blobs etc). While the values of an API that specifies the table's columns by ordinaility may seem dubious, it is an API that I think should be implemented anyway. Thanks, Ken
Ken Johanson <pg-user@kensystem.com> writes: > While the values of an API that specifies the table's columns by > ordinaility may seem dubious, it is an API that I think should be > implemented anyway. Every few weeks we get a complaint from someone who thinks that it should be easy to rearrange the logical order of table columns. If that comes to pass, it would be a seriously bad idea to have encouraged applications to rely on table column numbers. And given the number of votes for that (probably in the hundreds by now) versus the number of votes for this idea (one), I think column reordering is much more likely to get done ... regards, tom lane
On Wed, 12 Dec 2007, Tom Lane wrote: > Every few weeks we get a complaint from someone who thinks that it > should be easy to rearrange the logical order of table columns. > If that comes to pass, it would be a seriously bad idea to have > encouraged applications to rely on table column numbers. I think the expectation is that: CREATE TABLE t(a int, b int); INSERT INTO t(b,a) VALUES (1,2) RETURNING *; will return 1,2 instead of 2,1 as it does now. In this case the op is not expecting that the (potentially reorganized) table order is driving the results, but the order that they've actually specified the columns in creates the result. Kris Jurka
Kris Jurka <books@ejurka.com> writes: > I think the expectation is that: > CREATE TABLE t(a int, b int); > INSERT INTO t(b,a) VALUES (1,2) RETURNING *; > will return 1,2 instead of 2,1 as it does now. Hmm ... I see your point, but on what grounds could one argue that a "*" targetlist here should return something different from what "SELECT * FROM t" would return? I'd say that an app that wants that should write INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a; which is surely not that hard if you've got the code to produce the "(b,a)" part. In any case it's not clear this is the same thing Ken is complaining about ... regards, tom lane
Tom Lane wrote: > Kris Jurka <books@ejurka.com> writes: >> I think the expectation is that: > >> CREATE TABLE t(a int, b int); >> INSERT INTO t(b,a) VALUES (1,2) RETURNING *; > >> will return 1,2 instead of 2,1 as it does now. > > Hmm ... I see your point, but on what grounds could one argue that > a "*" targetlist here should return something different from what > "SELECT * FROM t" would return? > > I'd say that an app that wants that should write > > INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a; > > which is surely not that hard if you've got the code to produce > the "(b,a)" part. > > In any case it's not clear this is the same thing Ken is complaining > about ... > I am only seeking to have the columns returned in the order they appear naturally. JDBC says "This array contains the indexes of the columns in the target table that contain the auto-generated keys that should be made available." For the record I was not "complaining", only citing in advance the fact that while some consider selecting the keys by index to be dubious, it nonetheless must be done because an API requires it. Casting my question into a complaint is another topic.
Kris Jurka wrote: > > I think the expectation is that: > > CREATE TABLE t(a int, b int); > INSERT INTO t(b,a) VALUES (1,2) RETURNING *; > > will return 1,2 instead of 2,1 as it does now. In this case the op is > not expecting that the (potentially reorganized) table order is driving > the results, but the order that they've actually specified the columns > in creates the result. > Kris, do you have pointers to a spec that says the named-columns should specify the index, or that it should instead be the order in the table? My interpretation from the JDBC spec was that the latter is true, I may be wrong... In the case where it is table-order, then I presume in PG that the "natural" order of the columns (even if reordering is allowed at a alter date) is specified by data in one of the pg_* tables (pg_class, pg_index, etc). Does anyone know if this is true/false? If true, my next idea would be to derive the column name using a subquery in the returning clause. But it sounds like this may have potential security contraints (will any INSERT query always have read access to the PG tables?). And no guarantee of the order matching in the long term. Is there a more elegant approach, like?: INSERT... RETURNING (PG_LIST_KEYS(tblname)) I looked but did not find such a utility. It seems that such function would be best implemented in the server instead of in a driver (eg. having hardcoded subquery to the schema). Ken
On Wed, 12 Dec 2007, Ken Johanson wrote: > Kris, do you have pointers to a spec that says the named-columns should > specify the index, or that it should instead be the order in the table? My > interpretation from the JDBC spec was that the latter is true, I may be > wrong... No, I was actually misremembering what the JDBC spec said, although I think it's an interesting case to consider regardless of any specs. > In the case where it is table-order, then I presume in PG that the "natural" > order of the columns (even if reordering is allowed at a alter date) is > specified by data in one of the pg_* tables (pg_class, pg_index, etc). Does > anyone know if this is true/false? pg_attribute.attnum stores column order at the moment. If/when reordering is allowed, there will be another column indicating the logical order of the colums. > If true, my next idea would be to derive the column name using a subquery in > the returning clause. But it sounds like this may have potential security > contraints (will any INSERT query always have read access to the PG tables?). > And no guarantee of the order matching in the long term. There is no requirement that insert permission on a user table implies read access to pg_catalog. Still many clients will break if they can't read pg_catalog. For example, all of the JDBC driver's MetaData results need to query pg tables, updatable ResultSets need to query pg tables to know what the primary key is and so on. So if this functionality required access to pg_catalog that would neither be unprecedented nor unreasonable. > Is there a more elegant approach, like?: > > INSERT... RETURNING (PG_LIST_KEYS(tblname)) > You can't dynamically derive the returning clause for the same reason you can't say "INSERT INTO (SELECT myfunc()) VALUES (...)", using myfunc to determine the table name at runtime. The planner needs to know all the tables/columns/other database parts up front before executing anything. Kris Jurka
> >> If true, my next idea would be to derive the column name using a >> subquery in the returning clause. But it sounds like this may have >> potential security contraints (will any INSERT query always have read >> access to the PG tables?). And no guarantee of the order matching in >> the long term. > > There is no requirement that insert permission on a user table implies > read access to pg_catalog. Still many clients will break if they can't > read pg_catalog. For example, all of the JDBC driver's MetaData results > need to query pg tables, updatable ResultSets need to query pg tables to > know what the primary key is and so on. So if this functionality > required access to pg_catalog that would neither be unprecedented nor > unreasonable. > So it sounds like this may be the best approach, do you agree? I'll try and find the cycles to code this up although the limited value of getGeneratedKeys by index makes me think my time would be better spent elsewhere on the JDBC driver. For now at least. If you can respond to my earlier query (5 Dec) about what robustness improvements are needed, I'll start there.. Thanks, Ken
Kris and all, Here is the query I will call to the get the name of columns by ordinal position. Do you see any compatibility drivers will older server versions, or other issues? SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position Ken
On Thu, 13 Dec 2007, Ken Johanson wrote: > Here is the query I will call to the get the name of columns by ordinal > position. Do you see any compatibility drivers will older server versions, or > other issues? > > SELECT column_name > FROM information_schema.columns > WHERE table_catalog=? AND table_schema=? AND table_name=? > ORDER BY ordinal_position > Using pg_catalog tables is better than using information_schema because of the way permissions work. For information_schema you must be the table owner, while people who only have permissions to access a table will most likely be able to read pg_catalog. Kris Jurka
Kris Jurka wrote: > > Using pg_catalog tables is better than using information_schema because > of the way permissions work. For information_schema you must be the > table owner, while people who only have permissions to access a table > will most likely be able to read pg_catalog. > Do you have an equivalent query/join handy that will get the catalog and schema and table and column names frm the pg tables? SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position