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.