Re: Hex to Dec Conversion

My first post to the mailing list and I hope I am in the right place!

I am trying to convert from hex to decimal and can do that successfully
using the following code:

SELECT x'FF'::integer;

which outputs 255 and is exactly what I want.

I want to substitute the string in the code 'FF' for a column in the
database like so:

SELECT x'db_column'::integer FROM db_table;

but no matter the combinations I try, I cannot get it to work.

Thew data colum contains html color codes like "0099FF" and I want to
convert these to, in this case, "0 153 255".

The following code behaves well:

SELECT x'00'::integer || ' ' || x'99'::integer || ' ' || x'FF'::integer;

resulting in "0 153 255". All correct

I was hopeful that something similar to the following would work but I just
cannot get it to work despite trying various combinations.

SELECT x'substring(col,1,2)'::integer || ' ' ||
x'substring(col,3,2)'::integer || ' ' || x'substring(col,5,2)'::integer

I would much prefer to do this as part of the query rather than having to
create a function. There must be a way! :)

Any help would be very greatly apprecaited.

Regards,

Donald

On Mon, Oct 18, 2010 at 5:47 PM, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
> My first post to the mailing list and I hope I am in the right place!
>
> I am trying to convert from hex to decimal and can do that successfully
> using the following code:
>
> SELECT x'FF'::integer;
>
> which outputs 255 and is exactly what I want.
>
> I want to substitute the string in the code 'FF' for a column in the
> database like so:
>
> SELECT x'db_column'::integer FROM db_table;
>
> but no matter the combinations I try, I cannot get it to work.
>
> Thew data colum contains html color codes like "0099FF" and I want to
> convert these to, in this case, "0 153 255".
>
> The following code behaves well:
>
> SELECT  x'00'::integer || ' ' || x'99'::integer || ' ' ||  x'FF'::integer;
>
> resulting in "0 153 255". All correct
>
> I was hopeful that something similar to the following would work but I just
> cannot get it to work despite trying various combinations.
>
> SELECT x'substring(col,1,2)'::integer || ' ' ||
> x'substring(col,3,2)'::integer || ' ' || x'substring(col,5,2)'::integer
>
> I would much prefer to do this as part of the query rather than having to
> create a function. There must be a way! :)
>
> Any help would be very greatly apprecaited.

Hrmph. I took a look at this and couldn't figure out how to make it
work without declaring a PL/pgSQL function, so that I could construct
a dynamic query using EXECUTE. I googled around a bit, and found a few
people having roughly your same problem: I think this comes from
Postgres not having a counterpart to its built-in to_hex() function.

