Re: Dynamic query execution using array

Hello

2012/7/6 ChoonSoo Park <luispark@gmail.com>:
> 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?

it can work, but you have to use array access notation

EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[10,20]

Regards

Pavel

>
> Thanks in advance,
> Choon Park