I need to port over some Oracle PL/SQL code to Postgres. This is my first time working with Postgres.
In Oracle, with regards to exceptions, I can have this:
IF v_customer_id IS NULL OR v_email IS NULL THEN
RAISE invalid_paramters;
END IF;
How is that done in Postgres? Basically validating input, and if anything fails validation, call a custom handler to perform whatever actions. From what I have read, Postgres does not support custom named exceptions.
Thanks for your time.
You could use RAISE with a custom message and a specific sqlstate constant:
--Anonymous block for testing purposes
DO $$
BEGIN
RAISE invalid_parameter_value USING MESSAGE = 'Invalid customer or email';
END $$;
Or you could simply raise a generic exception:
DO $$
BEGIN
RAISE EXCEPTION 'A generic exception (P0001)';
END $$;
You could also handle a exception:
DO $$
BEGIN
--This will raise a division by zero
PERFORM 0 / 0;
--You can catch a exception with a EXCEPTION block
EXCEPTION
WHEN division_by_zero THEN
RAISE INFO 'Division by zero catched';
WHEN raise_exception THEN
RAISE INFO 'Another error catched...';
END $$;
And get more detailed information about a exception:
DO $$
DECLARE
error_msg text;
BEGIN
--This will raise a division by zero
PERFORM 0 / 0;
--You can get more about error with GET STACKED DIAGNOSTICS
EXCEPTION
--Tip: OTHERS keyword will catch any exception
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS error_msg = MESSAGE_TEXT;
RAISE EXCEPTION 'My custom exception: %', error_msg;
END $$;
I'd suggest you to take a look on sqlstates and control structures for more about error handling in PostgreSQL.
do $$
declare
v int := 1;
begin
begin
if v < 2 then -- Validation here
raise sqlstate 'A0001'; -- custom SQL state, any five upper case letters and/or digits
end if;
exception -- Catch exception in the nested BEGIN ... END block
when sqlstate 'A0001' then
raise notice 'Here is my exception handler';
v := 2;
end;
raise notice 'Continue here with value %', v; -- Reports that the v = 2
end $$;
Instead of capturing it in the main exception block, you can add a nested begin...end block in the place of RAISE EXCEPTION part and raise an exception inside and capture it in the others exception block inside the nested block with a RETURN keyword.
do
$$
begin
raise notice 'line 1';
begin
raise exception 'raising exception';
exception
when others then
raise notice 'captured in nested block';
end;
return;
raise notice 'check if it continues';
exception
when others then
raise notice 'main block others';
end;
$$
language plpgsql;