Anyway, I adapted the function from here
<http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php> to
use more modern function syntax, and came up with this:

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$
DECLARE
result int;
BEGIN
EXECUTE 'SELECT x''' || hexval || '''::int' INTO result;
RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

which you should be able to use like this:

SELECT hex_to_int(substring(color,1,2)) AS first,
hex_to_int(substring(color,3,2)) AS second,
hex_to_int(substring(color, 5,2)) AS third
FROM colors;

first | second | third
-------+--------+-------
0 | 153 | 255
(1 row)

I know it's not exactly what you were looking for, but IMO the
cleanest way to do this anyway would be to make a wrapper function
like "html_color_to_int()" that would handle all this conversion for
you in one place.

Josh

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Josh Kupershmidt
Sent: 19 October 2010 01:36
To: Donald Kerr
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Hex to Dec Conversion

On Mon, Oct 18, 2010 at 5:47 PM, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk>
wrote:
> My first post to the mailing list and I hope I am in the right place!
>
> I am trying to convert from hex to decimal and can do that
> successfully using the following code:
>
> SELECT x'FF'::integer;
>
> which outputs 255 and is exactly what I want.
>
> I want to substitute the string in the code 'FF' for a column in the
> database like so:
>
> SELECT x'db_column'::integer FROM db_table;
>
> but no matter the combinations I try, I cannot get it to work.
>
> Thew data colum contains html color codes like "0099FF" and I want to
> convert these to, in this case, "0 153 255".
>
> The following code behaves well:
>
> SELECT  x'00'::integer || ' ' || x'99'::integer || ' ' ||  
> x'FF'::integer;
>
> resulting in "0 153 255". All correct
>
> I was hopeful that something similar to the following would work but I
> just cannot get it to work despite trying various combinations.
>
> SELECT x'substring(col,1,2)'::integer || ' ' ||
> x'substring(col,3,2)'::integer || ' ' ||
> x'substring(col,5,2)'::integer
>
> I would much prefer to do this as part of the query rather than having
> to create a function. There must be a way! :)
>
> Any help would be very greatly apprecaited.

Hrmph. I took a look at this and couldn't figure out how to make it work
without declaring a PL/pgSQL function, so that I could construct a dynamic
query using EXECUTE. I googled around a bit, and found a few people having
roughly your same problem: I think this comes from Postgres not having a
counterpart to its built-in to_hex() function.

Anyway, I adapted the function from here
<http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php> to use
more modern function syntax, and came up with this:

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$
DECLARE
result int;
BEGIN
EXECUTE 'SELECT x''' || hexval || '''::int' INTO result; RETURN result;
END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;

which you should be able to use like this:

SELECT hex_to_int(substring(color,1,2)) AS first,
hex_to_int(substring(color,3,2)) AS second,
hex_to_int(substring(color, 5,2)) AS third
FROM colors;

first | second | third
-------+--------+-------
0 | 153 | 255
(1 row)

I know it's not exactly what you were looking for, but IMO the cleanest way
to do this anyway would be to make a wrapper function like
"html_color_to_int()" that would handle all this conversion for you in one
place.

Josh

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Josh Kupershmidt
Sent: 19 October 2010 01:36
To: Donald Kerr
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Hex to Dec Conversion

On Mon, Oct 18, 2010 at 5:47 PM, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk>
wrote:
> My first post to the mailing list and I hope I am in the right place!
>
> I am trying to convert from hex to decimal and can do that
> successfully using the following code:
>
> SELECT x'FF'::integer;
>
> which outputs 255 and is exactly what I want.
>
> I want to substitute the string in the code 'FF' for a column in the
> database like so:
>
> SELECT x'db_column'::integer FROM db_table;
>
> but no matter the combinations I try, I cannot get it to work.
>
> Thew data colum contains html color codes like "0099FF" and I want to
> convert these to, in this case, "0 153 255".
>
> The following code behaves well:
>
> SELECT  x'00'::integer || ' ' || x'99'::integer || ' ' ||  
> x'FF'::integer;
>
> resulting in "0 153 255". All correct
>
> I was hopeful that something similar to the following would work but I
> just cannot get it to work despite trying various combinations.
>
> SELECT x'substring(col,1,2)'::integer || ' ' ||
> x'substring(col,3,2)'::integer || ' ' ||
> x'substring(col,5,2)'::integer
>
> I would much prefer to do this as part of the query rather than having
> to create a function. There must be a way! :)
>
> Any help would be very greatly apprecaited.

Hrmph. I took a look at this and couldn't figure out how to make it work
without declaring a PL/pgSQL function, so that I could construct a dynamic
query using EXECUTE. I googled around a bit, and found a few people having
roughly your same problem: I think this comes from Postgres not having a
counterpart to its built-in to_hex() function.

Anyway, I adapted the function from here
<http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php> to use
more modern function syntax, and came up with this:

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$
DECLARE
result int;
BEGIN
EXECUTE 'SELECT x''' || hexval || '''::int' INTO result; RETURN result;
END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;

which you should be able to use like this:

SELECT hex_to_int(substring(color,1,2)) AS first,
hex_to_int(substring(color,3,2)) AS second,
hex_to_int(substring(color, 5,2)) AS third
FROM colors;

first | second | third
-------+--------+-------
0 | 153 | 255
(1 row)

I know it's not exactly what you were looking for, but IMO the cleanest way
to do this anyway would be to make a wrapper function like
"html_color_to_int()" that would handle all this conversion for you in one
place.

Josh

------------------------------------------------------

Thank you for your reply, Josh.

I was rather hoping not to have to call a function but it seems like it
might be the only way. I am actually using the query in a MapServer map file
and I think I may only be able to use one line of code i.e. I have to get
everything I need from the single query. I am assuming that I can add a
function to PostgreSQL so that it is available globally to any query and I
will have to do a wee bit of research to see if that is the case.

If anyone knows how to make a suggestion for inclusion in future PostgreSQL
releases then I think that a Hex_to_Int function such as
x'db_column'::integer, where db_column can be replaced by a variable, would
be a very good idea.

Many thanks once again.

Regards,

Donald

Probably not the correct place to post this message but is there a means
whereby I can post a suggestion to a PostgreSQL wishlist?

Many thanks.

Regards,

Donald Kerr

On 11/12/2010 12:00 PM, Donald Kerr wrote:
> Probably not the correct place to post this message but is there a means
> whereby I can post a suggestion to a PostgreSQL wishlist?
>

In my experience, the best first-step is to ask for help. Explain what
you are trying to solve or the problem you are encountering. In many if
not most cases there is either a solution or explanation. If not, you
will at least have better info about how (and whether or not it would be
productive) to frame your request.

Cheers,
Steve

Steve,

I have already posted asking for help (see below) but hit a brick wall. I
would like to see a modification to allow the conversion from Hex to Dec.

-------------------------------
I am trying to convert from hex to decimal and can do that successfully
using the following code:

SELECT x'FF'::integer;

which outputs 255 and is exactly what I want.

I want to substitute the string in the code 'FF' for a column in the
database like so:

SELECT x'db_column'::integer FROM db_table;

but no matter the combinations I try, I cannot get it to work.

Thew data colum contains html color codes like "0099FF" and I want to
convert these to, in this case, "0 153 255".

The following code behaves well:

SELECT x'00'::integer || ' ' || x'99'::integer || ' ' || x'FF'::integer;

resulting in "0 153 255". All correct

I was hopeful that something similar to the following would work but I just
cannot get it to work despite trying various combinations.

SELECT x'substring(col,1,2)'::integer || ' ' ||
x'substring(col,3,2)'::integer || ' ' || x'substring(col,5,2)'::integer
-------------------------------

Many thanks.

Regards,

Donald

-----Original Message-----
From: Steve Crawford [mailto:scrawford(at)pinpointresearch(dot)com]
Sent: 12 November 2010 21:01
To: Donald Kerr
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Postgres Wishlist

On 11/12/2010 12:00 PM, Donald Kerr wrote:
> Probably not the correct place to post this message but is there a
> means whereby I can post a suggestion to a PostgreSQL wishlist?
>

In my experience, the best first-step is to ask for help. Explain what
you are trying to solve or the problem you are encountering. In many if
not most cases there is either a solution or explanation. If not, you
will at least have better info about how (and whether or not it would be
productive) to frame your request.

Cheers,
Steve

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1153 / Virus Database: 424/3253 - Release Date: 11/12/10

"Donald Kerr" <donald(dot)kerr(at)dkerr(dot)co(dot)uk> writes:
> I am trying to convert from hex to decimal and can do that successfully
> using the following code:

> SELECT x'FF'::integer;

> which outputs 255 and is exactly what I want.

What you're doing there is abusing the bit-string-literal syntax.
I think you could get access to the same behavior for a non-constant
input like this:

SELECT 'xff'::text::bit(8)::int;

or in practice

SELECT ('x' || some-string-variable)::bit(8)::int;

This is relying on some undocumented behavior of the bit-type input
converter, but I see no reason to expect that would break. A possibly
bigger issue is that it requires PG >= 8.3 since there wasn't a text
to bit cast before that.

> Thew data colum contains html color codes like "0099FF" and I want to
> convert these to, in this case, "0 153 255".

It seems very unlikely that you're going to get any built-in solution
to that. You don't have just a hex-to-decimal conversion problem; you
also have the problems of splitting the given string into three pieces
and deciding how you're going to represent the three-component result of
the conversion. And both of your choices there seem pretty arbitrary/
specialized.

I think your best bet would be to write a custom function that does what
you want. This'd likely be a one-liner in plperl, for example. You
could do it in plpgsql if you don't want to rely on plperl being
installed, but it'd be a tad more tedious.

regards, tom lane

On 11/12/2010 01:38 PM, Tom Lane wrote:
> ...I think your best bet would be to write a custom function that does
> what
> you want.
>

Quick Googling shows one generic hex to int function:
http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php

Perhaps it is a good starting point for what you want.

Cheers,
Steve

Thank you, Tom.

I have tried what you suggest but it does not seem to work:
'x'||substring(col,3,2)::text::bit(8)::int returns - "9" is not a valid
binary digit.

Test SQL below:
SELECT col, 'x'||substring(col,3,2)::text::bit(8)::int AS Test,
x'00'::integer || ' ' || x'99'::integer || ' ' || x'FF'::integer AS oscolor
FROM cartographictext WHERE COL <> '000000' LIMIT 10

Many thanks.

Regards,

Donald

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 12 November 2010 21:38
To: Donald Kerr
Cc: 'Steve Crawford'; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Postgres Wishlist

"Donald Kerr" <donald(dot)kerr(at)dkerr(dot)co(dot)uk> writes:
> I am trying to convert from hex to decimal and can do that
> successfully using the following code:

> SELECT x'FF'::integer;

> which outputs 255 and is exactly what I want.

What you're doing there is abusing the bit-string-literal syntax. I think
you could get access to the same behavior for a non-constant input like
this:

SELECT 'xff'::text::bit(8)::int;

or in practice

SELECT ('x' || some-string-variable)::bit(8)::int;

This is relying on some undocumented behavior of the bit-type input
converter, but I see no reason to expect that would break. A possibly
bigger issue is that it requires PG >= 8.3 since there wasn't a text to bit
cast before that.

> Thew data colum contains html color codes like "0099FF" and I want to
> convert these to, in this case, "0 153 255".

It seems very unlikely that you're going to get any built-in solution to
that. You don't have just a hex-to-decimal conversion problem; you also
have the problems of splitting the given string into three pieces and
deciding how you're going to represent the three-component result of the
conversion. And both of your choices there seem pretty arbitrary/
specialized.

I think your best bet would be to write a custom function that does what you
want. This'd likely be a one-liner in plperl, for example. You could do it
in plpgsql if you don't want to rely on plperl being installed, but it'd be
a tad more tedious.

regards, tom lane

On Nov 13, 2010, at 3:32 , Donald Kerr wrote:

> Thank you, Tom.
>
> I have tried what you suggest but it does not seem to work:
> 'x'||substring(col,3,2)::text::bit(8)::int returns - "9" is not a valid
> binary digit.

Try with parens:

postgres=# select ('x' || 99::text)::bit(8)::int;
int4
------
153
(1 row)

postgres=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.1 on x86_64-apple-darwin10.4.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit
(1 row)

Michael Glaesemann
grzm seespotcode net

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;

SELECT col, hex_to_int(substring(col,1,2)) || ' ' ||
hex_to_int(substring(col,3,2)) || ' ' || hex_to_int(substring(col,5,2)) AS
oscolor FROM cartographictext WHERE COL <> '000000' LIMIT 10

Returns:
"0099FF";"0 153 255"
-----------------------------------

But, here's my additional problem ... I am creating the query in Mapserver
and sending it to PostGreSQL and I can only use one line of code i.e.
everything has to be part of the one line.

Is it possible to make this function available globally withing PostgreSQL?

Many thanks.

Regards,

Donald

-----Original Message-----
From: Steve Crawford [mailto:scrawford(at)pinpointresearch(dot)com]
Sent: 12 November 2010 21:48
To: Donald Kerr
Cc: Tom Lane; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Postgres Wishlist

On 11/12/2010 01:38 PM, Tom Lane wrote:
> ...I think your best bet would be to write a custom function that does
> what
> you want.
>

Quick Googling shows one generic hex to int function:
http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php

Perhaps it is a good starting point for what you want.

Cheers,
Steve

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1153 / Virus Database: 424/3253 - Release Date: 11/12/10

Michael,

First class :) Problem solved!!!

=======================
SELECT col, ('x'||substring(col,1,2))::text::bit(8)::int || ' ' ||
('x'||substring(col,3,2))::text::bit(8)::int || ' ' ||
('x'||substring(col,1,2))::text::bit(8)::int AS oscolor FROM
cartographictext WHERE COL <> '000000' LIMIT 10

Returns:
"0099FF";"0 153 0"
"FF00FF";"255 0 255"
Etc.

Thank you very much to everyone who helped me with this problem.

Regards,

Donald

-----Original Message-----
From: Michael Glaesemann [mailto:grzm(at)seespotcode(dot)net]
Sent: 13 November 2010 08:45
To: Donald Kerr
Cc: 'Tom Lane'; 'Steve Crawford'; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Postgres Wishlist

On Nov 13, 2010, at 3:32 , Donald Kerr wrote:

> Thank you, Tom.
>
> I have tried what you suggest but it does not seem to work:
> 'x'||substring(col,3,2)::text::bit(8)::int returns - "9" is not a
> valid binary digit.

Try with parens:

postgres=# select ('x' || 99::text)::bit(8)::int;
int4
------
153
(1 row)

postgres=# select version();
version

----------------------------------------------------------------------------
--------------------------------------------------------------
PostgreSQL 9.0.1 on x86_64-apple-darwin10.4.0, compiled by GCC
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit (1
row)

Michael Glaesemann
grzm seespotcode net

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

How about something like this:

create function dec2hex(integer) returns text
as $$
my $arg=shift;
return(sprintf("%x",$arg));
$$ language plperl;

scott=# select dec2hex(255);
dec2hex
---------
ff
(1 row)

It also works on the table columns:

scott=> select ename,dec2hex(sal::int) from emp;
ename | dec2hex
--------+---------
SMITH | 320
ALLEN | 640
WARD | 4e2
JONES | b9f
MARTIN | 4e2
BLAKE | b22
CLARK | 992
SCOTT | bb8
KING | 1388
TURNER | 5dc
ADAMS | 44c
JAMES | 3b6
FORD | bb8
MILLER | 514
(14 rows)

If the reverse function is needed, perl has a function called "hex".

Donald Kerr wrote:
> Michael,
>
> First class :) Problem solved!!!
>
> =======================
> SELECT col, ('x'||substring(col,1,2))::text::bit(8)::int || ' ' ||
> ('x'||substring(col,3,2))::text::bit(8)::int || ' ' ||
> ('x'||substring(col,1,2))::text::bit(8)::int AS oscolor FROM
> cartographictext WHERE COL <> '000000' LIMIT 10
>
> Returns:
> "0099FF";"0 153 0"
> "FF00FF";"255 0 255"
> Etc.
>
> Thank you very much to everyone who helped me with this problem.
>
> Regards,
>
> Donald
>
>
>
>
> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm(at)seespotcode(dot)net]
> Sent: 13 November 2010 08:45
> To: Donald Kerr
> Cc: 'Tom Lane'; 'Steve Crawford'; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Postgres Wishlist
>
>
>
> On Nov 13, 2010, at 3:32 , Donald Kerr wrote:
>
>
>> Thank you, Tom.
>>
>> I have tried what you suggest but it does not seem to work:
>> 'x'||substring(col,3,2)::text::bit(8)::int returns - "9" is not a
>> valid binary digit.
>>
>
> Try with parens:
>
> postgres=# select ('x' || 99::text)::bit(8)::int;
> int4
> ------
> 153
> (1 row)
>
> postgres=# select version();
> version
>
> ----------------------------------------------------------------------------
> --------------------------------------------------------------
> PostgreSQL 9.0.1 on x86_64-apple-darwin10.4.0, compiled by GCC
> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit (1
> row)
>
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>

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

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

Mladen Gogala wrote:
> It works like a charm:
> CREATE OR REPLACE FUNCTION hex2dec(text)
> RETURNS int
> AS $$
> my $arg=shift;
> return(hex($arg));
> $$ LANGUAGE plperl;
>
>
I've mistakenly posted the same thing twice. This is what I meant:

scott=# select hex2dec('FF');
hex2dec
---------
255
(1 row)

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

Hi Donald

On 13 November 2010 10:46, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
> Steve,
>
> That works a treat:
>
> -----------------------------------
> CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS '
[...]
>
> SELECT col, hex_to_int(substring(col,1,2)) || ' ' ||
> hex_to_int(substring(col,3,2)) || ' ' ||  hex_to_int(substring(col,5,2)) AS
> oscolor FROM cartographictext WHERE COL <> '000000' LIMIT 10
>
> Returns:
> "0099FF";"0 153 255"
> -----------------------------------
>
> But, here's my additional problem ... I am creating the query in Mapserver
> and sending it to PostGreSQL and I can only use one line of code i.e.
> everything has to be part of the one line.

I think you are misunderstanding something. Once you have created the
function (using CREATE FUNCTION, or CREATE OR REPLACE FUNCTION) it
exists in the database and you can call it (with a single line of
code) any time you like after that. i.e. you do the CREATE OR REPLACE
FUNCTION ...; once off. Then you should be able to tell Mapserver to
call SELECT hex_to_int(...) ...;

> Is it possible to make this function available globally withing PostgreSQL?

As far as I understand it, that *is* how they work. Well, global to
the database you define it in. Of course you can use GRANT and REVOKE
to control who can run the function.

Have a look at the following:
http://www.postgresql.org/docs/8.4/static/xfunc.html
http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

--
Michael Wood <esiotrot(at)gmail(dot)com>

Michael,

Thanks for your input and that certainly clears things up in terms of
resolving the problem by using a function.

However, my problem was solved using the following code:

SELECT col, ('x'||substring(col,1,2))::text::bit(8)::int || ' ' ||
('x'||substring(col,3,2))::text::bit(8)::int || ' ' ||
('x'||substring(col,1,2))::text::bit(8)::int AS oscolor FROM
cartographictext WHERE COL <> '000000' LIMIT 10

Returns:
"0099FF";"0 153 0"
"FF00FF";"255 0 255"

I know it is maybe an undocumented feature but it works, is only one query
and it is portable between Postgres servers without having to create a
custom function.

Many thanks.

Regards,

Donald

-----Original Message-----
From: Michael Wood [mailto:esiotrot(at)gmail(dot)com]
Sent: 13 November 2010 20:12
To: Donald Kerr
Cc: Steve Crawford; Tom Lane; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Postgres Wishlist

Hi Donald

On 13 November 2010 10:46, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
> Steve,
>
> That works a treat:
>
> -----------------------------------
> CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS '
[...]
>
> SELECT col, hex_to_int(substring(col,1,2)) || ' ' ||
> hex_to_int(substring(col,3,2)) || ' ' ||
> hex_to_int(substring(col,5,2)) AS oscolor FROM cartographictext WHERE
> COL <> '000000' LIMIT 10
>
> Returns:
> "0099FF";"0 153 255"
> -----------------------------------
>
> But, here's my additional problem ... I am creating the query in
> Mapserver and sending it to PostGreSQL and I can only use one line of
> code i.e. everything has to be part of the one line.

I think you are misunderstanding something. Once you have created the
function (using CREATE FUNCTION, or CREATE OR REPLACE FUNCTION) it exists in
the database and you can call it (with a single line of
code) any time you like after that. i.e. you do the CREATE OR REPLACE
FUNCTION ...; once off. Then you should be able to tell Mapserver to call
SELECT hex_to_int(...) ...;

> Is it possible to make this function available globally withing
> PostgreSQL?

As far as I understand it, that *is* how they work. Well, global to the
database you define it in. Of course you can use GRANT and REVOKE to
control who can run the function.

Have a look at the following:
http://www.postgresql.org/docs/8.4/static/xfunc.html
http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

--
Michael Wood <esiotrot(at)gmail(dot)com>

Hi

On 13 November 2010 22:25, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
> Michael,
>
> Thanks for your input and that certainly clears things up in terms of
> resolving the problem by using a function.
>
> However, my problem was solved using the following code:
[...]

Yes, I saw that, but thought I would clarify anyway :)

> I know it is maybe an undocumented feature but it works, is only one query
> and it is portable between Postgres servers without having to create a
> custom function.

If that's important then of course do it like you are doing, but in
other situations it might make more sense to create the function. In
this case the function would not get around the use of an undocumented
feature anyway, unless you went with pgperl or another language.

> Many thanks.

No problem.

--
Michael Wood <esiotrot(at)gmail(dot)com>

>>On 13 November 2010 22:25, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
>> Michael,
>>
>> Thanks for your input and that certainly clears things up in terms of
>> resolving the problem by using a function.
>>
>> However, my problem was solved using the following code:
[...]

Yes, I saw that, but thought I would clarify anyway :)

I appreciated that. Thank you.

Regards,

Donald