Re: Dynamic query execution using array

2012/7/6 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2012/7/6 ChoonSoo Park <luispark(at)gmail(dot)com>:
>> It works!
>>
>> One more question.
>> Do I have to use CAST for parameter value holder?
>

you can cast array to int array

string_to_array(..., ',')::int[]

Regards

Pavel

> probably
>
> all values in array should to share one type, and when this type is
> not same like holder, then you need cast
>
> Regards
>
> Pavel
>
>>
>> employee table's dept_id and salary columns are integer types.
>>
>> RETURN QUERY EXECUTE 'SELECT * FROM employee WHERE dept_id = $1[1] and
>> salary <= $1[2]' using tmpArrayValues;
>>
>> When I use text array, it complains:
>> ERROR: operator does not exist: integer = text
>>
>> So I have to match the type using CAST.
>> RETURN QUERY EXECUTE 'SELECT * FROM employee WHERE dept_id = CAST($1[1] as
>> integer) and salary <= CAST($1[2] as integer)' using tmpArrayValues;
>>
>> Looks like
>> Other ways to avoid using CAST?
>>
>> Best Regards,
>> Choon Park
>>
>>
>> On Fri, Jul 6, 2012 at 11:45 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>>
>>> Hello
>>>
>>> 2012/7/6 ChoonSoo Park <luispark(at)gmail(dot)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
>>
>>