Re: Problem in calling prepare statement from STORED PROCEDURE

Hi,

Inside a stored procedure EXECUTE runs a dynamic query,


DOCS SAY:

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

EXECUTE command-string;

 where command-string is an expression yielding a string (of type text) containing the command to be executed. This string is fed literally to the SQL engine.


Really not sure how to execute a named plan inside plpgsql.

Regds
mallah.



Rajat Katyal wrote:

Hi:

 

I prepare the statement for execution as follows:

 

PREPARE query(text) as SELECT count(*) FROM transform_customer_billing where inv_no = $1;

 

The problem is Iam not able to execute this prepare statement from the stored procedure defined. I added the statement to call the prepare statement is: EXECUTE query('100023'). The ERROR it prints : Function query(text) does not exist.

 

Please suggest the solution for this problem.

 

Thanks in advance.

 

Rajat