Re: Nice to have features: Percentage function

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(at)gmail(dot)com>
wrote:

>
>
> 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.
>