New Window Function: ROW_NUMBER_DESC() OVER() ? - Mailing list pgsql-hackers
Hi developers,
I was working on loans and bank financing, specifically focusing on Amortization Systems. I had the need to reverse the counter for the total number of installments or for a specific set of installments. This "reversal" is essentially a reverse "row_number" function. I realized that it is to "hard work" to write PL/foo functions for this or even to implement it in just SQL using little code.
To streamline the daily process, I conducted a laboratory (prototype, test) using the PostgreSQL 14.3 version doing a small customization. I implemented the window function "row_number_desc," as detailed below.
I would like to assess the feasibility of incorporating this into a future version of Postgres, given its significant utility and practicality in handling bank contract installments in many fields of Finacial Math, because to do use "row_number_desc() over()" is most easy that write a PL/foo or a big lenght SQL string that to do the "descendent case".
What is your opinion regarding this suggestion?
Is it possible to make this a 'feature patch' candidate to PostgreSQL 17?
I was working on loans and bank financing, specifically focusing on Amortization Systems. I had the need to reverse the counter for the total number of installments or for a specific set of installments. This "reversal" is essentially a reverse "row_number" function. I realized that it is to "hard work" to write PL/foo functions for this or even to implement it in just SQL using little code.
To streamline the daily process, I conducted a laboratory (prototype, test) using the PostgreSQL 14.3 version doing a small customization. I implemented the window function "row_number_desc," as detailed below.
I would like to assess the feasibility of incorporating this into a future version of Postgres, given its significant utility and practicality in handling bank contract installments in many fields of Finacial Math, because to do use "row_number_desc() over()" is most easy that write a PL/foo or a big lenght SQL string that to do the "descendent case".
What is your opinion regarding this suggestion?
Is it possible to make this a 'feature patch' candidate to PostgreSQL 17?
SUMMARY (IMPLEMENTATION and RESULT):
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
/home/postgresql-14.3-custom/src/backend/utils/adt/windowfuncs.c
/*
* row_number_desc
* Performs the inverse of row_number function, is a descendent result.
*/
Datum
window_row_number_desc(PG_FUNCTION_ARGS)
{
WindowObject winobj = PG_WINDOW_OBJECT();
int64 totalrows = WinGetPartitionRowCount(winobj);
int64 curpos = WinGetCurrentPosition(winobj);
int64 curpos = WinGetCurrentPosition(winobj);
WinSetMarkPosition(winobj, curpos);
PG_RETURN_INT64(totalrows - curpos);
}
-------------------------------------------------------------------------------------
/home/postgresql-14.3-custom/src/include/catalog/pg_proc.dat
{ oid => '13882', descr => 'row number descendent within partition',
proname => 'row_number_desc', prokind => 'w', proisstrict => 'f',
prorettype => 'int8', proargtypes => '', prosrc => 'window_row_number_desc' },
Note: In this step, I know that I'll need to use an unused OID returned by the 'src/include/catalog/unused_oids' script.
Note: In this step, I know that I'll need to use an unused OID returned by the 'src/include/catalog/unused_oids' script.
-------------------------------------------------------------------------------------
/home/postgresql-14.3-custom/src/backend/catalog/postgres.bki
insert ( 13882 row_number_desc 11 10 12 1 0 0 0 w f f f f i s 0 0 20 '' _null_ _null_ _null_ _null_ _null_ window_row_number_desc _null_ _null_ _null_ _null_ )
Note: In this step, I know that I'll need to use an unused OID returned by the 'src/include/catalog/unused_oids' script.
Note: In this step, I know that I'll need to use an unused OID returned by the 'src/include/catalog/unused_oids' script.
-------------------------------------------------------------------------------------
perl -I /home/postgresql-14.3-custom/src/backend/catalog Gen_fmgrtab.pl --include-path / /home/postgresql-14.3-custom/src/include/catalog/pg_proc.dat --output /home
Applying the "row_number() over() DESC" function (basic example):
Tks,
Maiquel Orestes Grassi.
Attachment
pgsql-hackers by date: