Re: BUG #5310: "NOT FOUND" throws "GetData to Procedure return failed." in stored functions

2010/2/3 Lothar Bongartz <lotharbongartz@hotmail.com>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05310
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Lothar Bongartz
> Email address: =C2=A0 =C2=A0 =C2=A0lotharbongartz@hotmail.com
> PostgreSQL version: 8.4
> Operating system: =C2=A0 Windows XP Professioanl
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0"NOT FOUND" throws "GetData to Pr=
ocedure return failed."
> in stored functions
> Details:
>
> A "NOT FOUND" condition in a stored function throws a "GetData to Procedu=
re
> return failed." error, which cannot be trapped by the EXCEPTION handling.
> Example:

what I know SELECT INTO doesn't raise exception.

postgres=3D# create table t(a int);
CREATE TABLE
Time: 6,632 ms
postgres=3D# create function f() returns int as $$declare _a int; begin
select a into _a from t where a =3D 10; return _a; end; $$ language
plpgsql;
CREATE FUNCTION
Time: 113,988 ms
postgres=3D# select f();
 f
---

(1 row)


you have to use SELECT INTO STRICT when you would not found exception

postgres=3D# create or replace function f() returns int as $$declare _a
int; begin select a into strict _a from t where a =3D 10; return _a;
end; $$ language plpgsql;
CREATE FUNCTION
Time: 18,734 ms
postgres=3D# select f();
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function "f" line 1 at SQL statement
postgres=3D#

regards
Pavel Stehule

>
> SELECT msg_id INTO v_nm FROM newmail WHERE memb_id=3Dv_id;



>
> The only way to avoid the complete failing of the stored function is to d=
o a
> check before:
>
> IF EXISTS (SELECT * FROM newmail WHERE memb_id=3Dv_id) THEN
> =C2=A0 =C2=A0SELECT msg_id INTO v_nm FROM newmail WHERE memb_id=3Dv_id;
> END IF;
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>