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.

If you have already written the functions, why not just use them?

To make them available across a cluster install them in a template
database(www.postgresql.org/docs/9.6/static/manage-ag-templatedbs.html)
and create your new databases using that. The default template is the
template1 database, but you can create your own.

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.

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

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

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.

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@gmail.com>
wrote:

On 04/16/2017 09:37 AM, Melvin Davidson wrote:

On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver

*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;*

Yeah, that is doable but I believe the OP is looking for generic
functions that eliminate the need to write out the math for each query.
A quick and dirty example:

test=# create table percent_test(id int, subtotal numeric, sales_tax
numeric);
CREATE TABLE
test=# insert into percent_test values (1, 128, 8.7), (2, 90, 8.5), (3,
256.35, 8.7), (4, 25.50, 8.5);
INSERT 0 4

test=# select id, subtotal, sales_tax as sales_tax_rate,
percent_of(sales_tax, subtotal)::numeric(7, 2) as tax from percent_test;
id | subtotal | sales_tax_rate | tax
----+----------+----------------+-------
1 | 128 | 8.7 | 11.14
2 | 90 | 8.5 | 7.65
3 | 256.35 | 8.7 | 22.30
4 | 25.50 | 8.5 | 2.17

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
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

On 04/16/2017 02:33 PM, Michael Nolan wrote:

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.

Are they available somewhere?

My previous examples where more proof of concept then complete.

--
Mike Nolan

--
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

<div><div dir="rtl"><div>Hi,<br><br></div>
<div>I know I can solve my issue localy but I think that percentage manipulation is commonly used by many users and while it's true that each one can create his own solution localy it would be nice if postgresql would have build in functions for that.</div>
<div>&nbsp;</div>
<div>percentagee manipulation is a core fuctionality.</div>
<div>It would be nice to see it listed here:</div>
<div>https://www.postgresql.org/docs/9.5/static/functions-math.html&lt;/div&gt;&lt;/div&gt;&lt;section class="cust_msg_end"></section><blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0;"><br>ב אפר׳ 17, 2017 0:47, Adrian Klaver כתב:<blockquote style="margin:0;margin-bottom:20px;border-top:1px solid #e0e0e0">On 04/16/2017 02:33 PM, Michael Nolan wrote:<br>&gt; I also have some pre-defined percentage functions, they check the<br>&gt; denominator and return null if it is zero, to avoid 'divide by zero'<br>&gt; errors.<br><br>Are they available somewhere?<br><br>My previous examples where more proof of concept then complete.<br><br><br>&gt; --<br>&gt; Mike Nolan<br>&gt;<br><br><br>-- <br>Adrian Klaver<br>adrian.klaver@aklaver.com<br><br><br>-- <br>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)<br>To make changes to your subscription:<br><a href="http://www.postgresql.org/mailpref/pgsql-general&quot;&gt;http://www.postgresql.org/mailpref/pgsql-general&lt;/a&gt;&lt;br&gt;&lt;br&gt;&lt;br&gt;&lt;/blockquote&gt;&lt;/blockquote&gt;&lt;br&gt;&lt;/div&gt;