The following bug has been logged online:

Bug reference: 1215
Logged by: Bob Henkel

Email address: bob (AT) teamhenkel (DOT) 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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
I think the problem is you declared f_test_sql as IMMUTABLE, which
entitles the planner to execute it once and bind the result as a
constant. Functions with side-effects should *never* be marked
immutable (nor stable for that matter).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

That fixed it, it works now as I had hoped. Thanks again




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)