Re: Postgres Wishlist

Michael Glaesemann wrote:
> On Nov 13, 2010, at 3:46 , Donald Kerr wrote:
>
>
>> Steve,
>>
>> That works a treat:
>>
>> -----------------------------------
>> CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS '
>> DECLARE
>>  h alias for $1;
>>  exec varchar;
>>  curs refcursor;
>>  res  int;
>> BEGIN
>> exec := ''SELECT x'''''' || h || ''''''::int'';
>> OPEN curs FOR EXECUTE exec;
>> FETCH curs INTO res;
>> CLOSE curs;
>> return res;
>> END;'
>> LANGUAGE 'plpgsql'
>> IMMUTABLE
>> STRICT;
>>
>
> That's really arcane. Much more simply:
>
> CREATE FUNCTION
> hex2dec(in_hex TEXT)
> RETURNS INT
> IMMUTABLE
> STRICT LANGUAGE sql AS $body$
>   SELECT CAST(CAST(('x' || CAST($1 AS text)) AS bit(8)) AS INT);
> $body$;
>
> test=# select hex2dec('99');
>  hex2dec
> ---------
>      153
> (1 row)
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
>
>
I think that something like this would be the easiest to read:

CREATE OR REPLACE FUNCTION hex2dec(text)
RETURNS int
AS $$
my $arg=shift;
return(hex($arg));
$$ LANGUAGE plperl;

It works like a charm:
CREATE OR REPLACE FUNCTION hex2dec(text)
RETURNS int
AS $$
my $arg=shift;
return(hex($arg));
$$ LANGUAGE plperl;


--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com