Thread: 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@yahoo.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@yahoo.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@yahoo.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