[pgsql-jp: 25122] Re: TRIGGER 作成でエラー

Tsukasa Koizumi tsukasa @ koiz.com
2002年 3月 7日 (木) 12:43:12 JST


小泉です。

At 18:18 02/03/06 +0900, you wrote:
>At 17:49 02/03/06 +0900, you wrote:
>>tsukasa> それにしてもSQLが使えないとは…ショック。
>>tsukasa> 実はちゃんと create function するのは初めてなのです。
>>tsukasa> なんとかPL/pgSQLで書き直してみます。。。
>>
>>RULEにして今使ってる関数を動作させるって言うのはどうでしょう?
>>今回のケースでうまくいくか分からないですけど。
>
>はい。PL/pgSQLのドキュメントを見ていてちょっと不安な記述があったので、
>ただいまRULEチャレンジ中です……。
>上手く動きそうな気がしてます。(根拠は無いのですが ……(^^;)

結局、トリガー動作が「AFTER」でなければならないのでRULEはボツ。
PL/pgSQLで関数を書き直しました。(書き直すってほどじゃないですね)
これで問題なく動作しています。ありがとうございました。>ALL

ところで。PL/pgSQLについて、次のような記述があるのですが、ちょっと気
になっています。

「PL/pgSQLはこのように実行プランを保存するので、直接 PL/pgSQL 関数内 
に現れる問い合わせはあらゆる実行において同じテーブルとフィールドを 
参照するものでなければいけません。つまり、問い合わせ中でテーブルや 
フィールド名を引数として使用することはできません。」

これは、記述したSQL文に出てくるテーブル、カラムなどすべてを、OIDで参
照しているから、関数内に名前が出てくるテーブルを作り直したりすると、
関数〜トリガーも作り直さねばならない……ということでしょうか?


↓書き直したもの
-----------------------------------------------------------------------
CREATE FUNCTION calc_i_status_new ()
RETURNS opaque AS '
BEGIN
DELETE FROM items_status WHERE id = new.item;
INSERT INTO items_status 
SELECT *, now() AS stamp FROM
    -- items + ( item_category + categories )
    ( SELECT * FROM
        -- items
        ( SELECT id,code,name,saleprice,costprice FROM items WHERE id = new.item ) AS i
    LEFT OUTER JOIN
        -- item_category + categories
        ( SELECT id,"group",element,name AS elname FROM 
            ( SELECT * FROM item_category WHERE id = new.item ) AS xic
        LEFT OUTER JOIN 
            categories USING ( "group", element ) ) AS c
    USING ( id ) ) AS ic
LEFT OUTER JOIN
    -- carried in + carried out + carried adj + in + out + will out + adj
    ( SELECT * FROM
        -- carried in + carried out + carried adj
        ( SELECT * FROM
            -- from carried in + carried out
            ( SELECT * FROM
                -- carried in
                ( SELECT item,SUM( quant ) AS carried_in FROM saledetails WHERE sales = 101 AND stamp < date_trunc( ''month'', now() ) AND item = new.item GROUP BY item ) AS sd
            FULL OUTER JOIN
                -- carried out
                ( SELECT item,( SUM( quant ) - SUM( cancel ) ) AS carried_out FROM saledetails WHERE sales > 10000000 AND delivinfo AND stamp < date_trunc( ''month'', now() ) AND item = new.item GROUP BY item ) AS co
            USING ( item ) ) AS cico
        FULL OUTER JOIN
            -- carried adj
            ( SELECT item,SUM( quant ) AS carried_adj FROM saledetails WHERE sales = 201 AND stamp < date_trunc( ''month'', now() ) AND item = new.item GROUP BY item ) AS ca
        USING ( item ) ) AS cicoca
    FULL OUTER JOIN
        -- in + out + will out + adj
        ( SELECT * FROM
            -- in + out + will out
            ( SELECT * FROM
                -- in + out
                ( SELECT * FROM
                    -- in
                    ( SELECT item,SUM( quant ) AS "in" FROM saledetails WHERE sales = 101 AND stamp >= date_trunc( ''month'', now() ) AND item = new.item GROUP BY item ) AS ni
                FULL OUTER JOIN
                    -- out
                    ( SELECT item,( SUM( quant ) - SUM( cancel ) ) AS "out" FROM saledetails WHERE sales > 10000000 AND delivinfo AND stamp >= date_trunc( ''month'', now() ) AND item = new.item GROUP BY item ) AS no
                USING ( item ) ) AS nino
            FULL OUTER JOIN
                -- adj
                ( SELECT item,SUM( quant ) as "adj" FROM saledetails WHERE sales = 201 AND stamp >= date_trunc( ''month'', now() ) AND item = new.item GROUP BY item ) AS aj
            USING ( item ) ) AS ninoaj
        FULL OUTER JOIN
            -- will out
            ( SELECT item,( SUM( quant ) - SUM( cancel ) ) AS "will_out" FROM saledetails WHERE sales > 10000000 AND ( NOT delivinfo ) AND item = new.item GROUP BY item ) AS ninoajwo
        USING ( item ) ) AS ninowo
    USING ( item ) ) AS stocks
ON id = item 
WHERE id >= 10000;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION calc_i_status_old ()
RETURNS opaque AS '
BEGIN
DELETE FROM items_status WHERE id = old.item;
INSERT INTO items_status 
SELECT *, now() AS stamp FROM
    -- items + ( item_category + categories )
    ( SELECT * FROM
        -- items
        ( SELECT id,code,name,saleprice,costprice FROM items WHERE id = old.item ) AS i
    LEFT OUTER JOIN
        -- item_category + categories
        ( SELECT id,"group",element,name AS elname FROM 
            ( SELECT * FROM item_category WHERE id = old.item ) AS xic
        LEFT OUTER JOIN 
            categories USING ( "group", element ) ) AS c
    USING ( id ) ) AS ic
LEFT OUTER JOIN
    -- carried in + carried out + carried adj + in + out + will out + adj
    ( SELECT * FROM
        -- carried in + carried out + carried adj
        ( SELECT * FROM
            -- from carried in + carried out
            ( SELECT * FROM
                -- carried in
                ( SELECT item,SUM( quant ) AS carried_in FROM saledetails WHERE sales = 101 AND stamp < date_trunc( ''month'', now() ) AND item = old.item GROUP BY item ) AS sd
            FULL OUTER JOIN
                -- carried out
                ( SELECT item,( SUM( quant ) - SUM( cancel ) ) AS carried_out FROM saledetails WHERE sales > 10000000 AND delivinfo AND stamp < date_trunc( ''month'', now() ) AND item = old.item GROUP BY item ) AS co
            USING ( item ) ) AS cico
        FULL OUTER JOIN
            -- carried adj
            ( SELECT item,SUM( quant ) AS carried_adj FROM saledetails WHERE sales = 201 AND stamp < date_trunc( ''month'', now() ) AND item = old.item GROUP BY item ) AS ca
        USING ( item ) ) AS cicoca
    FULL OUTER JOIN
        -- in + out + will out + adj
        ( SELECT * FROM
            -- in + out + will out
            ( SELECT * FROM
                -- in + out
                ( SELECT * FROM
                    -- in
                    ( SELECT item,SUM( quant ) AS "in" FROM saledetails WHERE sales = 101 AND stamp >= date_trunc( ''month'', now() ) AND item = old.item GROUP BY item ) AS ni
                FULL OUTER JOIN
                    -- out
                    ( SELECT item,( SUM( quant ) - SUM( cancel ) ) AS "out" FROM saledetails WHERE sales > 10000000 AND delivinfo AND stamp >= date_trunc( ''month'', now() ) AND item = old.item GROUP BY item ) AS no
                USING ( item ) ) AS nino
            FULL OUTER JOIN
                -- adj
                ( SELECT item,SUM( quant ) as "adj" FROM saledetails WHERE sales = 201 AND stamp >= date_trunc( ''month'', now() ) AND item = old.item GROUP BY item ) AS aj
            USING ( item ) ) AS ninoaj
        FULL OUTER JOIN
            -- will out
            ( SELECT item,( SUM( quant ) - SUM( cancel ) ) AS "will_out" FROM saledetails WHERE sales > 10000000 AND ( NOT delivinfo ) AND item = old.item GROUP BY item ) AS ninoajwo
        USING ( item ) ) AS ninowo
    USING ( item ) ) AS stocks
ON id = item 
WHERE id >= 10000;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trig_calc_i_status_new
AFTER INSERT OR UPDATE ON saledetails FOR EACH ROW
EXECUTE PROCEDURE calc_i_status_new ();

CREATE TRIGGER trig_calc_i_status_old
AFTER DELETE ON saledetails FOR EACH ROW
EXECUTE PROCEDURE calc_i_status_old ();



----------------------------------------------------------------------
小泉 司@コイズコム デジタルワークス(東京都文京区)
Desk - mailto:tsukasa @ koiz.com / Mobile - mailto:pigtail @ pdx.ne.jp
PGP Public Key - http://www.koiz.com/~tsukasa/PGP_KEY/tsukasa.asc
----------------------------------------------------------------------





pgsql-jp メーリングリストの案内