Postgresql – Return a record with PL/pgSQL function – to speed up the query

functionsplpgsqlpostgresqlpostgresql-9.3

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.

Best Answer

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;
Related Question