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.
The meta-command for exiting psql is \q .
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.
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';
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 ofRETURN NEXT
orRETURN QUERY
commands, and then a finalRETURN
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With