New Window Function: ROW_NUMBER_DESC() OVER() ?

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.

Responses

Browse pgsql-hackers by date

  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