Search Postgresql Archives

Re: Nice to have features: Percentage function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:


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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux