I've hunted online and through documentation, but the much more common use case appears to be returning a SETOF some particular row type, not just a single row.
The use case below is trivial for illustration, as I am just trying to determine if this is even possible in plpgsql, and understand the syntax to use for it if it is. (Just like the Postgresql documentation contains examples of functions to return a static set of fixed values, the point here is not the use case but the syntax.)
With this table:
create table my_table (f1 int primary key, f2 text, f3 real, f4 text, f5 bool);
The following function can be created:
create function my_func1(f1_to_look_up int) returns my_table
language sql
as $$ select * from my_table where f1 = f1_to_look_up $$;
Also, this can be done in plpgsql like so, with explicit out parameters (and with f1
specified explicitly in the where
clause to avoid ambiguity):
create function my_func2(f1_to_look_up int, out f1 int, out f2 text, out f3 real, out f4 text, out f5 bool)
language plpgsql
as $$
begin
select * into strict f1, f2, f3, f4, f5
from my_table where my_table.f1 = f1_to_look_up;
return;
end
$$;
Is there a way to accomplish the same thing in plpgsql, without needing to repeat the definition of my_table
in order to list all the OUT parameters?
returns setof my_table
? – a_horse_with_no_name Dec 30 '19 at 20:49return next
by processing each in turn. – Wildcard Dec 30 '19 at 20:51