Обсуждение: Dynamic query execution using array
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
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
It works!
One more question.
Do I have to use CAST for parameter value holder?
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.stehule@gmail.com> wrote:
Hello
2012/7/6 ChoonSoo Park <luispark@gmail.com>:it can work, but you have to use array access notation> 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?
EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[10,20]
Regards
Pavel
>
> Thanks in advance,
> Choon Park
2012/7/6 ChoonSoo Park <luispark@gmail.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.stehule@gmail.com> > wrote: >> >> 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 > >
2012/7/6 Pavel Stehule <pavel.stehule@gmail.com>: > 2012/7/6 ChoonSoo Park <luispark@gmail.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.stehule@gmail.com> >> wrote: >>> >>> 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 >> >>