JDBC not returning update count from updateable view
JDBC not returning update count from updateable view
От:
Russell Keane <Russell.Keane@inps.co.uk>
Дата:
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 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 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.ukRe: JDBC not returning update count from updateable view
От:
Russell Keane <Russell.Keane@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 > 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,
$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 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 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.ukRe: JDBC not returning update count from updateable view
От:
Dave Cramer <pg@fastcrypt.com>
Дата:
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 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 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
>
>Re: JDBC not returning update count from updateable view
От:
Dean Rasheed <dean.a.rasheed@gmail.com>
Дата:
On 25 February 2013 19:52, Russell Keane 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
