Обсуждение: JDBC inserts on views using rules
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
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 > >
Did you try executeQuery in place of executeUpdate? -Jayesh > -----Original Message----- > From: Barry Lind [SMTP:barry@xythos.com] > Sent: Friday, November 09, 2001 11:20 AM > To: Stuart Robinson > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] JDBC inserts on views using rules > > 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 > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
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 > > > > > >
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@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 > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Oops. My workaround doesn't. I was confused by a pre-existing row when I tested it. So, back to the drawing board. Sorry. BTW, from the JDBC 2.0 API compliance page I gather that CallableStatement is not supported. Are there plans for supporting it in future PostgreSQL JDBC drivers? -Stuart On Sun, 11 Nov 2001, Stuart Robinson wrote: > 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@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 > > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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@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 >>> >>> >>> >> > >
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@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 > >>> > >>> > >>> > >> > > > > > > > > ---------------------------(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 >