Re: PREPARED STATEMENT

On Sat, Dec 11, 2004 at 12:30:30PM +0000, NosyMan wrote:

> I want to know that is a posibillity to test if a statement is prepared in
> PL/PgSQL.
>
> I have create a function:
> .........
> PREPARE PSTAT_SAVE_record(INTEGER, INTEGER, DATE, VARCHAR) AS INSERT INTO
> table VALUES($1, $2, $3, $4);
> .........
>
> When I try to execute it second time I got an error: prepared statement
> 'PSTAT_SAVE_record' already exists. How can I avoid this error? is there a
> posibillity to test if a statement was prepared before?

I'm not aware of a way to test for the existence of a prepared
statement without trying to use it. Is there a reason the PREPARE
is executed more than once? Can you DEALLOCATE the prepared statement
when you're done with it so subsequent PREPAREs won't raise an error?

If you're using PostgreSQL 8.0 then you could trap the error and
ignore it:

BEGIN
PREPARE ...;
EXCEPTION
WHEN duplicate_prepared_statement THEN
NULL;
END;

While I was investigating your question I found a bug that caused
the backend to crash if a function that executed PREPARE was called
more than once. Are you not getting that crash? If not, what
version of PostgreSQL are you running? I discovered the bug in
8.0.0rc1 and it appears to exist in 7.4.6 as well.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/