From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | ChoonSoo Park <luispark(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Dynamic query execution using array |
Date: | 2012-07-06 15:45:42 |
Message-ID: | CAFj8pRCPH6oacwoe+b8AQZm=ee1NY90zE1VPBagstTyf9W69Jw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | rverghese | 2012-07-06 16:07:53 | Re: Primary key vs unique index |
Previous Message | ChoonSoo Park | 2012-07-06 14:02:09 | Dynamic query execution using array |