From: | Maiquel Grassi <grassi(at)hotmail(dot)com(dot)br> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | New Window Function: ROW_NUMBER_DESC() OVER() ? |
Date: | 2024-01-16 12:54:16 |
Message-ID: | CP8P284MB2496FA888E97623EE6FB3F89EC732@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
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);
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.
-------------------------------------------------------------------------------------
/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.
-------------------------------------------------------------------------------------
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):
[cid:7f9bab27-09e3-4a6c-b47c-6cd7983d79a4]
Tks,
Maiquel Orestes Grassi.
From | Date | Subject | |
---|---|---|---|
Next Message | Jelte Fennema-Nio | 2024-01-16 12:57:44 | Re: [PATCH] Add additional extended protocol commands to psql: \parse and \bindx |
Previous Message | Jelte Fennema-Nio | 2024-01-16 12:51:59 | Re: [PATCH] Add additional extended protocol commands to psql: \parse and \bindx |