CODEWITHSUNDEEP
amazon-redshift
Ankush Datta
Ankush Datta

Reputation: 23

Create Redshift UDF with default Parameters

I am trying to create a Redshift UDF with function Parameters as below:

create or replace function function1(srctimezone VARCHAR,desttimezone VARCHAR,flag = 'nr') returns datetime

The last Parameter would be a defualt Parameter, i.e if user does not pass any value, it should take 'nr' by default. But I am not able to create function with default parameters. Any suggestions if Redshfit does / does not allow function creation with default Parameters. If it does, then what will be the correct syntax for the same?

Upvotes: 1

Views: 3829

Answers (2)

Paulo Moreira
Paulo Moreira

Reputation: 631

You can do:

CREATE OR REPLACE FUNCTION public.emulate_optional_arg(env_name varchar, optional varchar)
    RETURNS varchar
    LANGUAGE sql
    IMMUTABLE
AS $$
    
SELECT $1 || ', ' || $2

$$
;


CREATE OR REPLACE FUNCTION public.emulate_optional_arg(env_name varchar)
    RETURNS varchar
    LANGUAGE sql
    IMMUTABLE
AS $$
    
SELECT public.emulate_optional_arg($1,'default_value_here')

$$
;


SELECT public.emulate_optional_arg('dev');

/*
emulate_optional_arg   |
-----------------------|
dev, default_value_here|
 */

SELECT public.emulate_optional_arg('dev','newvalue');

/*
emulate_optional_arg|
--------------------|
dev, newvalue       |
*/

Upvotes: 3

AlexYes
AlexYes

Reputation: 4208

In Redshift, you can create a Python UDF with an arbitrary number of arguments but you have to pass the same number of arguments when you execute the function. The workaround for optional parameters would be passing nulls and setting the parameter to the default value at the beginning of the function body similar to this:

create function f_optional_test(a int, b int)
returns int
stable as $$
    if b==None:
        return 999  # set default
    else:
        return b
$$ language plpythonu;

select f_optional_test(1,2);    -- returns 2
select f_optional_test(1,null); -- returns 999

Also, you can create multiple functions with the same name but different number of parameters, so the function that is selected for execution will be picked up by the database engine based on the number of parameters and their data type:

create function f_optional_test(a int)
returns int
stable as $$
    return 999 # default for b
$$ language plpythonu;

select f_optional_test(1,2); -- returns 2
select f_optional_test(1);   -- returns 999

It's up to you to choose whether you'd like to have a single function at the expense of passing nulls for default parameters or have optional parameters at the expense of maintaining two versions of the same function if there is one optional parameter (with more optional parameters and their variable order it's more complicated and the first option is obviously better).

Upvotes: 2

Related Questions