When it comes to data issues (too many rows, no data found, etc), in Oracle stored procedures, I am used to having exceptions raised that I then handle. PL/pgSQL does not raise exceptions for the same conditions in the same way. The Postgres docs are pretty complete though and through some reading this weekend, I discovered a new keyword. For example, assuming that I have this table (which is empty) in both Oracle and Postgres:
CREATE TABLE empty_table ( empty_col integer );In Oracle this procedure:
CREATE OR REPLACE PROCEDURE no_data_found_test
AS
v_int_field INTEGER;
BEGIN
SELECT empty_col
INTO v_int_field
FROM empty_table;
END;
When run:
BEGIN no_data_found_test; END;Produces an error:
Error starting at line 1 in command: BEGIN no_data_found_test; END; Error report: ORA-01403: no data found ORA-06512: at "HR.NO_DATA_FOUND_TEST", line 6 ORA-06512: at line 2 01403. 00000 - "no data found" *Cause: *Action:However, the equivalent procedure in Postgres:
CREATE OR REPLACE FUNCTION no_data_found_test()
RETURNS void AS
$BODY$
DECLARE
v_int_field INTEGER;
BEGIN
SELECT empty_col
INTO v_int_field
FROM empty_table;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Does not raise an exception when run:
postgres=# select * from no_data_found_test(); no_data_found_test -------------------- (1 row)I have been using the FOUND variable to check for a result and raise an exception manually if no data was found. Like this:
CREATE OR REPLACE FUNCTION no_data_found_test()
RETURNS void AS
$BODY$
DECLARE
v_int_field INTEGER;
BEGIN
SELECT empty_col
INTO v_int_field
FROM empty_table;
IF NOT FOUND THEN
raise exception 'NO_DATA_FOUND';
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
When run, I do get an exception like I was expecting.
postgres=# select * from no_data_found_test(); ERROR: NO_DATA_FOUNDHowever, after a little bit more reading, I see that I can add the STRICT keyword to make the procedure behave like Oracle:
CREATE OR REPLACE FUNCTION no_data_found_test()
RETURNS void AS
$BODY$
DECLARE
v_int_field INTEGER;
BEGIN
SELECT empty_col
INTO STRICT v_int_field
FROM empty_table;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
And now when run, it does raise the exception:
postgres=# select * from no_data_found_test(); ERROR: query returned no rows CONTEXT: PL/pgSQL function "no_data_found_test" line 5 at SQL statement postgres=#Very Nice! I think I prefer this method. It would be nice if STRICT were a database wide configuration parameter. LewisC postgresql no_data_found
