Thread: prepared statement already exists

Hi!  In a function to insert rows into a table, I keep
getting ERROR: prepared statement "updateplan" already
exists.  If any ideas; thanks.


CREATE OR REPLACE FUNCTION testPreparedStatement()
RETURNS SETOF FLOAT AS $$

DECLARE
tryint1 int4:=1 ;
tryint2 int4:=2 ;
BEGIN

PREPARE updatePlan ( int4, int4 ) AS INSERT INTO
testtable VALUES ( $1, $2 );

EXECUTE updatePlan ( tryint1, tryint2 ) ;

RETURN NEXT tryint1;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Sun, Aug 13, 2006 at 10:48:37AM -0700, Jim Bryan wrote:
> Hi!  In a function to insert rows into a table, I keep
> getting ERROR: prepared statement "updateplan" already
> exists.  If any ideas; thanks.

As the error says, you already have a prepared statement named
"updateplan".  To reuse that name you'll need to DEALLOCATE it
first.  However, then you'll get the following error:

test=> SELECT testPreparedStatement();
ERROR:  function updateplan(integer, integer) does not exist

That's because the PL/pgSQL command EXECUTE is different than the
SQL command EXECUTE:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

You don't need to use prepared statements in PL/pgSQL functions
because the language automatically prepares and caches query plans.
Just do the INSERT directly.

--
Michael Fuhr