Re: Postgres Wishlist

Michael,

Thanks for your input and that certainly clears things up in terms of
resolving the problem by using a function.

However, my problem was solved using the following code:

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"

I know it is maybe an undocumented feature but it works, is only one query
and it is portable between Postgres servers without having to create a
custom function.

Many thanks.

Regards,

Donald

-----Original Message-----
From: Michael Wood [mailto:esiotrot(at)gmail(dot)com]
Sent: 13 November 2010 20:12
To: Donald Kerr
Cc: Steve Crawford; Tom Lane; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Postgres Wishlist

Hi Donald

On 13 November 2010 10:46, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
> Steve,
>
> That works a treat:
>
> -----------------------------------
> CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS '
[...]
>
> 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.

I think you are misunderstanding something. Once you have created the
function (using CREATE FUNCTION, or CREATE OR REPLACE FUNCTION) it exists in
the database and you can call it (with a single line of
code) any time you like after that. i.e. you do the CREATE OR REPLACE
FUNCTION ...; once off. Then you should be able to tell Mapserver to call
SELECT hex_to_int(...) ...;

> Is it possible to make this function available globally withing
> PostgreSQL?

As far as I understand it, that *is* how they work. Well, global to the
database you define it in. Of course you can use GRANT and REVOKE to
control who can run the function.

Have a look at the following:
http://www.postgresql.org/docs/8.4/static/xfunc.html
http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

--
Michael Wood <esiotrot(at)gmail(dot)com>