I'm trying to write a cast method for Redshift to convert to decimal if applicable. The second argument is the precision, but I'm not able to pass the second argument to the decimal() type. Is there a workaround for this?
decimal(18, $2) is causing an error.
create or replace function try_cast_decimal (varchar, integer)
returns decimal
stable
as $$
-- Match up to $2 decimal places
select case when $1 SIMILAR TO '-?[0-9]+(.[0-9]{0,' + CAST($2 AS varchar(3)) + '})?' then cast($1 as decimal(18, $2)) else null
end
$$ language sql;
