12

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.

2 Answers 2

21

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;
2
  • +1 this works great, thanks. Just one small question: currently I have either NULL or TRUE in my is_banned variable with this statement: select true into is_banned from pref_ban where id=_id. Is there a way to change it to FALSE or TRUE? Jun 12, 2014 at 8:23
  • 1
    Yes, is_banned := exists(select 1 from pref_ban where id=_id) should work, but that is a different question.
    – pozs
    Jun 12, 2014 at 8:27
10

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
5
  • 1
    Using OUT parameters achieve basically the same thing, but without creating user-defined types: postgresql.org/docs/current/static/…
    – pozs
    Jun 11, 2014 at 15:13
  • @pozs +1 thanks, I would like to use the OUT parameters - but how SELECT them in my case of 4 unrelated queries? Jun 12, 2014 at 8:06
  • @klin +1 thanks, I have tried your suggestion and it works. For creating my custom type I've used drop type if exists user_type cascade; create type user_type as(...); because my Perl script calls the SQL statements every time at start up time. Jun 12, 2014 at 8:26
  • 1
    You should not do that. Functions in Postgres are stored procedures. Once created are ready to use in any session. The same concerns user defined types. You have to drop a composite type only if you are going to change it (or to remove it at all).
    – klin
    Jun 12, 2014 at 8:36
  • +1 for "select * from my_function()". I was doing "select my_function()" and having trouble. Mar 16, 2019 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.