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, whole thread or download thread mbox)
Lists: pgsql-novice

That works a treat:

CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS '
  h alias for $1;
  exec varchar;
  curs refcursor;
  res  int;
 exec := ''SELECT x'''''' || h || ''''''::int'';
 OPEN curs FOR EXECUTE exec;
 FETCH curs INTO res;
 CLOSE curs;
 return res;
LANGUAGE 'plpgsql'

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 

"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.



-----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:

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


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

In response to


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-2017 The PostgreSQL Global Development Group