In plpgsql, how to exit a function returning records

Question

in postgresql plpgsql,

create function f1( p_i int ) returns table( c1 int ) as $$ begin -- wish to exit, do not wish to return anything if p_i < 0 then -- cannot RETURN - since can only return record! end if; -- continue processing return query select c2 from t1 where c1 = p_i; ... end; $$ language plpgsql; 

according to doc , the only way to break out of a function is RETURN. but RETURN here requires RETURN QUERY or RETURN NEXT - there seems to be no way to just exit the function.

Answer 1

If p_i < 0 is actually an error, you can throw an exception :

 if p_i < 0 then raise exception 'Don''t know what to do with %', p_i end if; 

If p_i < 0 should just calmly return nothing, you can do something like this:

 create or replace function f1( p_i int ) returns table( c1 int ) as $$ begin if p_i < 0 then return; end if; return query select c2 from t1 where c1 = p_i; end; $$ language plpgsql; 

From the exact guide :

39.6.1.2. RETURN NEXT and RETURN QUERY
[...]
individual returned items are specified using a sequence of RETURN NEXT or RETURN QUERY commands, and then the final RETURN command with no argument is used to indicate that the function has completed execution .

The emphasis is mine. That way you can use RETURN QUERY to return a query and just a simple return; for salvation without any action.

For example, the return; version return; gives me things like this:

 => select * from f1(-1); c1 ---- (0 rows) => select * from f1(1); c1 ---- 1 1 ... (15 rows) 

and the exception version does the following:

 => select * from f1(-1); ERROR: Don't know what to do with -1