DROP FUNCTION refresh_prices();
CREATE OR REPLACE FUNCTION refresh_prices() RETURNS int AS '

    DECLARE
        event destinations_event%ROWTYPE;
        minpromoprice pricelists_promoprices%ROWTYPE;
        minprice destinations_eventprices%ROWTYPE;

    BEGIN


        FOR event IN SELECT * FROM destinations_event
        LOOP
            SELECT INTO minpromoprice pp.* FROM pricelists_promoprices pp, taxonomy_taxonomy tt, pricelists_promotion ppro
                WHERE pp.service_id = tt.id AND tt.dictionary_id = 38
                AND pp.event_id = ppro.id id AND ppro.begin <= CURRENT_TIME AND ppro.end >= CURRENT_TIME
                    AND pp.event_id IN
                        (SELECT id FROM pricelists_promotion
                            WHERE id IN
                                (SELECT promotion_id FROM destinations_eventpromotions WHERE event_id = event.id)
                                OR  id IN (SELECT dipro.promotion_id FROM destinations_itinerarypromotions dipro, destinations_eventitinerary de
                                        WHERE dipro.itinerary_id = de.itinerary_id AND de.id =  event.id)
                                OR  id IN (SELECT dspro.promotion_id FROM destinations_shippromotions dspro, destinations_eventitinerary de
                                        WHERE dspro.ship_id = de.ship_id AND de.id =  event.id)
                                OR  id IN (SELECT dsopro.promotion_id FROM destinations_shipownerpromotions dsopro, destinations_eventitinerary de
                                        WHERE dsopro.shipowner_id = de.shipowner_id AND de.id = event.id))
                ORDER BY price LIMIT 1
            ;

            SELECT INTO minprice * FROM destinations_eventprices dep, taxonomy_taxonomy tt
                                    WHERE dep.event_id = event.id AND dep.service_id = tt.id AND tt.dictionary_id = 38 AND price > 0
                                    ORDER BY dep.price LIMIT 1
            ;

            UPDATE destinations_event
                SET min_price                   = minprice.price,
                    min_price_currency_id       = minprice.currency_id,
                    min_promo_price             = minpromoprice.price,
                    min_promo_price_currency_id = minpromoprice.currency_id,
                    promotion_id                = minpromoprice.event_id
                WHERE id = event.id
            ;
        END LOOP;

        RETURN 1;
    END;

' LANGUAGE 'plpgsql';