Checking Function Parameter Postgres

Question

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

In order to avoid updates with null values, you can use COALESCE() directly in your UPDATE statement, for instance UPDATE seller SET name=COALESCE(seller_name, name), ... — Edouard
– Edouard, Commented Jan 19, 2022 at 14:47

Pooya · Accepted Answer · 2022-01-19 14:51:22Z

2

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$

answered Jan 19, 2022 at 14:51

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Checking Function Parameter Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related