Conditional where for postgres stored procedure

Question

I want to create a conditional where clause using a PostgreSQL stored procedure.

This is my original stored procedure :

 select distinct 
 t.serialnumber as sn, 
 t.productid as tid, 
 t.merchantid as mid 
 from tbterminal as t
 ORDER BY idterminal ASC 

I want to check if p_mid, p_tid and p_sn are null or empty; if not null and not empty, add an and where for every component.

This is what I've tried :

CREATE OR REPLACE FUNCTION get_list_terminal_datatable (
 p_mid varchar, 
 p_tid varchar,
 p_sn varchar,
 p_limit int, 
 p_offset int
) 
 RETURNS TABLE (
 sn VARCHAR,
 tid varchar,
 mid varchar
) 
AS $$
DECLARE
 data_where varchar := 'WHERE ';
BEGIN
 IF p_mid notnull and p_mid != '' THEN
 data_where := data_where || ' t.merchantid like '''%|| p_mid ||'%'' ';
 END IF;
 IF p_tid notnull and p_tid != '' THEN
 data_where := data_where || ' t.productid like '''%|| p_tid ||'%'' ';
 END IF;
 IF p_sn notnull and p_sn != '' THEN
 data_where := data_where || ' t.serialnumber like '''%|| p_sn ||'%'' ';
 END IF;
 RETURN QUERY 
 select distinct 
 t.serialnumber as sn, 
 t.productid as tid, 
 t.merchantid as mid 
 from tbterminal as t
 ORDER BY idterminal ASC 
 limit p_limit offset p_offset;
END; $$ 
LANGUAGE 'plpgsql';

I know it doesn't work properly, so I need help on how to make the conditional where clause stored procedure in PostgreSQL work.

How would I have to write the stored procedure correctly?

Answer 1

You can use Boolean expressions, that return true, if a parameter is null or empty, otherwise the result of a LIKE operation.

...
SELECT DISTINCT
 t.serialnumber as sn,
 t.productid as tid,
 t.merchantid as mid
 FROM tbterminal as t
 WHERE (coalesce(p_mid, '') = ''
 OR t.merchantid LIKE '%' || p_mid || '%')
 AND (coalesce(p_tid, '') = ''
 OR t.productid LIKE '%' || p_tid || '%')
 AND (coalesce(p_sn, '') = ''
 OR t.serialnumber LIKE '%' || p_sn || '%')
 ORDER BY idterminal ASC
 LIMIT p_limit
 OFFSET p_offset;
...