Re: Postgres Wishlist - Mailing list pgsql-novice
From | Donald Kerr |
---|---|
Subject | Re: Postgres Wishlist |
Date | |
Msg-id | CED0B1237528483EBF6E2CA6BDF36304@DELLM4500 Whole thread Raw |
In response to | Re: Postgres Wishlist (Michael Wood <esiotrot@gmail.com>) |
Responses |
Re: Postgres Wishlist
|
List | pgsql-novice |
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@gmail.com] Sent: 13 November 2010 20:12 To: Donald Kerr Cc: Steve Crawford; Tom Lane; pgsql-novice@postgresql.org Subject: Re: [NOVICE] 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>
pgsql-novice by date: