Hi,
I tried calling the postgres function using the select query from the odbc stage as suggested by you, and it does run without any errors. But the output is not as expected. The same function gives correct results when I run it on postgres server. So the function is fine. Let me explain it in detail.
This is my function:
Code: Select all
CREATE OR REPLACE FUNCTION public.ctrl_run_check_proc(OUT param integer, OUT run_key integer)
RETURNS record
LANGUAGE plpgsql
AS $function$
declare
runn_date integer;
status varchar(50);
flag integer;
Begin
select run_date
from ctrl_run
where dw_run_key = (select MAX(dw_run_key) from ctrl_run) into runn_date;
select Status_Code
from ctrl_run
where dw_run_key = (select MAX(dw_run_key) from ctrl_run) into status;
IF (cast(to_char(current_date, 'YYYYMMDD') as integer) = runn_date ) Then
IF status ='Completed' or status ='Running' Then
flag:= 1;
ELSE
flag:= 2;
End If;
ElsIf (CAST(to_char(current_date, 'YYYYMMDD') as INTEGER) > runn_date) Then
flag := 3;
Else
flag := 4;
End If;
--Part 2
IF flag = 3 Then
param := 1;
insert into Ctrl_run(run_date,run_eff_dt,run_end_dt,run_type,status_code,dw_create_dtm,dw_updt_dtm) values
(
cast(to_char(current_date, 'YYYYMMDD') as Integer),
(select run_end_dt from Ctrl_run where DW_RUN_KEY = (select max(DW_Run_Key)
from Ctrl_Run where Status_Code = 'Completed')),
current_timestamp,
'Daily',
'Running',
current_timestamp,
current_timestamp
);
select MAX(dw_run_key) from ctrl_run into run_key ;
elsif flag = 2 Then
param := 1;
update ctrl_run set Status_Code = 'Running', DW_Updt_Dtm = current_timestamp
where dw_run_key = (select MAX(dw_run_key) from ctrl_run);
select MAX(dw_run_key) from ctrl_run into run_key ;
elsif flag in (1,4) Then
param := 0;
select MAX(dw_run_key) from ctrl_run into run_key;
End IF;
END;
$function$
So when the max run_date in the table is less than the current date and the status is completed, then a new row should be inserted and param should be set to 1.
Now when i run this function in postgres, it does as expected.
And when i run this from datastage, it gives me the output: param = 0 and run_key is some random value and not the max run_key + 1. Also no row gets inserted in the postgres table.
Eg:
If the data in Ctrl_run table is:
run_key Status
30 Completed
When i run the procedure, a new run_key should be inserted:
run_key Status
30 Completed
31 Running
But, it does not insert a row and when i collect the ouput in a sequential file, it shows me that run_key = 35
I hope you have understood my issue.
I am not able to understand from where is this run_key with the incremented value is getting populated. Also, why is the record not getting inserted into the database.
Thanks. I appreciate your help.