Re: Nice to have features: Percentage function

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