Re: JDBC inserts on views using rules - Mailing list pgsql-jdbc
From | Stuart Robinson |
---|---|
Subject | Re: JDBC inserts on views using rules |
Date | |
Msg-id | Pine.LNX.4.30.0111091127500.11712-100000@othello.dreamingamerica.com Whole thread Raw |
In response to | Re: JDBC inserts on views using rules (Barry Lind <barry@xythos.com>) |
Responses |
Re: JDBC inserts on views using rules
(Stuart Robinson <stuart@zapata.org>)
|
List | pgsql-jdbc |
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@zapata.org> > > To: pgsql-general@postgresql.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@postgresql.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 > > > > > >
pgsql-jdbc by date: