Re: Saving score of 3 players into a table - Mailing list pgsql-general

Hello again,

thank you for your replies. If I create a separate table for games:

    create table pref_users (
            uid varchar(32) primary key,
            first_name varchar(64),
            female boolean,
            avatar varchar(128)
    }

    create table pref_games {
            gid serial,
            rounds integer not null,
            finished timestamp default current_timestamp
    }

    create table pref_scores (
            uid varchar(32) references pref_users,
            gid serial references pref_games,  /* XXX serial ok here? */
            money integer not null,
            quit boolean
    );

then how do I find the new game id after I've just created it here:

    create or replace function pref_insert_scores(
        _uid0 varchar, _money0 integer, _quit0 boolean,
        _uid1 varchar, _money1 integer, _quit1 boolean,
        _uid2 varchar, _money2 integer, _quit2 boolean,
        _rounds integer) returns void as $BODY$
            begin

            insert into pref_games (rounds) values (_rounds);

            -- XXX how do I get the _gid of this new game?

            insert into pref_scores (uid, gid, money, quit)
                values(_uid0, _gid, _money0, _quit0);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid1, _gid, _money1, _quit1);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid2, _gid, _money2, _quit2);
            end;
    $BODY$ language plpgsql;

Thank you! I've listed few more details at
http://stackoverflow.com/questions/7899995/save-scores-of-3-players-per-game-into-postgresql

Regards
Alex