HPPM: High Precision PostgreSQL Mathematics.  
Tags: help, postgres, c
Published: 2021-10-06 04:41:23

I have been trying to find active interest in an available for public use PostgreSQL extension, complete and available, on the public internet, that will support the following:


   High Precision Numeric and Elementary Functions Support      
               In PostgreSQL v13 or beyond.                  
      HPPM: High Precision PostgreSQL Mathematics.            


-Integer Z, or Rational Mixed Decimal Q, numbers support in 64 bit PostgreSQL.  Via HPZ, and HPQ, original types. In this specification, they are collectively referred to as HPX types.  

THERE SHOULD BE NO RANGE OR MULTIRANGE TYPES, OR THEIR SUPPORTING FUNCTIONS OR OPERATORS, THAT RELY ON HPX TYPES, CERTAINLY AT THIS POINT.

-The extension could be based on a library like GMP, written in C, being an appropriate basis to use, for all OS platforms involved. The point being, that there is already some support for this kind of mathematics, in terms of its logic, and further optimisation, publicly available in C that can be apprehended for this extension and its platforms.

-Real numbers are the values of Integer, non-recurring Rational Numbers and recurring, Irrational Numbers.  
Recurring numbers can be appropriately truncated, via a finite Natural precision value, always at least 1, to obtain an approximating value.  The approximating value can really be seen as a finite Rational value, possibly with integer or decimal parts, or both together. These numbers may be positive or negative, or zero, scalar values, and always do exist on the one dimensional number line.

-A defaulting number of significant figures (precision), will be stored inside each HPX datum or type variable or column type. This gets specified at a type declaration, or around an expression or value datum, before further use. Or the defaulting precision amount is taken. Precision can be accessed and changed, later, via a secondary, different variable declaration, or a precision function call.

Precision is set at type declaration, or from the default. Precision is always apprehended before external or internal evaluation begins.  Precision is used to control numbers, and operations involving them, and the value output, when numeric manipulations are done.

If an HPX value is data on its own, without a variable or a coded expression, it gains the total default precision amount. If it is inserted into a table column with a different precision, then that precision is applied.  When an HPX calculating expression is assigned into an HPX variable, it will have checked for (any) assignment, =, into a variable with its own precision, before proceeding through the PostgreSQL code, value(s) and expression to the right of that assignment and correctly copying all of that inside the main assigned variable. If however, an HPX value, in a PostgreSQL code expression is sent straight into a RETURN statement or a SELECT statement, without assignment or a precision function call, or is just specified in a comparison, then that datum will contain the highest precision value out of any of the others in its PostgreSQL expression, by checking the largest one found as it is evaluation considered, from left to right. But before anything is set or specified, a total, separate default precision value of 20 is the beginning point for all HPX data. In such absence, or when insertion into a preexisting table column is occurring, an HPX datum can also have its precision accessed via these functions:

precision(HPZ input, BIGINT input) returns HPZ;
precision(HPQ input, BIGINT input) returns HPQ;
precision(HPZ input) returns BIGINT;
precision(HPQ input) returns BIGINT;

expression(HPZ input) returns TEXT;
expression(HPQ input) returns TEXT;

-HPX values, as PostgreSQL data, can be displayed directly.  They sit on top of a few other phenomenon however.  Forward and inverse accuracy, withstanding truncation, can be achieved by distinctly storing, operating with and normalising the mathematical expression (or just one value, from assignment).  The expression has one or more links, from value(s) to variable(s), all within the expression, via applying of precision consideration or adjustment at evaluation time, internally. This system will uphold any precision, certainly ones within a very large range limit, controlled by the already available type, the BIGINT. It can enumerate digits of a frequency within the range of 
-9223372036854775808 to +9223372036854775807. This is at least between negative and positive nine quintilion digit places.  More than enough for the speeds of now, or maybe, tomorrow.

Though naturally evaluation will slow down, or not conclude in useful time frames, before those limits, in the here and now.  That phenomenon can be allowed, and left to the context of the programmer to deal with or avoid as they may.  They may try to minimise the extent of one internal expression by using re-substitution in the body of originating, PostgreSQL, code. If the internal expression, but mainly the multiple values set inside one HPX datum gets too large or too elaborate, there is the option to convert the output value to TEXT by casting, and to start over in terms of only one net value, by casting that value back into an HPX type again, and reassigning all other internal structuring.  A TEXT variable or table column, or even another numeric type if the value is in range and can operate accurately there, can be used to store digit data alone.  Secondary assignments reset the expression and values buffer within an HPX variable or table column.

--At the point of PostgreSQL code input and execution:

select pi(1001) as pi;

--Within a table creation command:

create table example_table
(
id BIGSERIAL PRIMARY KEY,
a HPZ,
b HPQ(50)
);

ALTER TABLE example_table ALTER COLUMN b TYPE HPQ(60);
ALTER TABLE example_table ALTER COLUMN b TYPE HPQ(50);

INSERT INTO example_table(a,b) VALUES(0,  0.1);
INSERT INTO example_table(a,b) VALUES(100,1.1);
INSERT INTO example_table(a,b) VALUES(200,2.2);
INSERT INTO example_table(a,b) VALUES(300,3.3);
INSERT INTO example_table(a,b) VALUES(400,4.4);
INSERT INTO example_table(a,b) VALUES(500,5.5);
INSERT INTO example_table(a,b) VALUES(600,6.6);
INSERT INTO example_table(a,b) VALUES(700,7.7);
INSERT INTO example_table(a,b) VALUES(800,8.8);
INSERT INTO example_table(a,b) VALUES(900,9.9);

