[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 メーリングリストの案内