Re: Postgres Wishlist

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