JDBC not returning update count from updateable view

Russell Keane <Russell.Keane@xxxxxxxxxx> · Mon, 25 Feb 2013 19:52:19 +0000

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
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.helpdesk@xxxxxxxxxx