From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Selecting records with highest timestamp - for a join |
Date: | 2016-10-19 18:35:37 |
Message-ID: | CAADeyWgx4DEPrG4HJJxV4J-Wm8U4vY3QWCy-2__Jdgpit1vsAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thank you for any hints
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-10-19 18:51:47 | Re: Selecting records with highest timestamp - for a join |
Previous Message | Greg Sabino Mullane | 2016-10-19 18:03:39 | Re: pg_sample |