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?
Best Answer
You can use Boolean expressions, that return true, if a parameter is null or empty, otherwise the result of a
LIKE
operation.