From: | Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> |
---|---|
To: | Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> |
Cc: | 'Michael Glaesemann' <grzm(at)seespotcode(dot)net>, 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 'Steve Crawford' <scrawford(at)pinpointresearch(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Postgres Wishlist |
Date: | 2010-11-13 17:59:39 |
Message-ID: | 4CDED20B.4090101@vmsinfo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mladen Gogala | 2010-11-13 18:07:58 | Re: Postgres Wishlist |
Previous Message | Michael Glaesemann | 2010-11-13 10:00:30 | Re: Postgres Wishlist |