Message-ID: <41E3B9FD.9040907@archonet.com>
Date: Tue, 11 Jan 2005 11:35:25 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Kieran Ashley <krashley@space.qinetiq.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Parsing a Calculation from a field
References: <6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb>
In-Reply-To: <6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Kieran Ashley wrote:
> 
> Some of our data comes in in a format which provides us with extra
> information, but which we currently don't need to use; specifically
> we sometimes receive information in the form of calculations, for
> example a column which needs to be transformed to an integer is
> initially of type varchar, and contains values like:
> 
> 
> 
> 6
> 10
> 2
> 4 + 8
> 2 + 4 + 8
> NULL
> 4
[snip]
> I could obviously add an extra step to the procedure and write a
> script using something like PHP to scan the table and carry out any
> necessary calculations in advance, but I'm curious to know if there
> is a way within Postgres to do it either as part of the select query
> itself, or as a PL/SQL procedure.  I've had a quick look at the
> string handling functions but I can't see any way to parse integers
> out of a varchar column - which would seem to prohibit using
> substring to manually break up the sums.

You should look into eval() - available in most scripting languages in 
some form or other. It treats its parameter as code/an expression and 
evaluates it. WARNING - can be vulnerable to abuse, make sure you trust 
or clean your input data first.

You could probably do it in pl/perl or pl/perlu, pl/tcl. Perhaps pl/php 
too. The only way I can think to do it in pl/pgsql would be to have a 
line like:

   eval_qry := ''SELECT ('' || $1 || '')::integer AS result''

Then use FOR..IN..EXECUTE to get the results.

HTH
--
   Richard Huxton
   Archonet Ltd