Re: Selecting records with highest timestamp - for a join - Mailing list pgsql-general

On 10/19/2016 11:35 AM, Alexander Farber wrote:
> Good evening,
>
> I have a question please on which kind of statement to use -
>
> In a table I store user info coming from social networks (Facebook,
> Twitter, ...):
>
> CREATE TABLE words_social (
>         sid varchar(255) NOT NULL,
>
>         social integer NOT NULL CHECK (0 <= social AND social <= 6),
>         female integer NOT NULL CHECK (female = 0 OR female = 1),
>         given  varchar(255) NOT NULL CHECK (given ~ '\S'),
>         family varchar(255),
>         photo  varchar(255) CHECK (photo ~* '^https?://...'),
>         place  varchar(255),
>         stamp  integer NOT NULL,  /* HOW TO USE THE LATEST stamp? */
>
>         uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
>         PRIMARY KEY(sid, social)
> );
>
> I.e. a user can have several records in the above table, but I always
> use the most recent one (the one with the highest "stamp") to display
> that user in my game.
>
> Then I use a custom function to retrieve current games info for a
> particular user:
>
> CREATE OR REPLACE FUNCTION words_get_games(in_uid integer)
>         RETURNS TABLE (
>                 out_gid integer,
>                 out_created integer,
>                 out_finished integer,
>                 out_player1 integer,
>                 out_player2 integer,
>                 out_played1 integer,
>                 out_played2 integer,
>                 out_score1 integer,
>                 out_score2 integer,
>                 out_hand1 text,
>                 out_hand2 text,
>                 out_letters varchar[15][15],
>                 out_values integer[15][15],
>                 out_bid integer,
>                 out_last_tiles jsonb,
>                 out_last_score integer
>         ) AS
> $func$
>         SELECT
>                 g.gid,
>                 EXTRACT(EPOCH FROM g.created)::int,
>                 EXTRACT(EPOCH FROM g.finished)::int,
>                 g.player1,
>                 g.player2, -- can be NULL
>                 EXTRACT(EPOCH FROM g.played1)::int,
>                 EXTRACT(EPOCH FROM g.played2)::int,
>                 g.score1,
>                 g.score2,
>                 ARRAY_TO_STRING(g.hand1, ''),
>                 REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g'),
>                 g.letters,
>                 g.values,
>                 g.bid,
>                 m.tiles,
>                 m.score
>         FROM words_games g LEFT JOIN words_moves m USING(mid)
>         WHERE g.player1 = in_uid
>         AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
> INTERVAL '1 day')
>         UNION SELECT
>                 g.gid,
>                 EXTRACT(EPOCH FROM g.created)::int,
>                 EXTRACT(EPOCH FROM g.finished)::int,
>                 g.player2,
>                 g.player1, -- can not be NULL
>                 EXTRACT(EPOCH FROM g.played2)::int,
>                 EXTRACT(EPOCH FROM g.played1)::int,
>                 g.score2,
>                 g.score1,
>                 ARRAY_TO_STRING(g.hand2, ''),
>                 REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g'),
>                 g.letters,
>                 g.values,
>                 g.bid,
>                 m.tiles,
>                 m.score
>         FROM words_games g LEFT JOIN words_moves m USING(mid)
>         WHERE g.player2 = in_uid
>         AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP -
> INTERVAL '1 day');
>
> $func$ LANGUAGE sql;
>
> I would like to extend the above custom function, so that user info
> (given and last names, photo) is returned too.
>
> How to approach this problem please, should I use CTE for this?

For player1, player2 or both?

Since you are returning a table from words_get_games() you can
experiment by joining it's output to words_social.

>
> Thank you for any hints
> Alex
>


--
Adrian Klaver
adrian.klaver@aklaver.com