2012/7/6 Pavel Stehule <pavel.stehule@xxxxxxxxx>: > 2012/7/6 ChoonSoo Park <luispark@xxxxxxxxx>: >> 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@xxxxxxxxx> >> wrote: >>> >>> Hello >>> >>> 2012/7/6 ChoonSoo Park <luispark@xxxxxxxxx>: >>> > 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 >> >> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
