I have a function with parameters. If I pass null as the parameter in a where clause I would like it to bring back all results eg

CREATE OR REPLACE FUNCTION ABC(" CUSTCODE" VARCHAR)
RETURNS TABLE ("CUSTOMER_CODE" VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT  CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE  CUSTOMER_CODE = CUSTCODE
';

So if CUSTCODE is NULL then bring back all CUSTOMER_CODE almost like ignoring the where clause

Could someone point me in the right direction?

Thanks in advance!

Leon

CodePudding user response：

As per Dai's comment:

CREATE OR REPLACE TABLE DIM_CUSTOMER(CUSTOMER_CODE text, CUSTOMERNAME text);

INSERT INTO DIM_CUSTOMER VALUES ('one', 'customer one'),('two', 'customer two');

CREATE OR REPLACE FUNCTION ABC(CUSTCODE VARCHAR)
RETURNS TABLE (CUSTOMER_CODE VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE CUSTCODE IS NULL OR CUSTOMER_CODE = CUSTCODE
';

SELECT * FROM TABLE(ABC('one'));

gives:

SELECT * FROM TABLE(ABC(null::text));

gives: