From: Richard Huxton <dev@archonet.com>
To: Kieran Ashley <krashley@space.qinetiq.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Parsing a Calculation from a field
Date: Tue, 11 Jan 2005 11:35:25 +0000
Message-ID: <41E3B9FD.9040907@archonet.com> (raw)
In-Reply-To: <6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb>
References: <6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, krashley@space.qinetiq.com
Subject: Re: Parsing a Calculation from a field
In-Reply-To: <41E3B9FD.9040907@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox