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
