Dynamic query execution using array

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

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

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