Dynamic query execution using array

From: ChoonSoo Park <luispark(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Dynamic query execution using array
Date: 2012-07-06 14:02:09
Message-ID: CACgbiFt4ZFYKpS3hLQLGsLHx1N6TW1Zq_EafEwwTqAVAVVWFgg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2012-07-06 15:45:42 Re: Dynamic query execution using array
Previous Message Robert Gravsjö 2012-07-06 07:04:05 Re: Draw Model from existing DB