Thread: hexadecimal to decimal
Hello, I have an attribute in a table which stores hexadecimal numbers as a two-character string, i.e. the attr definition is char(2). Now I need to display these values in decimal, but I wasn´t able to find such a function. So, is there a way to perform this conversion? thanks cl.
Claudio Lapidus wrote:
> I have an attribute in a table which stores hexadecimal numbers as a
> two-character string, i.e. the attr definition is char(2). Now I need to
> display these values in decimal, but I wasn´t able to find such a function.
> So, is there a way to perform this conversion?
>
I would have thought there was an easier way (I couldn't think of it),
but this seems to work:
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');
regression=# select hex_to_int(f1) from foo;
hex_to_int
------------
255
254
253
(3 rows)
I'm sure you could do this with plperl or one of the other PLs as well.
HTH,
Joe
On Wed, 2003-07-30 at 16:49, Joe Conway wrote: > Claudio Lapidus wrote: [snip] > > 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. -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
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
Joe Conway <mail@joeconway.com> writes:
> 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.
Presumably, the 50 msec difference represents the time to load up the
perl library and initialize a perl interpreter.
It might help if libperl were to be preloaded into the postmaster in the
way you created ...
regards, tom lane
Tom Lane wrote: > Presumably, the 50 msec difference represents the time to load up the > perl library and initialize a perl interpreter. > > It might help if libperl were to be preloaded into the postmaster in the > way you created ... > I tried that after I posted, but only saw roughly 30% improvement (which is consistent with my earlier tests IIRC). Not bad, but this still left plperl initial call at ~40 msec versus plpgsql at ~4 msec. It is possible that the initialization function that I used, plperl_init_all(), doesn't include everything it could. I might play around with it when I get a few moments. Joe
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> It might help if libperl were to be preloaded into the postmaster in the
>> way you created ...
> I tried that after I posted, but only saw roughly 30% improvement (which
> is consistent with my earlier tests IIRC). Not bad, but this still left
> plperl initial call at ~40 msec versus plpgsql at ~4 msec.
Hm. And the first call to a plpgsql function does require opening a
shared library. Curious that libperl seems so much more heavyweight
than plpgsql.
regards, tom lane
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>I tried that after I posted, but only saw roughly 30% improvement (which
>>is consistent with my earlier tests IIRC). Not bad, but this still left
>>plperl initial call at ~40 msec versus plpgsql at ~4 msec.
>
> Hm. And the first call to a plpgsql function does require opening a
> shared library. Curious that libperl seems so much more heavyweight
> than plpgsql.
>
I found the problem (or arguably two). Hows this look 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=3.31..3.53 rows=3 loops=1)
Total runtime: 3.69 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=2.38..2.39
rows=1 loops=1)
Total runtime: 2.43 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.29..0.49 rows=3 loops=1)
Total runtime: 0.54 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.15..0.15
rows=1 loops=1)
Total runtime: 0.18 msec
(2 rows)
Now the first call to the perl function is quicker than plpgsql and 90+%
faster than without preloading :-)
The first problem is that the initialization function for plperl,
plperl_init_all() is declared static, hence it couldn't be loaded
externally at all. The second problem is that when I wrote
process_preload_libraries() I used this line to call the init function:
initfunc = (func_ptr) load_external_function(filename, funcname,
false, NULL);
That false means that load_external_function() doesn't report errors if
the funcname cannot be found ;(
My reasoning at the time was that library preloading shouldn't prevent
the postmaster from starting, even if it is unsuccessful, but now I
wonder if that was a good idea.
What do you think:
1) should that call to load_external_function() use true for signalNotFound?
2) do you want a patch that exports plperl_init_all() (and I guess
similar init functions in pltcl and plpython)?
Joe
Joe Conway <mail@joeconway.com> writes:
> My reasoning at the time was that library preloading shouldn't prevent
> the postmaster from starting, even if it is unsuccessful, but now I
> wonder if that was a good idea.
It seems entirely sensible to me for the postmaster to choke on invalid
settings in postgresql.conf. Better than failing to mention the problem
at all, anyway.
> 2) do you want a patch that exports plperl_init_all() (and I guess
> similar init functions in pltcl and plpython)?
Yeah, I guess. Might as well make one in plpgsql too --- even if it
does nothing today, it might be useful in the future, so the
documentation ought to recommend "call 'plxxx_init' when preloading plxxx"
as a general thing.
regards, tom lane
Tom Lane wrote: > It seems entirely sensible to me for the postmaster to choke on invalid > settings in postgresql.conf. Better than failing to mention the problem > at all, anyway. > >>2) do you want a patch that exports plperl_init_all() (and I guess >>similar init functions in pltcl and plpython)? > > Yeah, I guess. Might as well make one in plpgsql too --- even if it > does nothing today, it might be useful in the future, so the > documentation ought to recommend "call 'plxxx_init' when preloading plxxx" > as a general thing. OK -- I'll put a patch together. Thanks, Joe