-- 総タイトル数を取得し更新する関数
-- DROP FUNCTION f_upd_stockupddate (integer);

CREATE OR REPLACE FUNCTION f_upd_stockupddate(integer) RETURNS integer AS $$
DECLARE
    _count  integer;
    _timestamp timestamp;
    i  integer;
    _daycount integer;
    _s varchar(4000);
BEGIN

    -- 日時設定
    i := 0;

    -- 商品の更新日時の最新日時を取得
    SELECT MAX (basic.stock_upddate) INTO _timestamp FROM t_item_basic_attr basic
      INNER JOIN t_item_web_attr web ON basic.itemcd = web.itemcd AND web.delflg = FALSE AND releasedate <= CURRENT_DATE
    WHERE basic.delflg=FALSE;

    LOOP
        -- DVD件数チェック （中古、在庫有）
        _s := 'SELECT count(basic.itemcd)
            FROM t_item_basic_attr basic
            INNER JOIN t_item_web_attr web
              ON basic.itemcd = web.itemcd
              AND web.delflg = FALSE AND releasedate <= CURRENT_DATE
            WHERE basic.stock_upddate >= date \'' || _timestamp || '\' -' || i || '
               AND basic.delflg=FALSE AND basic.oldlowprice > 0;';

        EXECUTE _s  INTO _count;

        -- 10件以上、$1+10日前まで
        IF 10 <= _count THEN
            EXIT;
        END IF;
        IF i >= ($1 + 10) THEN
            EXIT;
        END IF;

        -- 1日ずつ
        i := i + 1;
    END LOOP;

    -- DVDの更新日と現在日の差分を取得
    SELECT date_part('day', CURRENT_TIMESTAMP - _timestamp) INTO _daycount;

    UPDATE t_systeminfo SET dvd_stockupddate_before = _daycount+1 + i
     WHERE validflg IS TRUE;
    RAISE NOTICE 'stockupddate_before =  % ', _daycount+1  + i;

    RETURN 1;
END;
$$ LANGUAGE plpgsql;