Return a record with PL/pgSQL function - to speed up the query
Using OUT
parameters achieve basically the same thing as in @klin's answer, but without creating user-defined types. Just move all your variables from the declare block into the argument-list as OUT
parameters:
create or replace function get_user_info(
IN _id varchar,
OUT is_banned boolean,
OUT reputation integer,
OUT is_vip boolean,
OUT completed_games integer
)
-- no returns clause necessary, output structure controlled by OUT parameters
-- returns XXX
as $BODY$
begin
select true into is_banned from pref_ban where id=_id;
select
count(nullif(nice, false)) -
count(nullif(nice, true))
into reputation
from pref_rep where id=_id;
select vip > now() into is_vip from pref_users where id=_id;
select completed into completed_games from pref_match where id=_id;
-- no return statement necessary, output values already stored in OUT parameters
-- return XXX;
end
$BODY$ language plpgsql;
This will return a record (exactly one), so you can select its values as a normal record:
-- this will return all properties (columns) from your function:
select * from get_user_info();
-- these will return one property (column) from your function:
select is_banned from get_user_info();
select (get_user_info()).is_banned;
You should define a composite type. You can use it as return type of function and for record variables inside a function.
Example:
create type user_type as (
is_banned boolean,
reputation integer,
is_vip boolean,
completed_games integer);
create or replace function check_user_type ()
returns user_type language plpgsql as $$
declare
rec user_type;
begin
select true into rec.is_banned;
select 100 into rec.reputation;
select false into rec.is_vip;
select 22 into rec.completed_games;
-- you can do the same in a little bit nicer way:
-- select true, 100, false, 22 into rec
return rec;
end $$;
select * from check_user_type();
In my opinion using functions like this is quite reasonable in terms of both performance and application logic.
User-defined composite types are very useful if you want to return set of rows from your function. Then you should define return type of the function as setof composite-type
and use return next
or return query.
Example:
create or replace function check_set_of_user_type ()
returns setof user_type language plpgsql as $$
declare
rec user_type;
begin
for rec in
select i/2*2 = i, i, i < 3, i+ 20
from generate_series(1, 4) i
loop
return next rec;
end loop;
return query
select true, 100+ i, true, 100+ i
from generate_series(1, 2) i;
end $$;
select * from check_set_of_user_type();
is_banned | reputation | is_vip | completed_games
-----------+------------+--------+-----------------
f | 1 | t | 21
t | 2 | t | 22
f | 3 | f | 23
t | 4 | f | 24
t | 101 | t | 101
t | 102 | t | 102