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.