Re: JDBC inserts on views using rules

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
> >
> >
>
>