PLPGSQL Function returns no results when I pass a null input parameter

Question

I have a function that has a condition testing for null values with one of the input parameters. If the input parameter is not null, it executes the sql statment, if its null then fit executes the last SQL statment.

For example

When I execute the function as
SELECT CAST(MD5(iml.udf_Extract_Shipment_HASHBYTESVALUES('SG160211304', '+00000', ''))

I expected the second query to be executed and get a result set.

CREATE OR REPLACE FUNCTION iml.udf_extract_shipment_hashbytesvalues ( varchar, varchar,varchar
)
RETURNS TABLE (
  concatenatedfield text
) AS
$body$
DECLARE
    v_custpo  ALIAS FOR $1;
    v_shipqty ALIAS FOR $2;
    V_mfgpart ALIAS FOR $3;



BEGIN

    IF V_mfgpart IS NOT NULL OR V_mfgpart <> ' ' THEN

    RETURN QUERY

          SELECT 
            CAST(CASE WHEN order_no        IS NULL THEN '1' ELSE order_no        END AS VARCHAR(200)) ||
            CAST(CASE WHEN customer_po     IS NULL THEN '1' ELSE customer_po     END AS VARCHAR(200)) ||
            CAST(CASE WHEN cust_no         IS NULL THEN '1' ELSE cust_no         END AS VARCHAR(200)) ||
            CAST(CASE WHEN customer_name   IS NULL THEN '1' ELSE customer_name   END AS VARCHAR(200)) ||
            CAST(CASE WHEN vendor_no       IS NULL THEN '1' ELSE vendor_no       END AS VARCHAR(200)) ||
            CAST(CASE WHEN im_part_no      IS NULL THEN '1' ELSE im_part_no      END AS VARCHAR(200)) ||
            CAST(CASE WHEN order_branch    IS NULL THEN   1 ELSE order_branch    END AS VARCHAR(200)) ||
            CAST(CASE WHEN ship_frm_branch IS NULL THEN   1 ELSE ship_frm_branch END AS VARCHAR(200)) ||
            CAST(CASE WHEN ship_to_branch  IS NULL THEN   1 ELSE ship_to_branch  END AS VARCHAR(200)) ||
            CAST(CASE WHEN lent_order      IS NULL THEN '1' ELSE lent_order      END AS VARCHAR(200)) ||
            CAST(CASE WHEN order_type      IS NULL THEN '1' ELSE order_type      END AS VARCHAR(200)) ||
            CAST(CASE WHEN ship_qty        IS NULL THEN '1' ELSE ship_qty        END AS VARCHAR(200)) ||
            CAST(CASE WHEN inventory_type  IS NULL THEN '1' ELSE inventory_type  END AS VARCHAR(200)) ||
            CAST(CASE WHEN credit_code     IS NULL THEN '1' ELSE credit_code     END AS VARCHAR(200)) ||
            CAST(CASE WHEN tax_amt         IS NULL THEN   1 ELSE tax_amt         END AS VARCHAR(200)) ||
            CAST(CASE WHEN freight_in      IS NULL THEN '1' ELSE freight_in      END AS VARCHAR(200)) ||
            CAST(CASE WHEN freight_out     IS NULL THEN '1' ELSE freight_out     END AS VARCHAR(200)) ||
            CAST(CASE WHEN mfg_part        IS NULL THEN '1' ELSE mfg_part        END AS VARCHAR(200)) ||
            CAST(CASE WHEN description     IS NULL THEN '1' ELSE description     END AS VARCHAR(200))   
          FROM 
            iml.shipments
          WHERE customer_po = v_custpo AND ship_qty = v_shipqty  AND mfg_part = V_mfgpart;

    ELSEIF  V_mfgpart = '' OR  V_mfgpart IS NULL THEN
        RETURN QUERY
          SELECT 
            CAST(CASE WHEN order_no        IS NULL THEN '1' ELSE order_no        END AS VARCHAR(200)) ||
            CAST(CASE WHEN customer_po     IS NULL THEN '1' ELSE customer_po     END AS VARCHAR(200)) ||
            CAST(CASE WHEN cust_no         IS NULL THEN '1' ELSE cust_no         END AS VARCHAR(200)) ||
            CAST(CASE WHEN customer_name   IS NULL THEN '1' ELSE customer_name   END AS VARCHAR(200)) ||
            CAST(CASE WHEN vendor_no       IS NULL THEN '1' ELSE vendor_no       END AS VARCHAR(200)) ||
            CAST(CASE WHEN im_part_no      IS NULL THEN '1' ELSE im_part_no      END AS VARCHAR(200)) ||
            CAST(CASE WHEN order_branch    IS NULL THEN   1 ELSE order_branch    END AS VARCHAR(200)) ||
            CAST(CASE WHEN ship_frm_branch IS NULL THEN   1 ELSE ship_frm_branch END AS VARCHAR(200)) ||
            CAST(CASE WHEN ship_to_branch  IS NULL THEN   1 ELSE ship_to_branch  END AS VARCHAR(200)) ||
            CAST(CASE WHEN lent_order      IS NULL THEN '1' ELSE lent_order      END AS VARCHAR(200)) ||
            CAST(CASE WHEN order_type      IS NULL THEN '1' ELSE order_type      END AS VARCHAR(200)) ||
            CAST(CASE WHEN ship_qty        IS NULL THEN '1' ELSE ship_qty        END AS VARCHAR(200)) ||
            CAST(CASE WHEN inventory_type  IS NULL THEN '1' ELSE inventory_type  END AS VARCHAR(200)) ||
            CAST(CASE WHEN credit_code     IS NULL THEN '1' ELSE credit_code     END AS VARCHAR(200)) ||
            CAST(CASE WHEN tax_amt         IS NULL THEN   1 ELSE tax_amt         END AS VARCHAR(200)) ||
            CAST(CASE WHEN freight_in      IS NULL THEN '1' ELSE freight_in      END AS VARCHAR(200)) ||
            CAST(CASE WHEN freight_out     IS NULL THEN '1' ELSE freight_out     END AS VARCHAR(200)) ||
            CAST(CASE WHEN mfg_part        IS NULL THEN '1' ELSE mfg_part        END AS VARCHAR(200)) ||
            CAST(CASE WHEN description     IS NULL THEN '1' ELSE description     END AS VARCHAR(200))   
          FROM 
            iml.shipments
          WHERE customer_po = v_custpo AND ship_qty = v_shipqty  AND mfg_part IS NULL OR mfg_part = V_mfgpart
          ;

END IF;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

BTW it is better to use COALESCE(order_no, '1') instead of CASE WHEN order_no IS NULL THEN '1' ELSE order_no END — Ihor Romanchenko
– Ihor Romanchenko, Commented Mar 13, 2014 at 16:31

Clodoaldo Neto · Accepted Answer · 2014-03-13 15:40:37Z

You are passing an empty string, which is not null, as mfgpart. So the first query will be executed. The second query will only be executed if you pass null as mfgpart. If you want the second query to be executed when you pass an empty string then change

IF V_mfgpart IS NOT NULL OR V_mfgpart <> ' ' THEN

to

IF V_mfgpart IS NOT NULL and V_mfgpart <> '' THEN

Notice the space vs empty string

I suspect you want this

create or replace function iml.udf_extract_shipment_hashbytesvalues (
    varchar v_custpo,
    varchar v_shipqty,
    varchar v_mfgpart
)
returns table (
  concatenatedfield text
) as
$body$
    select 
        cast(case when order_no        is null then '1' else order_no        end as varchar(200)) ||
        cast(case when customer_po     is null then '1' else customer_po     end as varchar(200)) ||
        cast(case when cust_no         is null then '1' else cust_no         end as varchar(200)) ||
        cast(case when customer_name   is null then '1' else customer_name   end as varchar(200)) ||
        cast(case when vendor_no       is null then '1' else vendor_no       end as varchar(200)) ||
        cast(case when im_part_no      is null then '1' else im_part_no      end as varchar(200)) ||
        cast(case when order_branch    is null then   1 else order_branch    end as varchar(200)) ||
        cast(case when ship_frm_branch is null then   1 else ship_frm_branch end as varchar(200)) ||
        cast(case when ship_to_branch  is null then   1 else ship_to_branch  end as varchar(200)) ||
        cast(case when lent_order      is null then '1' else lent_order      end as varchar(200)) ||
        cast(case when order_type      is null then '1' else order_type      end as varchar(200)) ||
        cast(case when ship_qty        is null then '1' else ship_qty        end as varchar(200)) ||
        cast(case when inventory_type  is null then '1' else inventory_type  end as varchar(200)) ||
        cast(case when credit_code     is null then '1' else credit_code     end as varchar(200)) ||
        cast(case when tax_amt         is null then   1 else tax_amt         end as varchar(200)) ||
        cast(case when freight_in      is null then '1' else freight_in      end as varchar(200)) ||
        cast(case when freight_out     is null then '1' else freight_out     end as varchar(200)) ||
        cast(case when mfg_part        is null then '1' else mfg_part        end as varchar(200)) ||
        cast(case when description     is null then '1' else description     end as varchar(200))   
    from 
        iml.shipments
    where
        customer_po = v_custpo and
        ship_qty = v_shipqty and
        (v_mfg_part is null or v_mfg_part = '' or mfg_part = v_mfgpart)
    ;
end;
$body$
language sql
volatile
called on null input
security invoker
cost 100 rows 1000;

That is what is what I wanted; It worked. Thank you, so much.

Collectives™ on Stack Overflow

PLPGSQL Function returns no results when I pass a null input parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related