Checking Function Parameter Postgres-CodePudding

i'm trying to find the fastest way to check whether my postgres function parameter is null or empty.

my current function looks like:

CREATE OR REPLATE FUNCTON update_seller_detail(seller_id int, 
seller_name varchar, seller_address varchar, seller_gender varchar)
RETURNS character varying
LANGUAGE plpsql
SECURITY DEFINER
AS $function$

BEGIN

IF seller_id = '' THEN
RAISE NOTICE 'Check Parameter'
return -1
ELSE
IF seller_name = '' THEN
    UPDATE seller SET address=seller_address, gender=seller_gender WHERE id=seller_id;
ELSEIF seller_address = '' THEN 
    UPDATE seller SET name=seller_name, gender=seller_gender WHERE id=seller_id;
ELSE seller_gender= '' THEN 
    UPDATE seller SET name=seller_name, address=seller_address WHERE id=seller_id;
END IF;
END IF;
END;
$function$

Is there any way to find which of the parameter is null? so i can set my update to the column that's not null. thanks

CodePudding user response：

you can use coalesce(nullif(trim(<column-reference>), '') in update query with column reference:

CREATE OR REPLACE FUNCTION update_seller_detail(
        seller_id int,
        seller_name varchar, 
        seller_address varchar, 
        seller_gender varchar
    )
    RETURNS character varying
    LANGUAGE plpgsql
    SECURITY DEFINER
AS
$function$

BEGIN

    IF seller_id ISNULL THEN
        RAISE NOTICE 'Check Parameter';
        return -1;
    ELSE

        UPDATE public.seller s
        SET name    = coalesce(nullif(trim(seller_name), ''), s.name),
            address = coalesce(nullif(trim(seller_address), ''), s.address),
            gender  = coalesce(nullif(trim(seller_gender), ''), s.gender)
        WHERE id = seller_id;

        return 1;
    END IF;
END;
$function$