Re: JDBC inserts on views using rules

Oops. My workaround doesn't. I was confused by a pre-existing row when I
tested it. So, back to the drawing board. Sorry.

BTW, from the JDBC 2.0 API compliance page I gather that CallableStatement
is not supported. Are there plans for supporting it in future PostgreSQL
JDBC drivers?

-Stuart

On Sun, 11 Nov 2001, Stuart Robinson wrote:

> I found a workaround for my problem (a rule for inserts on views that call
> functions instead of doing the insert), but it's really just a hack. I'm
> hoping that someone knows of a better way to do this. The solution is as
> follows: my insert-on-a-view rule takes the return value of the function
> that it calls and sticks the value into a dummy column of a dummy table I
> created. So, instead of:
>
> CREATE RULE ex_view_insert AS
> ON INSERT TO ex_view
> DO INSTEAD
> select ex_function(new.url, new.name)
>
> I'm now doing this:
>
> CREATE RULE ex_view_insert AS
> ON INSERT TO ex_view
> DO INSTEAD
> update dummy_table set dummy_column = (select ex_function(new.url,
> new.name));
>
> Now the JDBC no longer gets a return value when it expects none and
> doesn't throw any exceptions. So, it works, but it's ugly. Surely someone
> has come across this sort of problem and has a better solution... If so,
> I'd love to hear it. Thanks in advance.
>
> -Stuart
>
> On Fri, 9 Nov 2001, Stuart Robinson wrote:
>
> > I didn't give the actual code because it's a bit involved and the details
> > probably don't matter too much. I'll just give a simplified example that
> > illustrates my point.
> >
> > Basically, I've got a view that combines two tables.
> >
> > CREATE VIEW ex_view AS
> > SELECT u.url, i.name
> > FROM url u, url_info i
> > WHERE u.id = i.id_url
> >
> > I also have a rule like the following to intercept inserts to the view:
> >
> > CREATE RULE ex_view_insert AS
> > ON INSERT TO ex_view
> > DO INSTEAD
> > select ex_function(new.url, new.name)
> >
> > The function inserts into the tables that make up the view by doing
> > something like this (the logic is more compicated, hence the necessity
> > for a function, but this will give you an idea of what I'm doing):
> >
> > CREATE FUNCTION ex_function(TEXT, TEXT)
> > RETURNS INTEGER
> > AS '
> > DECLARE
> >
> > BEGIN
> > INSERT INTO url (url) VALUES ($1);
> > INSERT INTO url_info (name) VALUES ($2);
> > RETURN 1;
> > END;
> > '
> > LANGUAGE 'plpgsql';
> >
> > This works fine when you run it manually in psql, but when it's run by the
> > JDBC, you get an error, because it returns a value (1) (since I
> > called the function with a select). However, the application doesn't
> > expect a return value, since it
> > thought it was doing an insert and used the executeUpdate method.
> >
> > So, is there some way of supressing the return value so that the
> > rule-redirected insert will succeed? Could I call the function w/
> > something other than select? I hope the problem is clear and that there's
> > a straightforward solution, but if functions necessarily return values
> > unless they're called as triggers, I might be out of luck.
> >
> > Thanks.
> >
> > -Stuart
> >
> > On Fri, 9 Nov 2001, Barry Lind wrote:
> >
> > > Stuart,
> > >
> > > What is the sql statement you are issuing that is causing this error.
> > > Without seeing the sql statement I am having a difficult time
> > > understanding exactly what you are trying to do.
> > >
> > > thanks,
> > > --Barry
> > >
> > >
> > > Stuart Robinson wrote:
> > >
> > > > I sent this to the general mailing list, but I thought it might be
> > > > appropriate for this forum, since it does relate to the JDBC.
> > > >
> > > > Regards,
> > > > Stuart Robinson
> > > >
> > > > ---------- Forwarded message ----------
> > > > Date: Thu, 8 Nov 2001 23:51:33 -0800 (PST)
> > > > From: Stuart Robinson <stuart(at)zapata(dot)org>
> > > > To: pgsql-general(at)postgresql(dot)org
> > > > Subject: [GENERAL] inserts on views using rules
> > > >
> > > > I've got a view that combines a couple of tables. The view is meant to
> > > > simplify interactions with a Java application, so that selects, inserts,
> > > > and updates can be made on the view as if it were a real table. So, I
> > > > figured that rules would be the way to go. For inserts, I wrote a
> > > > do-instead rule for the view which in turn calls a function. The function
> > > > is meant to take the values from the insert statement and stick them into
> > > > the appropriate tables. However, functions appear to always return a value
> > > > (except as triggers), which confuses the JDBC. So, when I do an insert on
> > > > the view, the function is called and returns a value, causing the
> > > > following error:
> > > >
> > > > A result was returned by the statement, when none was expected.
> > > > at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:80)
> > > > at
> > > > org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:122)
> > > >
> > > > I take it this is the expected JDBC behavior and not a bug. If so, what
> > > > are my options given that functions always return values and triggers
> > > > operate before or after inserts, but not instead of them? Is there some
> > > > way of calling functions so that they don't return a value? (If this
> > > > posting is more appropriate for another mailing list, please let me know.)
> > > > Thanks in advance.
> > > >
> > > > -Stuart Robinson
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > > message can get through to the mailing list cleanly
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 5: Have you checked our extensive FAQ?
> > > >
> > > > http://www.postgresql.org/users-lounge/docs/faq.html
> > > >
> > > >
> > >
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>