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

2010/2/3 Lothar Bongartz <lotharbongartz(at)hotmail(dot)com>:
>
> The following bug has been logged online:
>
> Bug reference:      5310
> Logged by:          Lothar Bongartz
> Email address:      lotharbongartz(at)hotmail(dot)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:

what I know SELECT INTO doesn't raise exception.

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

(1 row)

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

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

regards
Pavel Stehule

>
> 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;
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>