Function Hangs

Поиск
Список
Период
Сортировка
От Patrick Hatcher
Тема Function Hangs
Дата
Msg-id OFBD357E75.ECE08C45-ON88256B5F.0002F097@fds.com
обсуждение исходный текст
Ответы Re: Function Hangs  ("Josh Berkus" <josh@agliodbs.com>)
Re: Function Hangs  ("Josh Berkus" <josh@agliodbs.com>)
Список 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 по дате отправления:

Предыдущее
От: "Brett W. McCoy"
Дата:
Сообщение: Re: varchar vs char vs text
Следующее
От: "Josh Berkus"
Дата:
Сообщение: Re: Function Hangs