plpgsql, dynamic variable lengths..

[Wells Oliver <wells.oliver@xxxxxxxxx>]

I am trying to create a plpgsql function which accepts a jsonb object as its parameter.

This jsonb can have up to three keys, and I am trying to build out the dynamic SQL like this. I want to be able to pass a variable number of params to the RETURN QUERY EXECUTE ... USING ... because it can be 0 params in the JSONB object, or one, or two.

What's the missing piece here? How can I adjust my USING to accomplish this?

begin
SQLSTRING := 'SELECT * FROM mytable WHERE true';
if args->>'col1' is not null then
SQLSTRING := SQLSTRING || ' AND col1 = $1';
end if;

if args->>'col2' is not null then
SQLSTRING := SQLSTRING || ' AND col2 = $1';
end if;

if args->>'col3' is not null then
SQLSTRING := SQLSTRING || ' AND col3 = $1';
end if;
SQLSTRING := SQLSTRING || ' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10';
raise notice 'SQL: %', SQLSTRING;
return query execute SQLSTRING USING args->>'col1', args->>'col2', args->>'col3';
end;
$$ language plpgsql;

--
Wells Oliver
wells.oliver@xxxxxxxxx