Re: JDBC not returning update count from updateable view

As far as I remember this is an artifact of using rules to update a table.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Mon, Feb 25, 2013 at 2:52 PM, Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)uk>wrote:

> Hi,****
>
> ** **
>
> We have a table which is inserted to and update via a view (using rules /
> functions).****
>
> ** **
>
> We are trying to update this from JDBC but the view update command (on the
> java side) doesn’t return the count of rows updated. I assume this is
> because the postgres update function actually returns a tuple rather than a
> single count.****
>
> ** **
>
> Any ideas?****
>
> ** **
>
> A simplified version of the java bit:****
>
> ** **
>
> JdbcTemplate is: org.springframework.jdbc.core.JdbcTemplate;****
>
> ** **
>
> *public* *final* *int* updateTest(*final* String updateSQL, *final*Object[] args) {
> ****
>
> JdbcTemplate template = createJdbcTemplate();****
>
> *return* template.update(updateSQL, args);****
>
> }****
>
> ** **
>
> And the postgres object creation (again simplified):****
>
> ** **
>
> --PG START****
>
> ** **
>
> drop table if exists msg_table cascade;****
>
> drop sequence if exists msg_seq;****
>
> drop sequence if exists msg_aud_seq;****
>
> create sequence msg_seq;****
>
> create sequence msg_aud_seq;****
>
> ** **
>
> CREATE TABLE msg_table****
>
> (****
>
> aud_seq int default nextval('msg_aud_seq'),****
>
> status int default 1,****
>
> id int default nextval('msg_seq'),****
>
> val int****
>
> );****
>
> ** **
>
> create or replace view msg as ****
>
> select****
>
> aud_seq,****
>
> id,****
>
> status,****
>
> val****
>
> from msg_table;****
>
> ** **
>
> -- audit the original record****
>
> CREATE OR REPLACE FUNCTION audit_original_record(msg) RETURNS void AS $$**
> **
>
> BEGIN****
>
> UPDATE msg_table****
>
> SET****
>
> status = 2****
>
> WHERE****
>
> aud_seq = $1.aud_seq;****
>
> END;****
>
> $$ LANGUAGE plpgsql;****
>
> ** **
>
> ** **
>
> -- insert function****
>
> CREATE OR REPLACE FUNCTION process_insert(msg) RETURNS integer AS $body$**
> **
>
> declare new_id integer;****
>
> ****
>
> BEGIN****
>
> ****
>
> INSERT INTO msg_table ****
>
> (****
>
> val****
>
> )****
>
> SELECT****
>
> $1.val****
>
> ****
>
> RETURNING id INTO new_id;****
>
> ****
>
> return new_id;****
>
> END;****
>
> $body$ LANGUAGE plpgsql;****
>
> ** **
>
> -- update function****
>
> CREATE OR REPLACE FUNCTION process_update(msg, msg) RETURNS void AS $body$
> ****
>
> BEGIN****
>
> INSERT INTO msg_table ****
>
> (****
>
> id,****
>
> val****
>
> )****
>
> SELECT****
>
> $1.id,****
>
> $1.val;****
>
> ** **
>
> EXECUTE audit_original_record($2);****
>
> ** **
>
> END;****
>
> $body$ LANGUAGE plpgsql;****
>
> ** **
>
> -- insert to msg****
>
> create or replace rule msg__rule_ins as on insert to msg****
>
> do instead****
>
> SELECT process_insert(NEW);****
>
> ** **
>
> -- update to msg****
>
> create or replace rule msg__rule_upd as on update to msg****
>
> do instead****
>
> SELECT****
>
> COUNT(process_update(NEW, OLD))****
>
> WHERE****
>
> NEW.status = 1;****
>
> ** **
>
> ** **
>
> alter sequence msg_seq restart 1;****
>
> alter sequence msg_aud_seq restart 1;****
>
> ** **
>
> delete from msg_table;****
>
> ** **
>
> insert into msg****
>
> (val)****
>
> values****
>
> (1),****
>
> (2),****
>
> (66);****
>
> ** **
>
> select * from msg;****
>
> ** **
>
> update msg****
>
> set val = 5****
>
> where id = 1;****
>
> ** **
>
> select * from msg;****
>
> ** **
>
> --PG END****
>
> ** **
>
> ** **
>
> Thanks for any help you can give me.****
>
> ** **
>
> Regards,****
>
> ** **
>
> *Russell Keane***
>
> *INPS*****
>
> Follow us <https://twitter.com/INPSnews> on twitter | visit www.inps.co.uk
> ****
>
> ** **
>
> ------------------------------
> Registered name: In Practice Systems Ltd.
> Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
> Registered Number: 1788577
> Registered in England
> Visit our Internet Web site at www.inps.co.uk
> The information in this internet email is confidential and is intended
> solely for the addressee. Access, copying or re-use of information in it by
> anyone else is not authorised. Any views or opinions presented are solely
> those of the author and do not necessarily represent those of INPS or any
> of its affiliates. If you are not the intended recipient please contact
> is(dot)helpdesk(at)inps(dot)co(dot)uk
>
>