Parsing a Calculation from a field

Hi,

Apologies if this is the wrong list for this question, I hope it’s not.

I’m porting an application to PostgreSQL, and rewriting a number of transforms used to convert incoming Excel data into a final Postgres table schema for an application.  Most of it’s gone okay, but there’s one column giving me trouble.

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 need to come up with some code that can somehow treat the contents of this field as a potential sum, and execute that if necessary – something along the lines of ‘select exec(FIELDNAME) from TABLE’.

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.

Any help on this would be greatly appreciated.

Kieran