Create Redshift UDF with default Parameters

Question

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?

Paulo Moreira · Accepted Answer · 2021-01-05 20:25:58Z

3

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       |
*/

answered Jan 5, 2021 at 20:25

Paulo Moreira

6517 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AlexYes · Accepted Answer · 2018-03-20 21:43:05Z

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).

Collectives™ on Stack Overflow

Create Redshift UDF with default Parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related