From: Pavel Stehule <pavel.stehule@gmail.com>
To: Daniel Caune <daniel.caune@ubisoft.com>
Cc: Gerardo Herzig <gherzig@fmed.uba.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL stored function inserting and returning data in a row.
Date: Fri, 11 Jan 2008 15:17:53 +0100
Message-ID: <162867790801110617v3776adeat6c5466af5ed8dd8e@mail.gmail.com> (raw)
In-Reply-To: <1E293D3FF63A3740B10AD5AAD88535D206F41789@UBIMAIL1.ubisoft.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, daniel.caune@ubisoft.com, gherzig@fmed.uba.ar
Subject: Re: SQL stored function inserting and returning data in a row.
In-Reply-To: <162867790801110617v3776adeat6c5466af5ed8dd8e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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