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

Greg Stark wrote:

>Ron St-Pierre <rstpierre(at)syscor(dot)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