Function Hangs
От | Patrick Hatcher |
---|---|
Тема | Function Hangs |
Дата | |
Msg-id | OFBD357E75.ECE08C45-ON88256B5F.0002F097@fds.com обсуждение исходный текст |
Ответы |
Re: Function Hangs
Re: Function Hangs |
Список | pgsql-novice |
I created the following function below. It hangs when I need to do the table update. Originally I wrote this function in MS SQL using cursors. However reading the documentation, I couldn't figure out how to loop through a cursor so I tried the FOR...LOOP instead. Any help would be appreciated. TIA CREATE FUNCTION "removeduppchange"() RETURNS "int4" AS ' DECLARE v_prevProd int4; v_count int4; myRec RECORD; BEGIN v_prevProd := 0; v_count :=0; FOR myRec IN -- Find all duplicate records that are still valid according to date Select o.keyf_products ,o.keyp_priceschedule from ozpricing o, ( Select keyf_products from ozpricing group by keyf_products having count(*) >1 ) a where o.keyf_products =a.keyf_products and o.keyf_products =76 and (date_end >= ''now'' and date_start <= ''now'') order by keyf_products,date_start desc LOOP --If we find that the previous keyF matches the current keyF then lets mark it. if myRec.keyf_products = v_prevProd then begin /* PROBLEM HAPPENS HERE. If I remove update statement, I get the correct record count of how many records should be marked */ update ozpricing set useascurrprice = 0 where keyp_priceschedule = myRec.keyp_priceschedule; v_count = v_count + 1; end; end if; v_prevProd := myRec.keyf_products; END LOOP; RETURN v_count; END; ' LANGUAGE 'plpgsql'; Patrick Hatcher Macys.Com Legacy Integration Developer 415-932-0610 office
В списке pgsql-novice по дате отправления: