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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
