DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=mqX7fqyYdSdLsEeEplUHjOiW0hxOOmgI9qp2y5vARCdsOf7obPTDUQcqbrOuGfVwkHGcynMZ+D+6539f13UgsrM2dZmA9NycTCqAZBvIwvRz7HkmAtc3ZV8nupeQXUHGbzVPpJzzC3sDrcNIwaA4DFpmL+vZUTSJmoo4CrI8iCY=
Message-ID: <162867790801110617v3776adeat6c5466af5ed8dd8e@mail.gmail.com>
Date: Fri, 11 Jan 2008 15:17:53 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Daniel Caune" <daniel.caune@ubisoft.com>
Subject: Re: SQL stored function inserting and returning data in a row.
Cc: "Gerardo Herzig" <gherzig@fmed.uba.ar>, pgsql-sql@postgresql.org
In-Reply-To: <1E293D3FF63A3740B10AD5AAD88535D206F41789@UBIMAIL1.ubisoft.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <47874CEB.3010803@fmed.uba.ar>
	<1E293D3FF63A3740B10AD5AAD88535D206F41789@UBIMAIL1.ubisoft.org>

Hello

> By the way, is there any performance difference between pure SQL and
> PL/pgSQL stored functions?  If I remember correctly there was such a
> distinction between pure SQL statement and PL/PLSQL stored procedures
> (Oracle), in the sense that PL/PLSQL stored procedures are executed
> within the PL/PLSQL engine which sends pure SQL statements to the SQL
> engine for execution.  There is a little overhead between PL/PLSQL and
> SQL engines.
>

create or replace function test1(integer)
returns integer as
$$select $1;$$
language sql immutable;

create
 or replace function test2(integer)
returns integer as
$$begin return  $1; end$$
language plpgsql immutable;

postgres=# select count(*) from (select test1(i) from
generate_series(1,100000) g(i)) f;
 count
--------
 100000
(1 row)

Time: 123,532 ms

postgres=# select count(*) from (select test2(i) from
generate_series(1,100000) g(i)) f;
 count
--------
 100000
(1 row)

Time: 123,877 ms

but if you forgot immutable
postgres=# create or replace function test3(integer)
returns integer as
$$begin return  $1; end$$
language plpgsql;
CREATE FUNCTION
Time: 430,258 ms
postgres=# select count(*) from (select test3(i) from
generate_series(1,100000) g(i)) f;
 count
--------
 100000
(1 row)

Time: 472,150 ms

Regards
Pavel Stehule