Reduce Calculations in SELECT

From: "Carel Combrink" <s25291930(at)tuks(dot)co(dot)za>
To: "PostgreSQL Novice List" <pgsql-novice(at)postgresql(dot)org>
Subject: Reduce Calculations in SELECT
Date: 2010-08-31 14:16:46
Message-ID: 20100831161646.38933og62kfyft1q@student.up.ac.za
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-novice

Hi,

I have a SELECT statement that does the following (example only,
actual query more complex):
SELECT foo.id, mod(foo.one, foo.two)
FROM my_table AS foo
WHERE mod(foo.one, foo.two) > 2
AND mod(foo.one, foo.two) < 6;

Mod is an expensive operation and it is calculated 3 times for the
same set of inputs. How can I get it to only calculate it once and and
use the result in the WHERE clause and return the value of the mod?

For this test my_table looks as follow:
=> SELECT * from my_table;
id | one | two
----+-----+-----
1 | 5 | 6
2 | 1 | 5
3 | 9 | 3
(3 rows)

Thank you
--
Carel Combrink
s25291930(at)tuks(dot)co(dot)za

This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-08-31 14:21:58 Re: Function Returning Table/Record
Previous Message SUNDAY A. OLUTAYO 2010-08-31 14:12:03 Re: Django + Postgressql