Greetings!
I am trying to write a function that will generate test requests as
input for another program. The function will be called every ten
minutes. It will read data from a table and generate requests for each
record in the source table in turn. When it runs out of records in the
source table, it will go back to the beginning of the source table.
The function will get the lowest value larger than a given number from a
column. If the given number is larger than any value in that column,
then the function should get the lowest number in that column. The
number that is retrieved is stored in a one-row table for use the next
time the function is called.
Here is my function:
CREATE OR REPLACE FUNCTION add_test_request()
RETURNS integer AS
$BODY$
declare
LastIPChargeNum integer;
LastHSCSChargeNum integer;
NextIPChargeNum integer;
NextHSCSChargeNum integer;
begin
raise notice 'Running add_test_request()';
select into LastIPChargeNum, LastHSCSChargeNum last_ip_charge,
last_hscs_charge from model_scheduler_test;
select into NextIPChargeNum min(charge) from charge where status =
'Done' and charge > LastIPChargeNum;
if not found then
select into NextIPChargeNum min(charge) from charge where status
= 'Done';
end if;
select into NextHSCSChargeNum min(charge) from feedback
where charge > LastHSCSChargeNum;
if not found then
select into NextHSCSChargeNum min(charge) from feedback;
end if;
insert into model_request (charge, run_date, heating, cooling)
values (NextIPChargeNum, current_timestamp, 0, 0);
insert into model_request (charge, run_date, heating, cooling)
values (NextHSCSChargeNum, current_timestamp, 1, 1);
update model_scheduler_test set last_ip_charge = NextIPChargeNum,
last_hscs_charge = NextHSCSChargeNum;
return 1;
end;
When the values stored in model_scheduler_test are higher than anything
in the charge columns of charge or feedback, the NextIPChargeNum and
NextHSCSChargeNum end up being null. If the values stored in
model_scheduler_test are low, the function works well.
What piece of sheer idiocy am I missing?
Thanks for your help!
RobR
