On 04/16/2017 09:37 AM, Melvin Davidson wrote:
>
>
> On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver
>
>
> *Or, you could just as easily compute inline in SQL:
>
> SELECT datname,
> pg_size_pretty(pg_database_size(datname))as size_pretty,
> pg_database_size(datname) as size,
> (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
> FROM pg_database) AS total,
> ((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname))
> FROM pg_database) ) *
> 100)::numeric(6,3) AS pct
> FROM pg_database
> ORDER BY datname;*
Yeah, that is doable but I believe the OP is looking for generic
functions that eliminate the need to write out the math for each query.
A quick and dirty example:
test=# create table percent_test(id int, subtotal numeric, sales_tax
numeric);
CREATE TABLE
test=# insert into percent_test values (1, 128, 8.7), (2, 90, 8.5), (3,
256.35, 8.7), (4, 25.50, 8.5);
INSERT 0 4
test=# select id, subtotal, sales_tax as sales_tax_rate,
percent_of(sales_tax, subtotal)::numeric(7, 2) as tax from percent_test;
id | subtotal | sales_tax_rate | tax
----+----------+----------------+-------
1 | 128 | 8.7 | 11.14
2 | 90 | 8.5 | 7.65
3 | 256.35 | 8.7 | 22.30
4 | 25.50 | 8.5 | 2.17
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
--
Adrian Klaver
adrian.klaver@aklaver.com