Обсуждение: BUG #5310: "NOT FOUND" throws "GetData to Procedure return failed." in stored functions

The following bug has been logged online:

Bug reference:      5310
Logged by:          Lothar Bongartz
Email address:      lotharbongartz@hotmail.com
PostgreSQL version: 8.4
Operating system:   Windows XP Professioanl
Description:        "NOT FOUND" throws "GetData to Procedure return failed."
in stored functions
Details:

A "NOT FOUND" condition in a stored function throws a "GetData to Procedure
return failed." error, which cannot be trapped by the EXCEPTION handling.
Example:

SELECT msg_id INTO v_nm FROM newmail WHERE memb_id=v_id;

The only way to avoid the complete failing of the stored function is to do a
check before:

IF EXISTS (SELECT * FROM newmail WHERE memb_id=v_id) THEN
    SELECT msg_id INTO v_nm FROM newmail WHERE memb_id=v_id;
END IF;

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
>

"Lothar Bongartz" <lotharbongartz@hotmail.com> writes:
> A "NOT FOUND" condition in a stored function throws a "GetData to Procedure
> return failed." error, which cannot be trapped by the EXCEPTION handling.

There is no such error message text anywhere in Postgres, and you
haven't given enough context to let anyone guess what the complaint
actually is about.  Please show a *complete* example not a one-line
extract.

The expected behavior of the command you show is to set the INTO
variable(s) to NULL if there is no matching row.  That isn't an
error though.

            regards, tom lane