By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Nested use of found doesn't seem to be working

P: 1
I have a simple plpgsql function that loops using a cursor and performs inserts/updates on a couple of tables. When I run the SQL piecemeal in pgAdmin's SQL editor everything looks OK. However, the IF statement that uses FOUND within the loop seems to always false. Any constructive insights would be appreciated.

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.    dcursor CURSOR (tdate date) IS SELECT * FROM recon.sp_ubsdaily_asof(tdate) AS u WHERE u.maturity_date >= tdate AND u.qty <> 0
  3.       AND NOT EXISTS(SELECT 1 FROM recon.ubs_daily_portfolio AS p WHERE u.serial_number = p.serial_number);
  4.    udaily recon.ubsdaily%ROWTYPE; 
  5.    umark recon.ubsmark%ROWTYPE;
  6.    ubscount bigint;
  7.  
  8. BEGIN
  9.    SELECT COUNT(*) INTO ubscount FROM recon.ubs_daily_portfolio;
  10.  
  11.    IF ubscount = 0 THEN
  12.       EXIT;
  13.    END IF;
  14.  
  15.    OPEN dcursor(asof);
  16.  
  17.    LOOP
  18.       FETCH dcursor INTO udaily;
  19.       EXIT WHEN NOT FOUND;
  20.  
  21.       IF (udaily.modified_date <> asof) THEN
  22.          INSERT INTO recon.ubsdaily (serial_number, modified_date, trade_date, maturity_date, deal_price, strike, trade_type, market,
  23.             product_code, product_name, acct_number, qty, ccy_code, strategy, tp_id, arcim_id)
  24.             VALUES(udaily.serial_number, asof, udaily.trade_date, udaily.maturity_date, udaily.deal_price, udaily.strike, 
  25.             udaily.trade_type, udaily.market, udaily.product_code, udaily.product_name, udaily.acct_number, 0, udaily.ccy_code, 
  26.             udaily.strategy, udaily.tp_id, udaily.arcim_id);
  27.       ELSE 
  28.          UPDATE recon.ubsdaily SET qty = 0 WHERE id = udaily.id;
  29.       END IF;
  30.  
  31.       SELECT m.* INTO umark FROM recon.ubsmark AS m WHERE m.serial_number = udaily.serial_number 
  32.          AND m.mark_date = (SELECT MAX(mm.mark_date) FROM recon.ubsmark AS mm WHERE mm.serial_number = udaily.serial_number AND mm.mark_date <= asof);
  33.  
  34.       IF NOT FOUND THEN
  35.          INSERT INTO recon.ubsmark (serial_number, mark_date, mark, implied_pnl, variation_margin, premium_value, realized_pnl) 
  36.             VALUES(udaily.serial_number, asof, 0, 0, 0, 0, 0);
  37.       ELSE 
  38.  
  39.          IF (umark.mark_date = asof) THEN
  40.             UPDATE recon.ubsmark set implied_pnl = 0, variation_margin = 0, premium_value = 0, realized_pnl = umark.implied_pnl 
  41.                WHERE id = umark.id;
  42.          ELSE
  43.             INSERT INTO recon.ubsmark (serial_number, mark_date, mark, implied_pnl, variation_margin, premium_value, realized_pnl) 
  44.                VALUES(udaily.serial_number, asof, 0, 0, 0, 0, umark.realized_pnl);
  45.          END IF;
  46.  
  47.       END IF;
  48.  
  49.    END LOOP;
  50.  
  51.    CLOSE dcursor;
  52. END;
  53.  
Feb 16 '07 #1
Share this Question
Share on Google+
1 Reply


P: 1
Speculation here,
The Manual says that FOUND is a local variable initially set to False in PLPGsql functions. Maybe you can't access it inside the nested instance. Perhaps wrapping the nested part in a begin-end block of it's own will get you an instance
Apr 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.