Handle empty value '' on function numeric variable

Question

I'm trying to handle empty '' values in FUNCTION variable. What should be the correct way to return nothing when calling the function with empty value like below

SELECT *
FROM metadata.fn_get_id('mea', 'sau', '');




DROP FUNCTION IF EXISTS metadata.fn_get_id(VARCHAR, VARCHAR, NUMERIC);

CREATE OR REPLACE FUNCTION metadata.fn_get_id(a1 CHARACTER VARYING, b2 CHARACTER VARYING,
                                                            c3 NUMERIC DEFAULT 0
) RETURNS INT
    LANGUAGE plpgsql
AS
$$
DECLARE
    linked_id INT;
BEGIN
    EXECUTE
            'SELECT linked_id::INT FROM ' || $1 || '_region
                WHERE 1=1 AND iso=upper(' || QUOTE_LITERAL($2) || ') AND id = '|| $3 ||' limit 1;'
        INTO linked_id;
    RETURN linked_id;

END


$$;

-- TEST LINK_ID 1213506417 (PASS)
SELECT *
FROM metadata.fn_get_id('mea', 'sau', 414803422);

-- TEST Null (PASS)
SELECT *
FROM metadata.fn_get_id('mea', 'sau');

-- TEST empty (FAILS ... HOW to Handle)
SELECT *
FROM metadata.fn_get_id('mea', 'sau', '');

You can't pass an empty string ('') for a numeric value to begin with. The obvious choice is pass null instead — user330315
– user330315, Commented Mar 23, 2022 at 13:26

Stefanov.sm · Accepted Answer · 2022-03-23 13:34:34Z

1

Make c3 function argument type text default null and check for empty string first thing in the function body.

create or replace function metadata.fn_get_id(a1 text, b2 text, c3 text default null)
RETURNS integer language plpgsql as
$$
declare 
 -- your declarations
begin
    if nullif(c3, '') is null then
        return null;
    end if;
 -- your function body
$$;

Call:

SELECT *
FROM metadata.fn_get_id('mea', 'sau', 414803422::text);

Btw the function in the example is injection prone.

edited Mar 23, 2022 at 13:34

answered Mar 23, 2022 at 13:28

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

4 Comments

user330315 Over a year ago

There is no need to define the parameter as text - numeric can handle NULL values just as well.

Stefanov.sm Over a year ago

Sure, but what about an empty string as the OP says?

user330315 Over a year ago

He shouldn't pass an empty string for a numeric parameter to begin with. That's exactly what null is for.

Stefanov.sm Over a year ago

No, he shouldn't. Yet strange enough this is exactly what he is asking about. I do agree however that it is very wrong to encourage bad practices.

Laurenz Albe · Accepted Answer · 2022-03-23 13:34:09Z

0

Simply call the function like this:

metadata.fn_get_id('mea', 'sau', nullif('', ''))

So that an empty string is replaced with NULL.

answered Mar 23, 2022 at 13:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Handle empty value '' on function numeric variable

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related