From: | ChoonSoo Park <luispark(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Dynamic query execution using array |
Date: | 2012-07-06 16:21:30 |
Message-ID: | CACgbiFsjOcXNVNx1GuLAe2C0LY2AKd1ikt9+yiBdh7Q6sL1feQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)stehule(at)gmail(dot)com>wrote:
> Hello
>
> 2012/7/6 ChoonSoo Park <luispark(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2012-07-06 16:40:45 | Re: Dynamic query execution using array |
Previous Message | rverghese | 2012-07-06 16:07:53 | Re: Primary key vs unique index |