Re: Postgres Wishlist

Hi Donald

On 13 November 2010 10:46, Donald Kerr <donald.kerr@dkerr.co.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@gmail.com>