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

[Tsukasa Koizumi]

小泉と申します。

CREATE TRIGGERがエラーになってしまいます。
SQL文は下記のようなものです。

作成したいこのTRIGGERは、ECシステムの在庫統計情報（items_status）を、
受注情報（saledetails）が更新された段階で、自動的に更新するための物で
す。

CREATE FUNCTIONは成功しており、関数calc_i_status()は正常に機能しています。
が、TRIGGERを設定しようとすると

    ERROR:  CreateTrigger: function calc_i_status() does not exist

となります。

いろいろ調べてみたのですが、どうにも原因がよくわかりません。
ご教授いただけると助かります。

【環境】
RedHat 6.2
PostgreSQL 7.1.3

よろしくお願いします。

----------------------------------------------------------------------
CREATE FUNCTION calc_i_status ( int4 )
RETURNS bool AS 
'DELETE FROM items_status WHERE id = $1;
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 = $1 ) AS i
    LEFT OUTER JOIN
        -- item_category + categories
        ( SELECT id,"group",element,name AS elname FROM 
            ( SELECT * FROM item_category WHERE id = $1 ) 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 = $1 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 = $1 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 = $1 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 = $1 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 = $1 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 = $1 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 = $1 GROUP BY item ) AS ninoajwo
        USING ( item ) ) AS ninowo
    USING ( item ) ) AS stocks
ON id = item 
WHERE id >= 10000;
SELECT TRUE;'
LANGUAGE 'sql';

CREATE TRIGGER trig_calc_i_status
AFTER INSERT OR UPDATE OR DELETE ON saledetails FOR EACH ROW
EXECUTE PROCEDURE calc_i_status ( item);







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