JDBC not returning update count from updateable view - Mailing list pgsql-general
From | Russell Keane |
---|---|
Subject | JDBC not returning update count from updateable view |
Date | |
Msg-id | 8D0E5D045E36124A8F1DDDB463D548557D1618F5D5@mxsvr1.is.inps.co.uk Whole thread Raw |
Responses |
Re: JDBC not returning update count from updateable view
(Dave Cramer <pg@fastcrypt.com>)
Re: JDBC not returning update count from updateable view (Dean Rasheed <dean.a.rasheed@gmail.com>) |
List | pgsql-general |
Hi, We have a table which is inserted to and update via a view (using rules / f= unctions). 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 becau= se the postgres update function actually returns a tuple rather than a sing= le 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 =3D 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 =3D 2 WHERE aud_seq =3D $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 =3D 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 =3D 5 where id =3D 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<h= ttp://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 sole= ly for the addressee. Access, copying or re-use of information in it by any= one else is not authorised. Any views or opinions presented are solely thos= e of the author and do not necessarily represent those of INPS or any of it= s affiliates. If you are not the intended recipient please contact is.helpd= esk@inps.co.uk
pgsql-general by date: