[SQL] 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 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.
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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] simulating row ownership
On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote: > I have a table where I want everyone to be able to be able to insert > and select. > But they should only be able to update and delete rows that they > "own". The table has a column indicating the owner. > What is the best way to accomplish this? I'm not real familiar with > rules, but it seems that I can do this with rules for update and > delete applied to the table. Using rules, you could do something like the following: CREATE TABLE test ( aname TEXT PRIMARY KEY ); INSERT INTO test ( aname ) VALUES ( 'aaa' ); INSERT INTO test ( aname ) VALUES ( 'yourusername' ); CREATE RULE lock_test_user_update AS ON UPDATE TO test WHERE old.aname = CURRENT_USER DO INSTEAD nothing; CREATE RULE lock_test_user_delete AS ON DELETE TO test WHERE old.aname = CURRENT_USER DO INSTEAD nothing; -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simulating row ownership
On Tue, Jan 11, 2005 at 08:38:21AM -0500, rpeterso wrote: > CREATE RULE lock_test_user_update > AS ON UPDATE TO test > WHERE old.aname = CURRENT_USER > DO INSTEAD nothing; > > CREATE RULE lock_test_user_delete > AS ON DELETE TO test > WHERE old.aname = CURRENT_USER > DO INSTEAD nothing; For your example, these rules should say !=, of course... -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Parsing a Calculation from a field
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:[EMAIL PROTECTED]
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.
#
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Single row tables
Hi, with what constraint or how can I ensure that one of my tables has exact one record or 0..1 records? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Parsing a Calculation from a field
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Single row tables
I suppose you could put a check constraint that forces a single value on a column with a unique index? Or an insert trigger that always inserts the same value into a unique field. Out of curiosity, why do you need to do this? Dmitri -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of KÖPFERL Robert Sent: Tuesday, January 11, 2005 9:46 AM Cc: pgsql-sql@postgresql.org Subject: [SQL] Single row tables Importance: High Hi, with what constraint or how can I ensure that one of my tables has exact one record or 0..1 records? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Single row tables
Also I thought of an UNIQUE constraint on a column with =const constraint. But It's for global configuration data that exists only once. Making a key value thing was to weak typed for my taste. > -Original Message- > From: Dmitri Bichko [mailto:[EMAIL PROTECTED] > Sent: Dienstag, 11. Jänner 2005 15:50 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: RE: [SQL] Single row tables > > > I suppose you could put a check constraint that forces a > single value on a column with a unique index? Or an insert > trigger that always inserts the same value into a unique field. > > Out of curiosity, why do you need to do this? > > Dmitri > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of KÖPFERL Robert > Sent: Tuesday, January 11, 2005 9:46 AM > Cc: pgsql-sql@postgresql.org > Subject: [SQL] Single row tables > Importance: High > > > Hi, > > with what constraint or how can I ensure that one of my > tables has exact one record or 0..1 records? > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Simple Question
Hello: I'm trying to figure out how to convert a floating point value into an interval of time. I'm calculating the time required to drive from point A to point B. For the sake of this question, we'll just say it is miles/speed. So: drv_time = 478 / 45.0; The value of this is: 10.6222 Is there a way of converting this value to an interval. It seems that INTERVAL only works with a quoted literal value. If I type: rnd=# select interval '10.8444 hours'; interval @ 10 hours 50 mins 40 secs (1 row) Anybody have an pointers? Thanks... Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Simple Question
I figured it out. This works: travel_window INTERVAL; drv_time FLOAT; drv_time = miles / 45.0;-- drive time travel_window = quote_literal(drv_time || '' hours''); The variable, travel_window becomes: @ 10 hours 50 mins 40 secs, which is what I wanted. If anybody knows any other ways, I'd be interested in see that too. On Tuesday 11 January 2005 04:42 pm, Terry Lee Tucker saith: > Hello: > > I'm trying to figure out how to convert a floating point value into an > interval of time. I'm calculating the time required to drive from point A > to point B. For the sake of this question, we'll just say it is > miles/speed. So: > > drv_time = 478 / 45.0; > > The value of this is: 10.6222 > > Is there a way of converting this value to an interval. It seems that > INTERVAL only works with a quoted literal value. > > If I type: > rnd=# select interval '10.8444 hours'; > interval > > @ 10 hours 50 mins 40 secs > (1 row) > > Anybody have an pointers? > > Thanks... > > > Work: 1-336-372-6812 > Cell: 1-336-363-4719 > email: [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Simple Question
On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote: > Subject: [SQL] Simple Question Please use a more descriptive subject -- think about how somebody looking at a list of 200 messages, all with subjects like "Simple Question" or "PostgreSQL Question," would decide to look at yours. > drv_time = 478 / 45.0; > > The value of this is: 10.6222 > > Is there a way of converting this value to an interval. It seems that > INTERVAL > only works with a quoted literal value. You can do arithmetic on intervals: SELECT 478 / 45.0 * interval'1 hour'; For more information, see "Date/Time Functions and Operators" in the documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Simple Question
Convert to seconds first (3600 sec/hr) : select ( '3600'::int4 * '478'::int4 / '45.0'::float8 )::int4::reltime::interval ; interval -- 10:37:20 (1 row) I don't know if "::int4::reltime::interval" is the best way to end up with an interval, but its the only way I could figure out how to do it off the top of my head. On Tue, 2005-11-01 at 16:42 -0500, Terry Lee Tucker wrote: > Hello: > > I'm trying to figure out how to convert a floating point value into an > interval of time. I'm calculating the time required to drive from point A to > point B. For the sake of this question, we'll just say it is miles/speed. So: > > drv_time = 478 / 45.0; > > The value of this is: 10.6222 > > Is there a way of converting this value to an interval. It seems that > INTERVAL > only works with a quoted literal value. > > If I type: > rnd=# select interval '10.8444 hours'; > interval > > @ 10 hours 50 mins 40 secs > (1 row) > > Anybody have an pointers? > > Thanks... > > > Work: 1-336-372-6812 > Cell: 1-336-363-4719 > email: [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Simple Question
On Tue, 11 Jan 2005, Terry Lee Tucker wrote: > Hello: > > I'm trying to figure out how to convert a floating point value into an > interval of time. I'm calculating the time required to drive from point A to > point B. For the sake of this question, we'll just say it is miles/speed. So: > > drv_time = 478 / 45.0; > > The value of this is: 10.6222 I think something like 478/45.0 * interval '1 hour' may do what you want. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Simple Question
Thank you for the reply in spite of the subject. On Tuesday 11 January 2005 05:15 pm, Michael Fuhr saith: > On Tue, Jan 11, 2005 at 04:42:21PM -0500, Terry Lee Tucker wrote: > > Subject: [SQL] Simple Question > > Please use a more descriptive subject -- think about how somebody > looking at a list of 200 messages, all with subjects like "Simple > Question" or "PostgreSQL Question," would decide to look at yours. I will do this in the future. > > > drv_time = 478 / 45.0; > > > > The value of this is: 10.6222 > > > > Is there a way of converting this value to an interval. It seems that > > INTERVAL only works with a quoted literal value. > > You can do arithmetic on intervals: > > SELECT 478 / 45.0 * interval'1 hour'; I like your soultion better than mine. Thanks for the answer. > > For more information, see "Date/Time Functions and Operators" in > the documentation. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Simple Question
Thanks for the reply. My answer was a little different than yours because I used 488 instead of 478. Well, that three ways so far ;o) On Tuesday 11 January 2005 05:06 pm, Guy Fraser saith: > Convert to seconds first (3600 sec/hr) : > > select ( > '3600'::int4 > * '478'::int4 > / '45.0'::float8 > )::int4::reltime::interval ; > interval > -- > 10:37:20 > (1 row) > > I don't know if "::int4::reltime::interval" is the best > way to end up with an interval, but its the only way I > could figure out how to do it off the top of my head. > > On Tue, 2005-11-01 at 16:42 -0500, Terry Lee Tucker wrote: > > Hello: > > > > I'm trying to figure out how to convert a floating point value into an > > interval of time. I'm calculating the time required to drive from point A > > to point B. For the sake of this question, we'll just say it is > > miles/speed. So: > > > > drv_time = 478 / 45.0; > > > > The value of this is: 10.6222 > > > > Is there a way of converting this value to an interval. It seems that > > INTERVAL only works with a quoted literal value. > > > > If I type: > > rnd=# select interval '10.8444 hours'; > > interval > > > > @ 10 hours 50 mins 40 secs > > (1 row) > > > > Anybody have an pointers? > > > > Thanks... > > > > > > Work: 1-336-372-6812 > > Cell: 1-336-363-4719 > > email: [EMAIL PROTECTED] > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > -- > Guy Fraser > Network Administrator > The Internet Centre > 1-888-450-6787 > (780)450-6787 > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Simple Question
Terry Lee Tucker <[EMAIL PROTECTED]> writes: > I'm trying to figure out how to convert a floating point value into an > interval of time. Use something like regression=# select (478 / 45.0) * '1 hour'::interval; ?column? -- 10:37:20 (1 row) ... or whatever other scale factor you have in mind. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Single row tables
On Tuesday 11 January 2005 17:45, KÖPFERL Robert wrote: > Hi, > > with what constraint or how can I ensure that one of my tables has exact > one record or 0..1 records? A trigger procedure BEFORE INSERT would help you. And a column type with only one possible field value would help you if you define UNIQUE INDEX on a field of this (user-defined) type. ---(end of broadcast)--- TIP 8: explain analyze is your friend
