By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,760 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,760 IT Pros & Developers. It's quick & easy.

Problem in calling prepare statement from STORED PROCEDURE

P: n/a
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) doesnot exist.

Please suggest the solution for this problem.

Thanks in advance.

Rajat

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a


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


Nov 23 '05 #2

P: n/a
"Rajat Katyal" <ra****@intelesoftech.com> writes:
I prepare the statement for execution as follows:
PREPARE query(text) as SELECT count(*) FROM transform_customer_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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.