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
