Re: BUG #5105: "Select Into Strict" does not throw NO_DATA_FOUND

The following bug has been logged online:

Bug reference: 5105
Logged by: Walter Mesz
Email address: meszwalter(at)yahoo(dot)de
PostgreSQL version: 8.4.0
Operating system: Windows XP SP2
Description: "Select Into Strict" does not throw NO_DATA_FOUND
Details:

Hi,

my problem is that this select into does not throw a NO_DATA_FOUND if my
select involves a max(). I did not see this behaviour documented anywhere
and could not find it in a reasonable time at google.

-------------code----------------------------------
create or replace function xyz() returns void as
$BODY$ declare
x integer;
begin
SELECT max(tanum)
INTO STRICT x
FROM lo_prod_req
WHERE tanum = '1234567';

raise notice 'failed';

exception
WHEN NO_DATA_FOUND
THEN
raise notice 'it should be as this';
end ;
$BODY$
LANGUAGE 'plpgsql';
-------------code----------------------------------

It does throw an Exception if I change the query into this though:

-------------code----------------------------------
SELECT tanum INTO STRICT x
FROM lo_prod_req
WHERE tanum = '1234567' limit 1;
-------------code----------------------------------

Although this query does not make much sense with the max() statement it
should work anyway

Thank you
Walter Mesz

"Walter Mesz" <meszwalter(at)yahoo(dot)de> writes:
> my problem is that this select into does not throw a NO_DATA_FOUND if my
> select involves a max().

Well, a query using max() (or any other aggregate) is defined to return
exactly one row, independently of how many rows feed into the max().
So I'm not sure why you'd think that it should throw NO_DATA_FOUND.

If you want to test for not finding any rows in the underlying scan,
the best way would be to also compute count(*) and check if that's
zero.

regards, tom lane

"Walter Mesz" <meszwalter(at)yahoo(dot)de> wrote:

> my problem is that this select into does not throw a NO_DATA_FOUND
> if my select involves a max(). I did not see this behaviour
> documented anywhere and could not find it in a reasonable time at
> google.

> SELECT max(tanum)
> INTO STRICT x
> FROM lo_prod_req
> WHERE tanum = '1234567';

The documentation says:

$ If the STRICT option is specified, the query must return exactly one
$ row or a run-time error will be reported

http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html

In this case the query will always return one row. The row may have a
NULL if no matching values were found, but the row will be there.

select max(x) from (select generate_series(1,10) as x) y where x > 10;
max
-----

(1 row)

Not a bug.

-Kevin