Re: JDBC not returning update count from updateable view - Mailing list pgsql-general

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.Keane@inps.co.uk>wr=
ote:

>  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 th=
e
> java side) doesn=92t 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*Ob=
ject[] 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.u=
k
> ****
>
> ** **
>
> ------------------------------
> Registered name: In Practice Systems Ltd.
> Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3=
QJ
> 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.helpdesk@inps.co.uk
>
>