Hi,
I have a weird behavior when i package some sql code in a function, the performance deteriorate. The same code running directly in select statement is blinking fast. ex:
*select date_to_str("Expiry_Date",'%Y-%m-%d') from table*: exec time is 0.72s
if i create a function: *CREATE FUNCTION DayBracket(pdate date) returns char(10)* *BEGIN* * return date_to_str(pdate,'%Y-%m-%d');* *end;* *grant execute on function DayBracket to public;*
and execute *select DayBracket("Expiry_Date") from table*: exec time is 24s
even when i set the limit to 1 its taking the same time, so i guess it should be something related to loading the function.
The following function perform much better: *CREATE FUNCTION DayBracketOpt(pdate date) returns char(10)* *BEGIN* * return extract(year from pdate) || '-' || lpad(extract(month from pdate),2,'0') || '-' || lpad(dayofmonth(pdate),2,'0');* *end;* *grant execute on function DayBracketOpt to public;*
I also tried to create a Python function: *CREATE FUNCTION pyDayBracket(pdate date) returns string* *LANGUAGE PYTHON {* * from time import strftime, strptime* * return [strftime("%Y-%m-%d", strptime(pdt,"%Y-%m-%d")) for pdt in pdate]* *};* it is taking too long and throwing an exception:buffer size mismatch PS: the pdate array type is detected in pythons as string not as date;
Also how to delete a function with dependency, I need to replace some functions which are used in others functions and views. Any flags to turn off dependency check before dropping?
Thank you.