Re: Parsing a Calculation from a field

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