From: | Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | triggers and plpgsql |
Date: | 2001-08-03 08:35:11 |
Message-ID: | Pine.LNX.4.33.0108031012120.22404-100000@u-pl0 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Fran Fabrizio | 2001-08-03 14:22:19 | Re: encrypting a password field in the database |
Previous Message | John Clark Naldoza y Lopez | 2001-08-03 00:40:52 | Re: Pgadmin |