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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: makulev@gmx.net
Subject: Re: Prepared statements in PGSQL functions
In-Reply-To: <010a01c68fb4$34db9990$0a00a8c0@trivadis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox