Apparently there's been a change in the way views are handled within
PL/pgSQL. The following program works fine in earlier versions of
PostgreSQL. It also works if the select on the view is replaced with a
direct call to nextval().
We use this construct repeatedly in OpenACS so we can share queries that
use sequences between Oracle and PostgreSQL unless they differ in other
ways that can't be reconciled between the two RDBMS's. Obviously, the
greater the number of queries we can share in this way, the lower our
porting, new code development, and code maintenance costs.
As it stands neither the older OpenACS 3.x toolkit nor the upcoming
OpenACS 4.x toolkit will work with PG 7.2.
Ouch.
Sorry for the long delay in reporting this. I only recently decided to
get off my rear end and test against PG 7.2 after Marc Fournier tried to
do an install and ran into a few problems.
Here's some code. The problem's caused by the fact that two rows are
being inserted due to the reference to "multiple rows". Sequential
calls to the function which insert single rows works fine:
create sequence test_seq_x;
create view test_seq as select nextval('test_seq_x') as nextval;
create table data (i integer primary key);
create table multiple_rows (i integer);
insert into multiple_rows values (1);
insert into multiple_rows values (2);
create function f() returns boolean as '
begin
-- The insert works if you use nextval() instead of the view.
insert into data select test_seq.nextval from multiple_rows;
return ''true'';
end;' language 'plpgsql';
select f();
--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org
