Search Postgresql Archives

Re: parsed queries (cursors) cashing issues

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On 8/2/07, Sergey Moroz <smo@xxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux