Re: parsed queries (cursors) cashing issues

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).

On 8/3/07, Sibte Abbas <sibtay(at)gmail(dot)com> wrote:
>
> On 8/2/07, Sergey Moroz <smo(at)mgcp(dot)com> wrote:
> > The problem is that I can't find the way to exclude query parsing
> (prepare
> > step) for custom queries. In other words I want to create a function
> that
> > accepts a query text with "$1, $2, etc." and variables as params,
> executes
> > the query and returns a set of record. I could use 'execute' in plpgsql
> but
> > in such case a query will be parsed each time it is called. I check SPI
> and
> > found the way to store execution plans for the duration of the session,
> but
> > no convenient way to check if the plan was already generated for the
> query.
> > So I should create and store hash table by myself, and associate plan
> > pointers and query hash by myself. I'm not a C/C++ guy so it's not an
> easy
> > task for me :). Is there any way to solve the problem? By the way - why
> not
> > to store hashes for queries and execution plans in a shared pool to have
> an
> > opportunity not to parse already parsed queries for any session as
> Oracle
> > does?
> >
>
> Looks like you want to cache the query plans and then simply execute
> them in subsequent invocations. The answer to this is Prepared
> statements. Go to
> http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html for
> more details.
>
> regards,
> -- Sibte
>

--
Sincerely,
Sergey Moroz