I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is banned or if the player has a VIP status), then I use synchronous queries.
This makes the game stop for a short moment, until the value has been read from the database.
I can not rewrite my game daemon to use async queries for reading values (I tried, but it required too many changes), so my question is: would it make sense to combine several unrelated queries (that I need to make when a new player connects) to 1 procedure and how could I return several values at the same time to my Perl program?
My current queries all take a player ID as parameter and return 1 value:
-- Has the player been banned?
select true from pref_ban where id=?
-- What is the reputation of this player?
select
count(nullif(nice, false)) -
count(nullif(nice, true)) as rep
from pref_rep where id=?
-- Is he or she a special VIP player?
select vip > now() as vip from pref_users where id=?
-- How many games has the player played to the end?
select completed from pref_match where id=?
To combine the above queries I probably need a procedure like this one:
create or replace function get_user_info(_id varchar) returns XXX as $BODY$
declare
is_banned boolean;
reputation integer;
is_vip boolean;
completed_games integer;
begin
select 1 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;
return XXX; /* How to return 4 values here? */
end;
$BODY$ language plpgsql;
Please help me to declare the above procedure properly.