pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPrepared statements in PGSQL functions
4+ messages / 3 participants
[nested] [flat]
* Prepared statements in PGSQL functions
@ 2006-06-14 13:12 Milen Kulev <makulev@gmx.net>
0 siblings, 2 replies; 4+ messages in thread
From: Milen Kulev @ 2006-06-14 13:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Prepared statements in PGSQL functions
@ 2006-06-14 13:30 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Milen Kulev <makulev@gmx.net>
1 sibling, 0 replies; 4+ messages in thread
From: A. Kretschmer @ 2006-06-14 13:30 UTC (permalink / raw)
To: pgsql-sql
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);';
execute sql;
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 ===
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Prepared statements in PGSQL functions
@ 2006-06-14 14:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Milen Kulev <makulev@gmx.net>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-06-14 14:35 UTC (permalink / raw)
To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Prepared statements in PGSQL functions
@ 2006-06-14 15:16 Milen Kulev <makulev@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Milen Kulev @ 2006-06-14 15:16 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
-----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). 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-14 15:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-14 13:12 Prepared statements in PGSQL functions Milen Kulev <makulev@gmx.net>
2006-06-14 13:30 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-06-14 14:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-14 15:16 ` Milen Kulev <makulev@gmx.net>
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