I'm trying to take all the dBm values for a date and average them, but to average dBm they need to be converted to watts, averaged, then converted back to dBm.
Here is what I tried with plperl.
CREATE OR REPLACE FUNCTION avg_dbm(double precision[]) RETURNS integer AS $$
my $watts = 0;
my $watt_count = 0;
foreach my $dbm ( @_ ) {
$watts = $watts + ( 10 ** ( $dbm / 10 ) ) / 1000;
$watt_count++;
}
my $avg_watts = $watts / $watt_count;
my $avg_dbm = 10 * log( 1000 * $avg_watts ) / log(10);
return $avg_dbm;
$$ LANGUAGE plperl;
DROP AGGREGATE IF EXISTS db_agg( double precision );
CREATE AGGREGATE db_agg ( double precision ) (
sfunc = array_append,
stype = double precision[],
finalfunc = avg_dbm
);
SELECT avg_db( dbm ) FROM data GROUP BY meas_date;
The error that I get when running this function is:
ERROR: column "data.dbm" must appear in the GROUP BY clause or be used in an aggregate function
I think I'm close to having this right but can't seem to figure it out. The query only needs to aggregate by date and average all the dbm values for that date into one value.
Any ideas or solutions?
EDIT: updated plsql code for proper perl variable name
UPDATE:
I changed the SQL to:
SELECT db_agg( dbm ) FROM data GROUP BY meas_date;
And get this error:
ERROR: Operation "/": no method found,
left argument in overloaded package PostgreSQL::InServer::ARRAY,
right argument has no overloaded magic at line 7.
CONTEXT: PL/Perl function "avg_dbm"
The part that is having problems is in this line of code in plperl
$watts = $watts + ( 10 ** ( $dbm / 10 ) ) / 1000;
Specifically $dbm doesn't want to be divided by 10...
I tried changing the functions to use non-array double precision data types but that didn't seem to work. I also tried adding 0 like in MySQL in hopes to do auto conversion but failed.
Is there some treatment to array data types needed before using them in division?
