Thread: Prepared statements in PGSQL functions

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 

am  14.06.2006, um 15:12:36 +0200 mailte Milen Kulev folgendes:
> How to solve my problem ? Is it possible at all to call prepared statement inside a function at all?

Yes, i have a example:

create or replace function foo() returns text as $$
declare sql text;
begin       sql := 'prepare bla(int) as select now();';       execute sql;       sql := 'execute bla(1);';
executesql;       return 'ready';
 
end
$$ language plpgsql;

test=*# select foo(); foo
-------ready
(1 row)

You should execute strings in plpgsql, not prepared statements.


HTH, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 

"Milen Kulev" <makulev@gmx.net> writes:
> I want to use prepared statement in a function.

Why?  You seem not to be aware that plpgsql implicitly prepares
statements behind the scenes.

>         prepare  mystmt( int, int, varchar)    as insert into part values ($1,$2,$3);
>         execute  mystmt(v_id1, v_id2, v_filler );
>         deallocate mystmt;

If that worked it would be *exactly* the same as just doing
    insert into part values (v_id1, v_id2, v_filler);

except for being slower due to re-preparing each time through the
function.  So don't waste your time trying to outsmart the language.
        regards, tom lane

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, June 14, 2006 4:35 PM
To: Milen Kulev
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Prepared statements in PGSQL functions


"Milen Kulev" <makulev@gmx.net> writes:
>> I want to use prepared statement in a function (your comments below).  Wantedjust to test the difference ...

Why?  You seem not to be aware that plpgsql implicitly prepares statements behind the scenes.

&&>> I already have a version with "direct" insert ( just as you say a couple of lines below)

>         prepare  mystmt( int, int, varchar)    as insert into part values ($1,$2,$3);
>         execute  mystmt(v_id1, v_id2, v_filler );
>         deallocate mystmt;

If that worked it would be *exactly* the same as just doing
    insert into part values (v_id1, v_id2, v_filler);

except for being slower due to re-preparing each time through the function.  So don't waste your time trying to
outsmart
the language.

>> My idea was to prepare the statment once and execute it in a loop  many times (within a procedure/function).
Anyway,
obviously there is no performance gain in using prepared statement in functions.
Regards. Milen
        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend