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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

  From Date Subject
Next Message Donald Kerr 2010-11-13 08:53:47 Re: Postgres Wishlist
Previous Message Michael Glaesemann 2010-11-13 08:44:53 Re: Postgres Wishlist