Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

in plpgsql, how to exit from a function returning records

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.

like image 454
cc young Avatar asked Jan 03 '12 04:01

cc young


People also ask

How do you exit a function in PostgreSQL?

The meta-command for exiting psql is \q .

Can a function return a record?

Records can be returned from functions and methods as a single structured value, by using only the record name in the RETURNS instruction. Important: To return a record as a single structured value, functions must have been defined with the fully typed syntax.

How do I return a set of records in PostgreSQL?

Let's make a function that returns all the rows of a table whose name you pass in as a parameter. create or replace function GetRows(text) returns setof record as ' declare r record; begin for r in EXECUTE ''select * from '' || $1 loop return next r; end loop; return; end ' language 'plpgsql';


1 Answers

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
like image 52
mu is too short Avatar answered Sep 28 '22 06:09

mu is too short