Re: Prepared statements in PGSQL functions

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