Good afternoon,
is it please posible to optimize the following SQL query with numerous CASE statements (on same condition!) without switching to PL/pgSQL?
SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int,
EXTRACT(EPOCH FROM g.finished)::int,
g.letters,
g.values,
g.bid,
m.tiles,
m.score,
/* HOW TO OPTIMIZE THE FOLLOWING CASE STATEMENTS? */
CASE WHEN g.player1 = in_uid THEN g.player1 ELSE g.player2 END,
CASE WHEN g.player1 = in_uid THEN g.player2 ELSE g.player1 END,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = in_uid THEN g.played1 ELSE g.played2 END)::int,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = in_uid THEN g.played2 ELSE g.played1 END)::int,
[...]
When seeing the above repetition I consider implementing a composite type and passing that around in the main portion of the queries and then (composite_type).* at the presentation layer.
As Geoff
indicated normalization makes this a bit easier; but you can still normalize "on-the-fly" via standalone composite types.
David J.
