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.
