On Mon, Oct 31, 2016 at 5:53 AM, Alexander Farber < alexander.far...@gmail.com> wrote:
> 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.
