Howdy:
Running Postgres 7.1.3 on RedHat Linux 7.2.
I'd like to create a table where I get two columns
on which I can do math, but also derive the percentage
difference in a new column.
So far:
I have a perl script that does a count from another
table and populates a new table with the results.
Takes awhile, but it works. Now, in the new table,
I want everytime I run the script again I want to
populate the next column with the *new* number
and then give the percent change.
Example:
[table script]
#!/usr/bin/perl -w
use DBI;
my $dbh=DBI->connect('dbi:Pg:dbname=database', 'joe')
or die "Can not connect: $!";
$dbh->do("create table t_trend (
count int,
diff int, #<-- difference between first run and now
p_chng int, #<-- difference between column one and two (%)
type varchar,
updated datetime #<-- updated by now() function
)"
);
$dbh->do("grant select, update on t_trend to public");
$dbh->disconnect;
[/table script]
-- and excerpt from second script --
[snip]
while (my ($syscount, $systype)=$sth->fetchrow) {
print COUNTFILE "$syscount\t$systype\n";
# insert data into table
$dbh->do("insert into t_trend values (
'$syscount',
# want to put difference here
# want to put percentage here
'$systype',
now() )");
}
[/snip]
So although this is a functioning table, I would have to do
the math somewhere else. Can't I just substitute my
p_chng to a function or extend the mathematics part to,
say, 'p_chng int(diff-count)' so that everytime the table
gets populated, that field will always do the math for me?
Hopefully I'm not throwing you guys off (and giving a clear
explaination of what I'm doing). Should you need more to
go on, let me know.
Thanks!
-X