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