Apparently there's been a change in the way views are handled within
PostreSQL. The following program works fine in earlier versions. 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
inserts of single rows using the view work just 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);
insert into data
select test_seq.nextval
from multiple_rows;
--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org
