Re: updateable resultset only working for tables?

Am Donnerstag, 11. März 2004 14:37 schrieb Dave Cramer:
> Actually, Guido, I put a hack in the driver that if you do
>
> select oid, * from table it should work.

I tried without success so far -

"create view someview as select oid, * from table;"

i assume?

What version of driver do i need?

Guido

>
> 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@postgresql.org)