pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedParsing a Calculation from a field
4+ messages / 3 participants
[nested] [flat]
* Parsing a Calculation from a field
@ 2005-01-11 11:20 Kieran Ashley <krashley@space.qinetiq.com>
2005-01-11 11:35 ` Re: Parsing a Calculation from a field Richard Huxton <dev@archonet.com>
2005-01-11 14:50 ` Re: Parsing a Calculation from a field Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Kieran Ashley @ 2005-01-11 11:20 UTC (permalink / raw)
To: pgsql-sql
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
#############################################################
The information contained in this email and any subsequent
correspondence is private and is intended solely for the
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any
action taken or omitted to be taken in reliance on such
information is prohibited and may be unlawful.
#############################################################
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parsing a Calculation from a field
2005-01-11 11:20 Parsing a Calculation from a field Kieran Ashley <krashley@space.qinetiq.com>
@ 2005-01-11 11:35 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2005-01-11 11:35 UTC (permalink / raw)
To: Kieran Ashley <krashley@space.qinetiq.com>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parsing a Calculation from a field
2005-01-11 11:20 Parsing a Calculation from a field Kieran Ashley <krashley@space.qinetiq.com>
@ 2005-01-11 14:50 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Eckermann @ 2005-01-11 14:50 UTC (permalink / raw)
To: pgsql-sql
How are you getting the data from Excel? Perhaps you could use Excel's own
methods to evaluate the cell contents? You may still need to do something
for literal text values (e.g. 'NULL'), though.
"Kieran Ashley" <krashley@space.qinetiq.com> wrote in message
news:6665151E3647D711B27B0090277C004F9AF681@ntexch02s.scs.dra.hmg.gb...
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
Note:
The information contained in this email and any subsequent correspondence is
private and is intended solely for the intended recipient(s). For those
other than the intended recipient(s) any disclosure, copying, distribution,
or any action taken or omitted to be taken in reliance on such information
is prohibited and may be unlawful.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Parsing a Calculation from a field
@ 2005-01-11 14:50 Kieran Ashley <krashley@space.qinetiq.com>
0 siblings, 0 replies; 4+ messages in thread
From: Kieran Ashley @ 2005-01-11 14:50 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Thanks for your help Richard,
I tried a number of Pl/SQL approaches, but couldn't get through the type-checking. I finally knocked out a little PL/TCL script that seems to get the job done:
create function eval_sums(varchar(8000)) returns integer as '
if {[argisnull 1]} { return_null }
return [expr $1]
'
language pltcl;
Hopefully this might help out anyone else faced with a similar problem.
Kieran
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 11 January 2005 11:35
To: Kieran Ashley
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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
#############################################################
The information contained in this email and any subsequent
correspondence is private and is intended solely for the
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any
action taken or omitted to be taken in reliance on such
information is prohibited and may be unlawful.
#############################################################
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-01-11 14:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-11 11:20 Parsing a Calculation from a field Kieran Ashley <krashley@space.qinetiq.com>
2005-01-11 11:35 ` Richard Huxton <dev@archonet.com>
2005-01-11 14:50 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2005-01-11 14:50 Re: Parsing a Calculation from a field Kieran Ashley <krashley@space.qinetiq.com>
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