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.steh...@gmail.com>wrote: > Hello > > 2012/7/6 ChoonSoo Park <luisp...@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 >
