triggers and plpgsql

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)

Responses

Browse pgsql-general by date

  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