Problem in calling prepare statement from STORED PROCEDURE

[Rajat Katyal]

Hi:

I prepare the statement for execution as follows:

PREPARE query(text) as SELECT count(*) FROM transform_custo mer_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) doesnot exist.

Please suggest the solution for this problem.

Thanks in advance.

Rajat

[Rajesh Kumar Mallah]

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_custo mer_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

[Tom Lane]

"Rajat Katyal" <ra****@inteles oftech.com> writes:

I prepare the statement for execution as follows:
PREPARE query(text) as SELECT count(*) FROM transform_custo mer_billing wher=
e inv_no =3D $1;
The problem is Iam not able to execute this prepare statement from the stor=
ed procedure defined.

EXECUTE means something different in plpgsql than it does in plain SQL,
and you do not need PREPARE at all in plpgsql. plpgsql's automatic
caching of plans gives you the effect of PREPARE on every statement
without your having to ask for it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings