The following bug has been logged online:
Bug reference: 1215
Logged by: Bob Henkel
Email address: bob@teamhenkel.com
PostgreSQL version: 8.0 Beta
Operating system: Windows XP Home SP1
Description: Call sql function from plpgsql results vary.
Details:
I was playing around seeing what new things I could do in stored procedures.
Here is the statment I'm using to get the issue.
select * from f_trap_error();
I expect the above statement to alway return 1 which it does.
The issue is I expect the trapped_error table to contain a seq id and than a
999 899
so the table should look like this if I ran select * from f_trap_error();
twice.
1 999
2 899
3 999
4 899
What I'm seeing is the first time I run select * from f_trap_error(); is
1 999
2 899
The second time I run select * from f_trap_error(); the table looks like
this
1 999
2 899
3 999
As you can see the 4th record never got inserted. This would be the insert
that is running in my sql stored procedure.
Here are the database objects I'm using to get this issue.
CREATE TABLE trapped_error
(
trapped_error_id serial NOT NULL,
error_code int8
)
WITHOUT OIDS;
-------------------------------
CREATE TABLE dual
(
x int2
)
WITHOUT OIDS;
---------------------------
insert into dual values(1);
---------------------------
CREATE OR REPLACE FUNCTION f_trap_error()
RETURNS int8 AS
$BODY$DECLARE
x integer;
BEGIN
x := 4;
x := x / 1;
insert into trapped_error(error_code) values(999);
select * from f_test_sql() into x;
RETURN 1;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
insert into trapped_error(error_code) values(2);
RETURN 2;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-----------------------------------
CREATE OR REPLACE FUNCTION f_test_sql()
RETURNS int2 AS
$BODY$
--SAVEPOINT my_savepoint;
insert into trapped_error(error_code) VALUES(899);
--ROLLBACK TO my_savepoint;
select * from dual;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
Let me know if I didn't explain something and if you can't reproduce this. I
will leave the database I created this untouched incase it can help shed
some light on something.
Thanks
Bob
