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<https://twitter.com/INPSnews> on twitter | visit
www.inps.co.uk<http://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.helpd...@inps.co.uk
