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

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


From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave =
Cramer
Sent: 26 February 2013 12:23
To: Russell Keane
Cc: pgsql-general@postgresql.org
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@inps.co.uk<ma=
ilto:Russell.Keane@inps.co.uk>> wrote:
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<http://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<http://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<mailto:is.helpdesk@inps.co.uk>