Re: Autogenerated keys and ... RETURNING
От | Dave Cramer |
---|---|
Тема | Re: Autogenerated keys and ... RETURNING |
Дата | |
Msg-id | 1B43C219-F4B1-4A62-A7AE-0A941267807C@fastcrypt.com обсуждение исходный текст |
Ответ на | Re: Autogenerated keys and ... RETURNING (Michael Paesold <mpaesold@gmx.at>) |
Список | pgsql-jdbc |
On 4-Oct-06, at 2:15 AM, Michael Paesold wrote: > Dave Cramer wrote: >> No, nobody is working on it, AFAIK. The question I have is how do >> you know which column to return ? > > Well, that is something I wanted to discuss here. I understand that > with the current backend support, we can't get a perfect > implementation. But I think we can support most cases, perhaps not > with the best possible performance. > > For auto-detecting which columns to return, we would really need to > parse the query first, which is not something that could be done > easily. On the other hand, at least the javadocs for executeUpdate > in JDK 1.4.2 say "The driver will ignore the array if the SQL > statement is not an INSERT statement.", so we only have to parse > INSERT statements, which is a much easier task than parsing the > full range of possible queries. (And we must only parse the table > and columns list). > > So if we could parse such INSERT queries, we would still have to > decide which columns to fetch if the caller doesn't specify them, > which would require the driver to look at the database meta data to > find out which columns to return. That is so much overhead, I > guess, that I think it would be easier to just use "RETURNING *" > and let the application decide which columns it would like to have. > Do you see any issues with this? > > There are four executeUpdate methods in Statement (and > corresponding prepareStatement methods). I would suggest doing the > following with them: > > int executeUpdate(String sql): > Don't change, this one is not required to return auto-generated > key, AFAIK. So no overhead in the common case. > > int executeUpdate(String sql, int autoGeneratedKeys): > If the caller wants auto-generated columns to be returned, use > RETURNING * to return all rows of the row. Since they do not > specify which columns to fetch, they cannot expect any certain > column order anyways. Alternatively we could parse the query, find > out the relation we are going to insert into, and fetch the primary > key and any column having a DEFAULT serial. But as I said above, > that sounds like to much overhead anyways. Makes sense, additionally I'd like to be able to specify my own insert returning clause, so If I do know what I want I can get it without returning all of them. > > int executeUpdate(String sql, String[] columnNames): > This one is the easiest, and the one we should certainly support. > Just add an appropriate RETURNING clause. > > int executeUpdate(String sql, int[] columnIndexes): > This one is tricky. It would be easier if RETURNING supported > positional column definitions (like in ORDER BY), but AFAIK it does > not, at least not according to the docs[1]. And since we are in > beta, there is no chance for adding it in the 8.2 release. > Therefore we can again either parse the columns list in the query, > or get the columns from the meta data, or use RETURNING * and just > filter the output columns, although I would certainly prefer > parsing the query. From what I read this requires (at a minimum) parsing the input SQL in order to figure out the column positions > > Personally I think we should at least support the String[] > columnNames case, but I would like to try supporting all cases. Do the easy ones > > Comments? Anyone see a defect in my reasoning? Dave > > Best Regards, > Michael Paesold > > [1] http://developer.postgresql.org/pgdocs/postgres/sql-insert.html > >
В списке pgsql-jdbc по дате отправления: