Inside a function, I can execute dynamic query like this
dept_id = 1;
salary = 50000;
RETURN QUERY EXECUTE 'SELECT * FROM employee WHERE dept_id = $1 and salary >= $2' using dept_id, salary;
When the query condition is dynamically generated and number of parameters is also dynamic,
DECLARE
tmpArray text[];
query text;
BEGIN
-- inputParameter will have the whole parameter list separated by comma.
tmpArray = string_to_array(inputParam, ',');
-- Let's assume the query condition is dynamically constructed from somewhere else.
query = 'select * FROM employee WHERE ' || dynamicQueryFunction(...);
RETURN QUERY EXECUTE query using tmpArray;
END
I know above one will fail to execute.
Of course, if I construct dynamic query using one of (quote_nullable, quote_literal, format), I can execute it.
Is there any other way to achieve dynamic query execution using array value?
Thanks in advance,
Choon Park
