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.ke...@inps.co.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.helpd...@inps.co.uk > >
