Re: JDBC inserts on views using rules

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
>