Re: percent function in table? - Mailing list pgsql-general

There are a few ways to do this.  The easiest is with a view, which would
be something like this:

create table test (n1 float,n2 float);
insert into test values (5,8);
insert into test values (8,12);
insert into test values (23.68,51.3);

create view tview as select n1,n2,n2||' is '||((n2-n1)/n1)*100||'% greater
than '||n1 as percent from test;

Or simply

create view tview as select n1,n2,((n2-n1)/n1)*100 as percent from test;

if you don't want the extra noise of the text.  Output
On Mon, 29 Apr 2002, Johnson, Shaunn wrote:

Or, make a function to do it and call it like

select n1, n2, pmore(n1,n2) from test;

You could also write a trigger to detect whenever a change was made to an
input field and recalc the dependent field, but that might stall bad under
heavy update load.

OTOH, if you mostly read, with little updates, than the trigger will be
better than doing the math on the fly.

Hope that helps.

> 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
>