The following bug has been logged on the website:
Bug reference: 18656
Logged by: Alexander Alehin
Email address: panso8@gmail.com
PostgreSQL version: 16.4
Operating system: Debian 11
Description:
CREATE TABLE stbl_states(
id int primary key,
state text
);
CREATE TABLE stbl_logs(
ts timestamp default clock_timestamp(),
log text
);
CREATE or replace FUNCTION stbl_get_state(p_id int) RETURNS text
LANGUAGE sql STABLE AS
$$
select coalesce(k.state, 'off') from stbl_states k where k.id = p_id;
$$;
CREATE or replace PROCEDURE stbl_log(p_message text)
LANGUAGE plpgsql AS
$$
begin
insert into stbl_logs(log) values (p_message);
end;
$$;
CREATE or replace PROCEDURE stbl_update_state()
LANGUAGE plpgsql AS
$$
declare
s text;
begin
update stbl_states set state='on' where id=1;
s := stbl_get_state(1);
call stbl_log(s);
call stbl_log(stbl_get_state(1));
insert into stbl_logs(log) values (stbl_get_state(1));
exception when others then
s := SQLERRM;
call stbl_log(s);
end;
$$;
insert into stbl_states(id) values(1);
call stbl_update_state();
select log from stbl_logs order by ts;
---------------------------------------------------------------
log
-----
on
off
on
(3 rows)
But expected:
on
on
on
If "STABLE" or "exception ..." is removed, it will work as expected.
