Re: [GENERAL] Nice to have features: Percentage function

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