rule and JDBC - Mailing list pgsql-hackers

Hi,

It seems JDBC driver does not handle if a INSERT SQL statement
performed by executeUpdate() is actually a SELECT, which is rewritten
by the rule system. 

Exception in thread "main" postgresql.stat.resultat
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:199)at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)attest.main(test.java:10)
 

I'm not sure whether the JDBC driver or PostgreSQL backend should be
blamed though.
--
Tatsuo Ishii

Here is a test case:

create table daily_log (   log_date timestamp default current_timestamp,   log_memo varchar
);
create table daily_log01 (   log_date timestamp default current_timestamp,   log_memo varchar
);
create table daily_log02 (   log_date timestamp default current_timestamp,   log_memo varchar
);

create function insert_daily_log(timestamp,varchar) returns void as '   declare       in_log_date alias for $1;
in_log_memoalias for $2;   begin       if 1=0 then           insert into daily_log01 (log_date,log_memo)
values(in_log_date,in_log_memo);       elsif 1=1 then           insert into daily_log02 (log_date,log_memo)
 values (in_log_date,in_log_memo);       end if;       return;   end;
 
' language plpgsql;

create rule insert_daily_log_rule as on insert to daily_log      do instead select
insert_daily_log(NEW.log_date,NEW.log_memo);

import java.sql.*;

public class test {   public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");      Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost/testdb","dummy", "");       conn.setAutoCommit(false);       Statement stat =
conn.createStatement();      stat.executeUpdate(           "insert into daily_log (log_memo) values ('hoge')");
 
/* this will not raise an error BTW       stat.executeQuery(           "insert into daily_log (log_memo) values
('hoge')");
*/       conn.commit();       stat.close();       conn.close();   }
}