From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ron Ben <ronb910(at)walla(dot)co(dot)il>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Nice to have features: Percentage function |
Date: | 2017-04-16 16:23:15 |
Message-ID: | 518fa1c0-a782-9352-a6ff-b408ca1f6ff6@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2017-04-16 16:37:03 | Re: Nice to have features: Percentage function |
Previous Message | Adrian Klaver | 2017-04-16 15:14:46 | Re: Nice to have features: Percentage function |