Re: Dynamic query execution using array

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?

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
>
>