CODEWITHSUNDEEP
pythonamazon-redshiftuser-defined-functions
Sachiko
Sachiko

Reputation: 924

How to trim variable in Redshift UDF

Thanks to advices here, now I can check the date value by Redshift UDF, How to check if '20210228' is a valid date? - stackoverflow.com.

To exclude null or '', I added 'if x1 is None or x1 == '' : return False', and it works. But still I need to exclude space ' ' -- currently it returns true, even though it's not correct date value.

If I trim target value outside UDF, it returns false, as expected. Seems I can't use trim in UDF like 'if trim(x1) is None or trim(x1) == '' : return False'. Or can't use length function like 'len(x1) > 0'.

I'd be grateful if you give me further advice to improve following UDF. Thank you in advance.

--example
select f_isdate('2021-03-01');  --> returns true, since it's correct date string
select f_isdate('');  --> returns false, as expected
select f_isdate('0');  --> returns false, as expected

select f_isdate(' ');  --> returns true, as NOT expected
select f_isdate(trim(' '));  --> returns false, if the target value is trimmed

-- revised UDF
CREATE OR REPLACE FUNCTION f_isdate (x1 varchar) 
  returns bool
IMMUTABLE 
as $$
    if x1 is None or x1 == '' : return False
    from dateutil.parser import parse;
    try:
        parse(x1,ignoretz=True);
    except:
        return 1==2;       
    else:
        return 1==1;
$$ language plpythonu;

Upvotes: 0

Views: 165

Answers (1)

fernolimits
fernolimits

Reputation: 416

I have tested all the cases and it works:

CREATE OR REPLACE FUNCTION f_isdate (x1 varchar) 
  returns bool
IMMUTABLE 
as $$
  if x1 is None or x1.strip() == "": 
    return False
  from dateutil.parser import parse
  try:
    parse(x1.strip(), ignoretz=True)
    return True
  except:
    return False       
$$ language plpythonu;

Upvotes: 1

Related Questions