Re: updateable resultset only working for tables?

Guido, Dave,

This should work for views as well (assuming as is the case here rules
are applied to the view making the view itself updateable).

The logic in the driver first looks for a primary key on the underlying
object of the select statement and if a primary key is found then see if
the select includes the primary key columns. If it does then use these
columns in building subsequent update statements.

However if either the table doesn't have a primary key, or the primary
key columns are not selected by the query, the driver falls back to
looking for a column in the select named 'oid' and it will use that as
the primary key for subsequent updates.

So as long as the select statement has a selected column named 'oid'
whose datatype is compatable with the oid datatype (i.e. I think integer
would also work, but I doubt varchar would), then the driver should just
go ahead and use this column in building its update statements.

So in the case at hand, I think the driver should work, however the
rules defined on the view will probably need to be different to allow
the view to be updated by the following type of update statement that
the driver would generate:
update <view> set bar = ? where oid = ?

thanks,
--Barry
Dave Cramer wrote:
> Actually, Guido, I put a hack in the driver that if you do
>
> select oid, * from table it should work.
>
> Dave
> On Thu, 2004-03-11 at 08:00, Kris Jurka wrote:
>
>>On Thu, 11 Mar 2004, Guido Fiala wrote:
>>
>>
>>>First i tried to update/insert records using an updateable resultset using a
>>>table as datasource, that works just fine.
>>>
>>>My application requires to do updates also to views which are created
>>>about as this:
>>>
>>>create view sometable_update as Select * from sometable;
>>>create rule sometable_update_rule as on update to sometable_update do instead
>>>update sometable set ...;
>>>
>>>Updating to sometable_update does work if i type an update statement myself.
>>>
>>>However, using code like this:
>>>
>>>Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
>>>ResultSet.CONCUR_UPDATABLE);
>>>ResultSet urs=stmt.executeQuery("SELECT * FROM sometable_update");
>>>
>>>results in an SQLException "no primary keys".
>>>
>>>Basically i assume, that this happens because the driver is not able to
>>>detect the primary keys of a view, which is mainly because postgres
>>>stores views in a completly different way as tables.
>>
>>Well, views don't have primary keys, just like SELECT statements don't
>>have primary keys. A view is completely opaque to the JDBC driver, it
>>doesn't know if the view definition is from multiple tables or involves
>>calculations, so it can't drill through it and update the base tables.
>>Now with your example you have provided an on update rule which alleviates
>>the multiple tables and calculations problems, but we still have no
>>primary key.
>>
>>
>>>Is there a way to tell the driver the primary keys manually (as i know
>>>them at this point in my code) for a certain ResultSet/Statement or
>>>another solution to this problem?
>>
>>How would you propose providing this information to the driver? If you
>>have the primary key columns, you should also have the non-pk columns
>>making an update simple. You're just going to have to bite the
>>bullet on this one and either avoid the view or write an update statement
>>yourself.
>>
>>Kris Jurka
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>