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
