Re: JDBC inserts on views using rules

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