From: "Milen Kulev" <makulev@gmx.net>
To: <pgsql-sql@postgresql.org>
Subject: Prepared statements in PGSQL functions
Date: Wed, 14 Jun 2006 15:12:36 +0200
Message-ID: <010a01c68fb4$34db9990$0a00a8c0@trivadis.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal

Hi Listers,
I want to use prepared statement in a function. Here is my code:

create or replace function  generate_data 
	( integer, integer )
returns integer
as
$BODY$
		declare 
		p_count         alias for $1;
		p_max_value_id1 alias for $2;
		v_max_value_id1 integer  ; 
		v_id1 int;
		v_id2 int;
		v_filler varchar(200) := repeat('BIGSTRING', 3);
	begin
		v_id1:= round(  (random()* v_max_value_id1)::bigint,0);
		v_id2:= round(  (random()* v_max_value_id1)::bigint,0);
		prepare  mystmt( int, int, varchar)	as insert into part values ($1,$2,$3);
		execute  mystmt(v_id1, v_id2, v_filler );
		deallocate mystmt;
	end;
$BODY$
language plpgsql ; 


Definition of table part is :

CREATE TABLE part (
    id1    int not null,
    id2    int not null,
    filler varchar(200)
    );


When I try to  call my function I am getting the following errors :
postgres=# select  * from  gen (10, 10 );
ERROR:  function mystmt(integer, integer, character varying) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
CONTEXT:  SQL statement "SELECT  mystmt( $1 ,  $2 ,  $3  )"
PL/pgSQL function "gen" line 12 at execute statement


How to solve my problem ? Is it possible at all to call prepared statement inside a function at all?


Regards. MILEN