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', '');

CodePudding user response：

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.

CodePudding user response：

Simply call the function like this:

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

So that an empty string is replaced with NULL.