Re: Prepared statements in PGSQL functions

From: "Milen Kulev" <makulev(at)gmx(dot)net>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Prepared statements in PGSQL functions
Date: 2006-06-14 15:16:39
Message-ID: 012701c68fc5$895d24f0$0a00a8c0@trivadis.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

"Milen Kulev" <makulev(at)gmx(dot)net> writes:
>> I want to use prepared statement in a function (your comments below). Wanted
just 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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Mark Adan 2006-06-14 15:50:23 Re: Good examples of calling slony stored procedures
Previous Message Tom Lane 2006-06-14 14:35:28 Re: Prepared statements in PGSQL functions