Re: Postgres Wishlist

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@pinpointresearch.com]
Sent: 12 November 2010 21:48
To: Donald Kerr
Cc: Tom Lane; pgsql-novice@postgresql.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