--Or as variables, in some function:

create or replace function example_function()
returns void
language plpgsql
as
$$

declare
a HPQ(2); 
b HPQ(2);
c HPQ(3);
begin

a = 0.1;
b = 0.1;
c=a*b;
precision(c,10);
return void
end;
$$

-Value assignment to a typed variable by =.

-Operators.  Base 10 Arithmetic and comparisons support on Base 10 HPZ and HPQ, with casting:

::,=,!=,<>,>,<,>=,<=,+,-,*,/,%,^

These include full division and integer only division (from type inference), with no remainder, and a remainder calculating operator, within all range possibilities of the involved two values. There should be automatic casting involved, upon other numeric type values, up to HPZ or HPQ, where required and appropriate, in PostgreSQL expressions.

-Reified support with broader syntax and operations within PostgreSQL.  Tables and related phenomena, Array types, Indexing, Variables and related phenomena, the Record type, 
direct compatibility with Aggregate and Window functions, and Partitions are all parts of a larger subset that should re-interact with HPZ or HPQ successfully.  WITH THE PRESENT EXCEPTION OF EVERYTHING RELATED TO RANGE AND MULTIRANGE TYPES.

-Ease of installation support. Particularly for Windows and Linux. *.exe, *.msi or *.rpm, *.deb, *.bin installers.
Upon a PostgreSQL installation.  Installation and Activation instructions included, if necessary for successful use for the uninitiated. The extension should literally just install and be applicable, with no loading command necessary, from inside PostgreSQL. For every time the database process is run, by default. 

-Mathematical and Operational functions support:

cast(HPZ as HPQ)  returns HPQ;
cast(HPQ as HPZ)  returns HPZ;
cast(TEXT as HPZ) returns HPZ;
cast(TEXT as HPQ) returns HPQ;
cast(HPQ as TEXT) returns TEXT;
cast(HPZ as TEXT) returns TEXT;

cast(HPZ as SMALLINT) returns SMALLINT;
cast(SMALLINT as HPZ) returns HPZ;
cast(HPZ as INTEGER)  returns INTEGER;
cast(INTEGER as HPZ)  returns HPZ;
cast(HPZ as BIGINT)   returns BIGINT;
cast(BIGINT as HPZ)   returns HPZ;
cast(HPQ as REAL)     returns REAL;
cast(REAL as HPQ)     returns HPQ;
cast(DOUBLE PRECISION as HPQ) returns HPQ;
cast(HPQ as DOUBLE PRECISION) returns DOUBLE PRECISION;
cast(HPQ as DECIMAL)  returns DECIMAL;
cast(DECIMAL as HPQ)  returns HPQ;
cast(HPQ as NUMERIC)  returns NUMERIC;
cast(NUMERIC as HPQ)  returns HPQ;

sign(HPQ input)  returns HPZ;
abs(HPQ input)   returns HPZ;
ceil(HPQ input)  returns HPZ;
floor(HPQ input) returns HPZ;
round(HPQ input) returns HPZ;
recip(HPQ input) returns HPQ;
pi(BIGINT precision) returns HPQ;
e(BIGINT precision)  returns HPQ;
power(HPQ base, HPQ exponent) returns HPQ;
sqrt(HPQ input) returns HPQ;
nroot(HPZ theroot, HPQ input) returns HPQ;
log10(HPQ input) returns HPQ;
ln(HPQ input) returns HPQ;
log2(HPQ input) returns HPQ;
factorial(HPZ input) returns HPZ;
nCr(HPZ objects, HPZ selectionSize) returns HPZ;
nPr(HPZ objects, HPZ selectionSize) returns HPZ;

degrees(HPQ input) returns HPQ;
radians(HPQ input) returns HPQ;
sind(HPQ input)    returns HPQ;
cosd(HPQ input)    returns HPQ;
tand(HPQ input)    returns HPQ;
asind(HPQ input)   returns HPQ;
acosd(HPQ input)   returns HPQ;
atand(HPQ input)   returns HPQ;
sinr(HPQ input)    returns HPQ;
cosr(HPQ input)    returns HPQ;
tanr(HPQ input)    returns HPQ;
asinr(HPQ input)   returns HPQ;
acosr(HPQ input)   returns HPQ;
atanr(HPQ input)   returns HPQ;

-Informative articles on all these things exist at:

Comparison Operators: https://en.wikipedia.org/wiki/Relational_operator
Floor and Ceiling Functions: https://en.wikipedia.org/wiki/Floor_and_ceiling_functions
Arithmetic Operations: https://en.wikipedia.org/wiki/Arithmetic
Integer Division: https://en.wikipedia.org/wiki/Division_(mathematics)#Of_integers
Modulus Operation: https://en.wikipedia.org/wiki/Modulo_operation
Rounding (Commercial Rounding): https://en.wikipedia.org/wiki/Rounding
Factorial Operation: https://en.wikipedia.org/wiki/Factorial
Degrees: https://en.wikipedia.org/wiki/Degree_(angle)
Radians: https://en.wikipedia.org/wiki/Radian
Elementary Functions: https://en.wikipedia.org/wiki/Elementary_function

The following chart could be used to help test trigonometry outputs, under
Further Consideration of the Unit Circle:
https://courses.lumenlearning.com/boundless-algebra/chapter/trigonometric-functions-and-the-unit-circle/


                           The End.
#help
#postgres