Re: parsed queries (cursors) cashing issues

On 8/3/07, Sergey Moroz <smo@mgcp.com> wrote:
> No that is not I meant. The problem in Prepared statements is in that you
> should determine SQL inside the function. I want to pass a query as a
> parameter, as well as query parameters.
> For example (I want to create a function like the following):
>
> select *
>   from exec_query(
>                               /*query text  => */  'select f1, f2 from table
> where f3 = $1' ,
>                                /*param1      => */  1::integer
>                           )
>          as (f1 integer, f2 text)
>
> so function exec_query got a query text as parameter, query parameters,
> executed it and returned result as SETOF. In case of such a query had been
> executed at least once, prepare step should be excluded (stored execution
> plan should be used).
>

In this case you need to store query text along with its plan name.
This will allow you to simply execute the plan each time a previously
parsed/planned query is executed.

However storing raw queries can be a *very* expensive operation, not
to mention the high cost of performing comparison on them. Due to the
associated cost, I'll
recommend using(and storing) hashes for query text.

If I were you, i'll write the hash calculation and storage and
retrieval functions in C and the top level function in Plpgsql.

Hope that helps.

regards,
-- Sibte