I also have some pre-defined percentage functions, they check the denominator and return null if it is zero, to avoid 'divide by zero' errors.
--
Mike Nolan--
On Sun, Apr 16, 2017 at 11:37 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:
On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:On 04/15/2017 10:47 PM, Ron Ben wrote:
Hi,
I'm always finiding myself writing many varations of functions to
calculate percentage.
I think it would be nice if postgresql would have build in functions for
that.
I think the major functionality is something like the 3 ooptions here:
https://percentagecalculator.net/
It may help to keep code simple and clean and it seem like something
simple to implement.
Plan B, CREATE your own extension. What follows is my first attempt at creating an extension and the functions included are simple placeholders more then anything else:
File name: calc_percents--1.0.sql
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION calc_percents" to load this file. \quit
CREATE OR REPLACE FUNCTION public.percent_of(val1 numeric, val2 numeric)
RETURNS numeric
LANGUAGE sql
AS $function$
select (val1 / 100) * val2;
$function$
;
CREATE OR REPLACE FUNCTION public.what_percent(val1 numeric, val2 numeric)
RETURNS numeric
LANGUAGE sql
AS $function$
SELECT (val1 / val2) * 100;
$function$
;
CREATE OR REPLACE FUNCTION public.percent_diff(val1 numeric, val2 numeric)
RETURNS numeric
LANGUAGE sql
AS $function$
select (val2 - val1) / val1 * 100;
$function$
;
File name: calc_percents.control
# calc_percents extension
comment = 'Functions for calculating percentages'
default_version = '1.0'
relocatable = true
Install the above in $SHARE/extension, in my case /usr/local/pgsql/share/extension/
Then:
test=# create extension calc_percents;
CREATE EXTENSION
test=# \df percent_of
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------------+------------------+------------------- ---------+--------
public | percent_of | numeric | val1 numeric, val2 numeric | normal
test=# select * from round(percent_of(10, 100), 2) ;
round
-------
10.00
test=# \df percent_diff
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------+------------------+----------------- -----------+--------
public | percent_diff | numeric | val1 numeric, val2 numeric | normal
test=# select * from round(percent_diff(100, 109), 2) ;
round
-------
9.00
(1 row)
test=# \df what_percent
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------+------------------+----------------- -----------+--------
public | what_percent | numeric | val1 numeric, val2 numeric | normal
(1 row)
test=# select * from round(what_percent(10, 109), 2) ;
round
-------
9.17
If you think it's a good idea it would be nice if someone can implement
this.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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;
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
