Обсуждение: 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