Re: most idiomatic way to "update or insert"?

Greg Stark wrote:

>Ron St-Pierre <rstpierre@syscor.com> writes:
>
>
>
>>BTW these updates do take longer than we'd like so I would appreciate more
>>input on how this setup could be redesigned.
>>
>>
>
>Where is the input coming from?
>
>One option is to batch changes.
>
<snip>

>
>Something like
>
>update current_stock_price
>   set price = log.price,
>       timestamp = log.timestamp
>  from stock_price log
> where current_stock_price.stock = stock_price_log.stock
>   and stock_price_log.timestamp between ? and ?
>
>
We check for new stocks and add them, and initially were using a
procedure to do something similar to your code:

CREATE OR REPLACE FUNCTION updateData() RETURNS SETOF datatype AS '
    DECLARE
        rec     RECORD;
    BEGIN
        FOR rec IN SELECT symbol, tradeDate, tickDate, high, low , open,
close, volume FROM exchangedata LOOP
            RETURN NEXT rec;
            UPDATE stockdata SET high=rec.high, low=rec.low,
open=rec.low, close=rec.close, volume=rec.volume, tradeDate=rec.tradeDate
            WHERE symbol=rec.symbol;
        END LOOP;
        RETURN;
    END;
' LANGUAGE 'plpgsql';
... but it took too long. Off hand, do you know if your approach above
would be quicker?

Ron