clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335496 fiddles created (27411 in the last week).

CREATE TABLE test ( value1 text, value2 text, value3 text, value4 text ); INSERT INTO test VALUES ('a',NULL,NULL,'d'), ('a', 'b', 'c', 'd'); -- is get out as result row in GetValues_old SELECT * FROM test;
2 rows affected
value1 value2 value3 value4
a d
a b c d
 hidden batch(es)


CREATE OR REPLACE FUNCTION "GetValues_old"( "@value1" VARCHAR(50), "@value2" VARCHAR(50), "@value3" VARCHAR(50), "@value4" VARCHAR(50) ) RETURNS SETOF test AS $$ BEGIN RETURN QUERY SELECT t."value1", t."value2", t."value3", t."value4" FROM test as t WHERE ("@value1" IS NULL OR t."value1" = "@value1") AND ("@value2" IS NULL OR t."value2" = "@value2") AND ("@value3" IS NULL OR t."value3" = "@value3") AND ("@value4" IS NULL OR t."value4" = "@value4"); END; $$ LANGUAGE plpgsql;
 hidden batch(es)


SELECT * FROM "GetValues_old"('a', NULL, NULL, 'd')
value1 value2 value3 value4
a d
a b c d
 hidden batch(es)


CREATE OR REPLACE FUNCTION "GetValues"( "@value1" VARCHAR(50), "@value2" VARCHAR(50), "@value3" VARCHAR(50), "@value4" VARCHAR(50) ) RETURNS SETOF test AS $$ BEGIN RETURN QUERY SELECT t."value1", t."value2", t."value3", t."value4" FROM test as t WHERE row(t.*) IS NOT DISTINCT FROM row("@value1", "@value2", "@value3", "@value4"); END; $$ LANGUAGE plpgsql;
 hidden batch(es)


SELECT * FROM "GetValues"('a', NULL, NULL, 'd')
value1 value2 value3 value4
a d
 hidden batch(es)