On 8/2/07, Sergey Moroz <smo@mgcp.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