Reusing query result in sub queries

Question

I have this ugly query....

sum(CASE 
        WHEN effective_from_date < '2011-05-24' THEN (rate * (effective_to_date - '2011-05-24' + 1)) 
        WHEN effective_to_date > '2011-05-28' THEN (rate * ('2011-05-28' - effective_from_date + 1)) 
        ELSE (rate * (effective_to_date - effective_from_date + 1))
    END
    ) as price_cal_rate
        FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28')) 
         AND 
         NOT EXISTS (

         SELECT days_diff FROM (


        SELECT  ((effective_from_date - lag(effective_to_date) OVER (PARTITION BY NULL ORDER BY effective_from_date ASC))) AS days_diff, effective_from_date, effective_to_date
             FROM calendar_event
             WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-26' AND '2011-05-28') OR (effective_to_date BETWEEN '2011-05-26' AND '2011-05-28')) 


        ) AS t WHERE COALESCE(days_diff, 0) > 1 

        ) AND EXISTS (select * from  (
          select min(effective_from_date) as min_date, max(effective_to_date) as max_date FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') AND 
        ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))
        ) as max_min WHERE min_date <= '2011-05-24' and max_date >= '2011-05-28')

the query is calculating the rate over a date range....the query is fine...but there is a lot of duplication in the query....I was wondering if there is a nice way to store the result of this sub query somewhere

FROM calendar_event
        WHERE property_rid = (SELECT rid FROM property WHERE web_id = 'T28314') 
AND 
            ((effective_from_date BETWEEN '2011-05-24' AND '2011-05-28')  OR (effective_to_date BETWEEN '2011-05-24' AND '2011-05-28'))

and use it throughout my query....

It may be work for nothing, but it's worth a try. pgsql is pretty good at optimizing what looks like a lot of repetition into a single call when it runs. I'd definitely compare the explain analyze output of both queries to see if you've made it any faster when you've either made a temp table or used a WITH clause. Look up WITH, it's pretty much a temp table that isn't made into a table for your query — Scott Marlowe
– Scott Marlowe, Commented May 8, 2011 at 14:28

Eelke · Accepted Answer · 2011-05-08 05:46:52Z

5

You can use a temp table as "mu is too short" suggested but if you only need the result in a single "main" query and you are using PostgreSQL 8.4 or higher you can also use with queries

answered May 8, 2011 at 5:46

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reusing query result in sub queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related