Thread: JDBC not returning update count from updateable view
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,
$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
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
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>wr= ote: > 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 th= e > java side) doesn=92t 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*Ob= ject[] 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,**** > > $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.u= k > **** > > ** ** > > ------------------------------ > Registered name: In Practice Systems Ltd. > Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3= QJ > 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@inps.co.uk > >
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>
On 25 February 2013 19:52, Russell Keane <Russell.Keane@inps.co.uk> wrote: > Hi, > > > > We have a table which is inserted to and update via a view (using rules / > functions). > In PG 9.1 and later, the recommended way to do this is using INSTEAD OF triggers on the view. See the "Notes" section here: http://www.postgresql.org/docs/current/static/sql-createview.html and the example here: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE Regards, Dean