CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftuser-defined-functions
mani bharataraju
mani bharataraju

Reputation: 162

Redshift UDF logical issue

I am trying to write a redshift udf to validate timestamp. But, it always returns false. Can some explain why?

create or replace function f_Is_timestamp_sql(VARCHAR(20000))
  returns timestamp  
  STABLE
as $$
       select $1::timestamp as a;
$$ language sql;

create or replace function f_Is_timestamp(val VARCHAR(20000))
  returns bool
IMMUTABLE 
as $$
    try:
       (f_Is_timestamp_sql(val));  
    except:
       return (1==2);
    else:
      return 1==1;
$$ language plpythonu;

select f_Is_timestamp('2019-10-09')

Upvotes: 3

Views: 987

Answers (1)

mani bharataraju
mani bharataraju

Reputation: 162

After reading thru the AWS documents, I figured out that a UDF cannot reference the contents of another UDF. https://docs.aws.amazon.com/redshift/latest/dg/udf-python-language-support.html Therefore, my function always throws an exception. I figured out an alternative way to accomplish this using python library

dateutil.parser

Working function below.

create or replace function f_Is_timestamp(val VARCHAR(20000))
  returns bool
IMMUTABLE 
as $$
    from dateutil.parser import parse;
    try:
        parse(val,ignoretz=True);
    except:
        return 1==2;
    else:
        return 1==1;
$$ language plpythonu;

Upvotes: 3

Related Questions