Skip site navigation (1) Skip section navigation (2)

Re: Postgres Wishlist

From: Michael Wood <esiotrot(at)gmail(dot)com>
To: Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk>
Cc: Steve Crawford <scrawford(at)pinpointresearch(dot)com>, 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 20:11:41
Message-ID: AANLkTikwzv2ngLMQSMMFRYL4iPhmjMa7mUO3S4uN3=ws@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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>

In response to

Responses

pgsql-novice by date

Next:From: Donald KerrDate: 2010-11-13 20:25:46
Subject: Re: Postgres Wishlist
Previous:From: Mladen GogalaDate: 2010-11-13 19:03:27
Subject: Re: Postgres Wishlist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group