I need to create a python UDF(user-defined function) in redshift, that would be called in some other procedure. This python UDF takes two date values and compares those dates within the given start and end date, and check for the occurrence of these intermediate dates in some list.
This list needs to collect it's values from another table's column. Now the issue is, python UDF are defined in plpythonplu language and they don't recognize any sql. What should I do to make this list out of the table's column?
This is my function:
create or replace function test_tmp (ending_date date, starting_date date)
returns integer
stable
as $$
def get_working_days(ending_date , starting_date ):
days=0
if start_date is not None and end_date is not None:
for n in range(int ((ending_date - starting_date).days)):
btw_date= (start_date + timedelta(n)).strftime('%Y-%m-%d')
if btw_date in date_list:
days=days+1
return days
return 0
return get_working_days(ending_date,starting_date)
$$ language plpythonu;
Now, I need to create this date_list as something like:
date_list = [str(each["WORK_DATE"]) for each in (select WORK_DATE from public.date_list_table).collect()]
But, using this line in the function obviously gives an error, as select WORK_DATE from public.date_list_table is SQL.
Following is the structure of table public.date_list_table:
CREATE TABLE public.date_list
(
work_date date ENCODE az64
)
DISTSTYLE EVEN;
Some sample values for this table (actually this table stores only the working days values for the entire year):
insert into date_list_table values ('2021-07-01'),('2021-06-30'),('2021-06-29');
