Skip site navigation (1) Skip section navigation (2)

Re: Postgres Wishlist

From: "Donald Kerr" <donald(dot)kerr(at)dkerr(dot)co(dot)uk>
To: "'Steve Crawford'" <scrawford(at)pinpointresearch(dot)com>
Cc: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Postgres Wishlist
Date: 2010-11-13 08:46:03
Message-ID: D36068AA89CC4DB0AA5121363D87BEB4@DELLM4500 (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Donald KerrDate: 2010-11-13 08:53:47
Subject: Re: Postgres Wishlist
Previous:From: Michael GlaesemannDate: 2010-11-13 08:44:53
Subject: Re: Postgres Wishlist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group