triggers and plpgsql

Поиск
Список
Период
Сортировка
От Jakub Ouhrabka
Тема triggers and plpgsql
Дата
Msg-id Pine.LNX.4.33.0108031012120.22404-100000@u-pl0
обсуждение исходный текст
Ответы Re: triggers and plpgsql  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: triggers and plpgsql  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
hi,
i'm getting strange results when executing the code below. i would expect
that li_count in function foo and the select after calling this function
should return same values. can anyone explain me why i'm getting these
results, please?

thanks
kuba

example (using 7.1.2):

CREATE TABLE TC01 (
TC01PK___ SERIAL PRIMARY KEY,
TC01CNT1_ INTEGER,
TC01CNT2_ INTEGER
);
CREATE TABLE TC02 (
TC02PK___ SERIAL PRIMARY KEY,
TC01PK___ INTEGER,
FOREIGN KEY (TC01PK___) REFERENCES TC01(TC01PK___)
);
CREATE FUNCTION on_delete_tc02() RETURNS OPAQUE AS '
DECLARE
BEGIN
    UPDATE TC01 SET TC01CNT2_ = TC01CNT2_ + 1 WHERE TC01PK___ =
OLD.TC01PK___;
    RETURN NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER on_delete_tc02 AFTER DELETE ON TC02 FOR EACH ROW EXECUTE
PROCEDURE on_delete_tc02();

INSERT INTO TC01 (
     TC01PK___
    ,TC01CNT1_
    ,TC01CNT2_
) VALUES (
     1
    ,2
    ,0
);

INSERT INTO TC02 (
     TC01PK___
) VALUES (
     1
);

INSERT INTO TC02 (
     TC01PK___
) VALUES (
     1
);

CREATE FUNCTION foo() RETURNS INTEGER AS '
DECLARE
    li_count INTEGER;
BEGIN
    DELETE FROM TC02;
    SELECT INTO li_count COUNT(*) FROM TC01 WHERE TC01CNT1_ = TC01CNT2_;
    RAISE NOTICE ''li_count = %'', li_count;
    RETURN 0;
END;
' LANGUAGE 'plpgsql';


tom=# begin;
BEGIN
tom=# select foo();
NOTICE:  li_count = 0
 foo
-----
   0
(1 row)

tom=# SELECT COUNT(*) FROM TC01 WHERE TC01CNT1_ = TC01CNT2_;
 count
-------
     1
(1 row)



В списке pgsql-general по дате отправления:

Предыдущее
От: John Clark Naldoza y Lopez
Дата:
Сообщение: Re: Pgadmin
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: triggers and plpgsql