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_id1int; 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
