New Window Function: ROW_NUMBER_DESC() OVER() ? - Mailing list pgsql-hackers

From Maiquel Grassi
Subject New Window Function: ROW_NUMBER_DESC() OVER() ?
Date
Msg-id CP8P284MB2496FA888E97623EE6FB3F89EC732@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM
Whole thread Raw
Responses Re: New Window Function: ROW_NUMBER_DESC() OVER() ?
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?

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


Tks,
Maiquel Orestes Grassi.
Attachment

pgsql-hackers by date:

Previous
From: Jelte Fennema-Nio
Date:
Subject: Re: [PATCH] Add additional extended protocol commands to psql: \parse and \bindx
Next
From: Jelte Fennema-Nio
Date:
Subject: Re: [PATCH] Add additional extended protocol commands to psql: \parse and \bindx