Thank you for your comments!
I have switched to SQL function now
(I didn't realize it is better performancewise) -
I have switched to SQL function now
(I didn't realize it is better performancewise) -
CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
RETURNS TABLE(
out_gid integer,
out_created 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,
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
UNION SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::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;
$func$ LANGUAGE sql;
RETURNS TABLE(
out_gid integer,
out_created 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,
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
UNION SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::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;
$func$ LANGUAGE sql;