hexadecimal to decimal

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

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.

Attached is a patch that:
1) fixes the behavior of preload_libraries
2) adds an exported xxx_init() function to plperl, pltcl, plpython, and
plpgsql
3) updates the documentation for the changes

Compiles clean, and passes all regression tests with the following line
in postgresql.conf (this probably won't wrap nicely):
preload_libraries =
'$libdir/plperl:plperl_init,$libdir/pltcl:pltcl_init,$libdir/plpython:plpython_init,$libdir/plpgsql:plpgsql_init'

I ran the following both without (one psql session for all four
statements) and with preloading (also all four in one session). The
actual function definitions at the bottom of the email:

without preload:
=====================================================================
regression=# explain analyze select echo_plperl('hello');
Total runtime: 55.29 msec
regression=# explain analyze select echo_pltcl('hello');
Total runtime: 23.34 msec
regression=# explain analyze select echo_plpythonu('hello');
Total runtime: 32.40 msec
regression=# explain analyze select echo_plpgsql('hello');
Total runtime: 3.09 msec

with preload:
=====================================================================
regression=# explain analyze select echo_plperl('hello');
Total runtime: 5.14 msec
regression=# explain analyze select echo_pltcl('hello');
Total runtime: 7.64 msec
regression=# explain analyze select echo_plpythonu('hello');
Total runtime: 1.91 msec
regression=# explain analyze select echo_plpgsql('hello');
Total runtime: 1.35 msec

Please apply.

Thanks,

Joe

--test functions
CREATE OR REPLACE FUNCTION echo_plperl(text) RETURNS text AS '
return $_[0];
' LANGUAGE plperl;

CREATE OR REPLACE FUNCTION echo_pltcl(text) RETURNS text AS '
return $1
' LANGUAGE pltcl;

CREATE OR REPLACE FUNCTION echo_plpythonu(text) RETURNS text AS '
return args[0]
' LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION echo_plpgsql(text) RETURNS text AS '
begin
return $1;
end;
' LANGUAGE plpgsql;

explain analyze select echo_plperl('hello');
explain analyze select echo_pltcl('hello');
explain analyze select echo_plpythonu('hello');
explain analyze select echo_plpgsql('hello');

Joe Conway <mail@joeconway.com> writes:

Attached is a patch that:
1) fixes the behavior of preload_libraries
2) adds an exported xxx_init() function to plperl, pltcl, plpython, and
plpgsql
3) updates the documentation for the changes

As coded, this will cause pltcl to try to execute the unknown-module
load on every pltcl function call :-(. You really need two bits of
state if you are going to have separate postmaster-time and backend-time
initialization.

Will fix and commit.

regards, tom lane

Tom Lane wrote:

As coded, this will cause pltcl to try to execute the unknown-module
load on every pltcl function call :-(. You really need two bits of
state if you are going to have separate postmaster-time and backend-time
initialization.

Hmmm, I see your point :(. Sorry about that!

Will fix and commit.

Thanks,

Joe