[snip]
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
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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
