in plpgsql, how to exit from 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 a RETURN here requires RETURN QUERY or RETURN NEXT - there seems to be no way to simply exit out of the function.

If a function (like yours) is defined to return TABLE() or SETOF ... you can simply RETURN;. You must be thinking of functions that return a value and have no OUT parameter defined. Then you need to return a value. — Erwin Brandstetter

mu is too short mu is too short · Accepted Answer · 2012-01-03T05:37:53

If p_i < 0 is actually an error then you could raise 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 quietly return nothing then you could 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 fine manual:

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

Emphasis mine. So you can use your return query to return the query and just a simple return; to bail out without doing anything.

For example, the return; version 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 this:

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

in plpgsql, how to exit from a function returning records

1 Answers