percent function in table? - Mailing list pgsql-general

From Johnson, Shaunn
Subject percent function in table?
Date
Msg-id 73309C2FDD95D11192E60008C7B1D5BB0452E34C@snt452.corp.bcbsm.com
Whole thread Raw
Responses Re: percent function in table?
List pgsql-general

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

pgsql-general by date:

Previous
From: Giovanni Montes Bonilla
Date:
Subject: "set" type
Next
From: Scott Marlowe
Date:
Subject: Re: percent function in table?