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 <melvin6...@gmail.com> wrote: > > > On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver <adrian.kla...@aklaver.com > > 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.kla...@aklaver.com >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> 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. >
