From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Kieran Ashley <krashley(at)space(dot)qinetiq(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Parsing a Calculation from a field |
Date: | 2005-01-11 11:35:25 |
Message-ID: | 41E3B9FD.9040907@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Peterson | 2005-01-11 13:38:21 | Re: simulating row ownership |
Previous Message | Kieran Ashley | 2005-01-11 11:20:39 | Parsing a Calculation from a field |