Re: hexadecimal to decimal - Mailing list pgsql-general

Ron Johnson wrote:
> On Wed, 2003-07-30 at 16:49, Joe Conway wrote:
>>I'm sure you could do this with plperl or one of the other PLs as well.
>
> They will probably be better optimized at it, also.
>

Your reply made me curious, so I tried a simple test:

create or replace function hex_to_int_perl(char(2)) returns integer as '
   return hex $_[0];
' language 'plperl';

create or replace function hex_to_int(char(2)) returns integer as '
declare
  v_ret record;
begin
  for v_ret in execute ''select x'''''' || $1 || ''''''::int as f'' loop
    return v_ret.f;
  end loop;
end;
' language 'plpgsql';

create table foo(f1 char(2));
insert into foo values ('ff');
insert into foo values ('fe');
insert into foo values ('fd');
explain analyze select hex_to_int(f1) from foo;
explain analyze select hex_to_int_perl('ff');
explain analyze select hex_to_int(f1) from foo;
explain analyze select hex_to_int_perl('ff');


The results were enlightening. Starting from a fresh psql session:

regression=# explain analyze select hex_to_int(f1) from foo;
                                           QUERY PLAN
-----------------------------------------------------------------------------------------------
  Seq Scan on foo  (cost=0.00..22.50 rows=1000 width=6) (actual
time=4.00..4.40 rows=3 loops=1)
  Total runtime: 4.66 msec
(2 rows)

regression=# explain analyze select hex_to_int_perl('ff');
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=54.55..54.55
rows=1 loops=1)
  Total runtime: 54.63 msec
(2 rows)

regression=# explain analyze select hex_to_int(f1) from foo;
                                           QUERY PLAN
-----------------------------------------------------------------------------------------------
  Seq Scan on foo  (cost=0.00..22.50 rows=1000 width=6) (actual
time=0.51..0.86 rows=3 loops=1)
  Total runtime: 0.95 msec
(2 rows)

regression=# explain analyze select hex_to_int_perl('ff');
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.22..0.23
rows=1 loops=1)
  Total runtime: 0.27 msec
(2 rows)

So based on this simple test, I'd say that if you only run the function
once per connect, use plpgsql, but if you run the function many times
during one backend session, use perl.

Joe