Re: [GENERAL] Nice to have features: Percentage function - Mailing list pgsql-general

From Melvin Davidson
Subject Re: [GENERAL] Nice to have features: Percentage function
Date
Msg-id CANu8Fizk1sAvrtfBKcYE7moiarUjdHBBQhB0kAr84HawTCZkmw@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Nice to have features: Percentage function  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: [GENERAL] Nice to have features: Percentage function  (Michael Nolan <htfoot@gmail.com>)
Re: [GENERAL] Nice to have features: Percentage function  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general


On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver <adrian.klaver@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.klaver@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.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] Nice to have features: Percentage function
Next
From: Michael Nolan
Date:
Subject: Re: [GENERAL] Nice to have features: Percentage function