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>wrote:

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<mailto:Russell.Keane@inps.co.uk>> wrote:
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<http://1.id&gt;,
$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&gt; on twitter | visit www.inps.co.uk<http://www.inps.co.uk/&gt;

________________________________
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&gt;
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<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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general