PostgreSQL function return multiple distinct types

Question

is it possible for a function to return multiple distinct types?

Example:

I have following tables: games, games_rounds and games_players

Now to load a full game by it's id i need to first load it from games and then load all rounds from games_rounds by doing where game_id = $1 and then do the same for games_players, that's 3 distinct calls to the database, can this be somehow optimized into a function?

Simple demo:

// Basic schema (all game_id rows have an index on them):
    
create table games (id serial, state smallint, value int);

create table games_rounds (id serial, game_id int, outcome int);

create table games_users (game_id int, user_id int, order int);

// Example query to get full game data:

select id, state, value from games where id = 1

select id, outcome from games_rounds where game_id = 1

select user_id, order from games_users where game_id = 1

As you can see above games_users and games_rounds are many-to-one in a relationship to games, the queries are simplified in real application there are many more rows and some joins

Answer 1

If I understand correctly this is what you want to do , you can use refcursor

create procedure getGamedata(gameid int,result_one inout refcursor, result_two inout refcursor, result_two inout refcursor, result_three inout refcursor)
as
$$
begin
  open result_one for 
    select id, state, value from games where id = gameid;

  open result_two for 
    select id, outcome from games_rounds where game_id = gameid;

  open result_three for 
    select user_id, order from games_users where game_id = gameid;
end;
$$
language plpgsql;