Search Postgresql Archives

JDBC not returning update count from updateable view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux