Re: JDBC not returning update count from updateable view

Russell Keane <Russell.Keane@xxxxxxxxxx> · Tue, 26 Feb 2013 15:01:14 +0000

But this works just fine when using libpq via c++.

From: davecramer@xxxxxxxxx [mailto:davecramer@xxxxxxxxx] On Behalf Of Dave Cramer
Sent: 26 February 2013 12:23
To: Russell Keane
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] 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.Keane@xxxxxxxxxx> 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 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