Re: JDBC inserts on views using rules

Sorry, I'm afraid that switching methods won't solve my problem, because
the point is that the application is trying to do an insert and therefore
must use the executeUpdate method. But I want the database to be smart
enough to intercept the insert and call my function instead, but since the
only way to call a function is w/ select, a value will be returned, making
executeUpdate inappropriate.

It sounds like it's just not possible to have a rule that intercepts
insert on a view and calls a function instead, given the current state of
PostgreSQL. At any rate, it's not really a JDBC problem, so I'll quit
harassing the JDBC mailing list.

Thanks.

-Stuart

On Fri, 9 Nov 2001, Barry Lind wrote:

> Stuart,
>
> OK, thanks for the explaination. All you need to do is use
> executeQuery() instead of executeUpdate() and ignore the ResultSet if
> you don't want the return value.
>
> thanks,
> --Barry
>
>
> 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 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
>