The following bug has been logged online:
Bug reference: 2873
Logged by: Jonathan Hull
Email address: jono@fabsoftware.com
PostgreSQL version: 8.1 / 8.2
Operating system: Linux
Description: Function that returns an empty set with a 'not null'
domain errors in 8.2 but not 8.1
Details:
Hi,
I have been testing PostgreSQL 8.2 and have noticed that a function that
returns an empty result set errors in 8.2 but not in 8.1 - is this a bug?
The key feature for the error is that when a result structure (eg : pg_foo)
is defined with a domain type that is not null, only PG 8.2 errors if the
result is an empty set. If the domain is replaced by a standard data type,
no error occurs in either 8.1 or 8.2.
Below is some code to duplicate the issue. When the two runs are executed in
PG 8.1, both work. When the two runs are executed in PG 8.2, the second run
errors.
Run 1 - without domain [succeeds PG 8.1 and 8.2] :
-- Create test Run 1
create table pg_foo (foo_field varchar(20) not null);
create or replace function test_foo(integer) returns setof pg_foo AS '
declare
isempty alias for $1;
foovar pg_foo%rowtype;
begin
for foovar in
select ''has a value'' as foo_field where (0 = isempty)
loop
return next foovar;
end loop;
return;
end;
' LANGUAGE 'plpgsql';
select * from test_foo(0);
select * from test_foo(1);
Run 2 - with domain [succeeds PG 8.1, errors 8.2] :
-- Remove test Run 1 first
drop function test_foo(integer);
drop table pg_foo;
-- Create test Run 1
create domain dom_foo as varchar(20) not null;
create table pg_foo (foo_field dom_foo);
create or replace function test_foo(integer) returns setof pg_foo AS '
declare
isempty alias for $1;
foovar pg_foo%rowtype;
begin
for foovar in
select ''has a value'' as foo_field where (0 = isempty)
loop
return next foovar;
end loop;
return;
end;
' LANGUAGE 'plpgsql';
select * from test_foo(0);
select * from test_foo(1);
Is this a bug or is this a new constraint feature implemented in 8.2?
Jonathan Hull
Director (Operations)
FAB Software (NZ) Limited
Ph +64 3 365 7851
PO Box 4567
Christchurch
New Zealand
