Re: indexes on varchar fields

On Fri, 8 Nov 2002 03:13 pm, Nigel J. Andrews wrote:
> On Fri, 8 Nov 2002, Peter Nixon wrote:
> > On Fri, 8 Nov 2002 12:03:15 +0000 (UTC)
> > Squire "Nigel J. Andrews" uttered the following:
> >
> > Well, they are generated by cisco routers. They are the conference id for
> > each voip call and "should" be unique (at least to each router).
> > This (41A4DCE3 8CF2D611 85170004 75AE73D4) is the format they come out of
> > the router in, and I am just inserting the data directly to a text field.
> > You are right, they do seem to be hex numbers. Can postgres accept hex as
> > a numberic? if so that would speed things up ALOT...
>
> I'm sure it must be possible but I can't see anything at the moment. So how
> about:
>
>
> create function hex_to_int (text) returns integer as '
>     return hex($_[0]);
> ' language 'plpgsql';
>
> create function int_to_hex (integer) returns text as '
>     return sprintf("%X",$_[0]);
> ' language 'plpgsql';
>
> Used as:
>
> insert int mytable values (hex_to_int('AB456F56'));
>
> and
>
> select int_to_hex(colname) from mytable;
>
> You'd need to be confident that you weren't going to exceed your native
> integer size in perl though.

radius=# create function hex_to_int (text) returns integer as '
radius'#         return hex($_[0]);
radius'# ' language 'plperl';
CREATE FUNCTION
radius=# select hex_to_int('AB456F56');
ERROR:  pg_atoi: error reading "2873454422": Numerical result out of range

Thanks for the idea though. I will investigate this further.

Also, can anyone explain to me why the following happens?

radius=# create function testperltest2 (text) returns text as '
radius'#         my $datetime = $_[0];
radius'#         return $datetime;
radius'#         # return $_[0];
radius'# ' language 'plperl';
CREATE FUNCTION
radius=# select testperltest2('.16:46:02.356 EET Wed Dec 11 2002');
           testperltest2
-----------------------------------
 .16:46:02.356 EET Wed Dec 11 2002
(1 row)

OK. Fine. My test function returns correctly..

radius=# create function testperltest3 (text) returns text as '
radius'#         my $datetime = $_[0];
radius'#         # Remove . from the start of time fields (routers that have
lost ntp timesync)
radius'#         $datetime =~ s/^\.*//;
radius'#         return $datetime;
radius'# ' language 'plperl';
CREATE FUNCTION
radius=# select testperltest3('.16:46:02.356 EET Wed Dec 11 2002');
 testperltest3
---------------

(1 row)

Do you have any idea why that regexp would be returning nothing? It works fine
in standard perl...


--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc