Skip site navigation (1) Skip section navigation (2)

Re: SQL stored function inserting and returning data in a row.

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com>
Cc: "Gerardo Herzig" <gherzig(at)fmed(dot)uba(dot)ar>, pgsql-sql(at)postgresql(dot)org
Subject: Re: SQL stored function inserting and returning data in a row.
Date: 2008-01-11 14:17:53
Message-ID: 162867790801110617v3776adeat6c5466af5ed8dd8e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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

In response to

pgsql-admin by date

Next:From: Andrew SullivanDate: 2008-01-11 15:24:49
Subject: Re: Need some info.
Previous:From: Daniel CauneDate: 2008-01-11 13:55:02
Subject: Re: SQL stored function inserting and returning data in a row.

pgsql-sql by date

Next:From: Erik JonesDate: 2008-01-11 16:01:16
Subject: Re: trigger for TRUNCATE?
Previous:From: Daniel CauneDate: 2008-01-11 13:55:02
Subject: Re: SQL stored function inserting and returning data in a row.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group