Обсуждение: BUG #5310: "NOT FOUND" throws "GetData to Procedure return failed." in stored functions
BUG #5310: "NOT FOUND" throws "GetData to Procedure return failed." in stored functions
От
"Lothar Bongartz"
Дата:
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;
Re: BUG #5310: "NOT FOUND" throws "GetData to Procedure return failed." in stored functions
От
Pavel Stehule
Дата:
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