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)
