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


Mon, 17 Dec 2001 10:20:14 -0800
Don Baccus <dhogaza@pacifier.com>
Bug in PG 7.2b4 (and b2, for good measure)