PostgreSQL sull'uso della funzione row_number()

Qualche giorno fa era nata la necessità di dovere modificare una funzione in PostgreSQL rendendo parametrizzatile il tipo di ordinamento (ASC o DESC) finale del set di dati restituito in output.

La soluzione più semplice, ma probabilmente, meno efficiente era quello di rendere dinamico tutto lo script ed eseguirlo con il comando EXECUTE, parametrizzando l'ordinamento ed eseguendo il tutto all'interno della funzione stessa.


Per esempio: 
 

CREATE OR REPLACE FUNCTION funct_1(IN p_1, IN p_order_type text)
  RETURNS TABLE( [. . .] ) AS
$BODY$

RETURN QUERY EXECUTE '

SELECT [ . . .]

FROM my_table

WHERE [. . . ]

ORDER BY col_1 ' ||  p_order_type || '
LIMIT 1
';

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1;

Ma questa è una delle condizioni dove le funzioni "window":
http://www.postgresql.org/docs/9.1/static/functions-window.html
possono venirci in aiuto.
In particolare quella che fa al caso nostro è: row_number() che si fa carico di numerare (partendo da 1) ogni riga del nostro set di dati, in base ad un ordinamento pre-impostato.

CREATE OR REPLACE FUNCTION funct_1(IN p_1, IN p_order_asc bool)
  RETURNS TABLE( [. . .] ) AS
$BODY$

SELECT [ . . .]

FROM my_table

WHERE [. . . ]

ORDER BY 
CASE 
 WHEN p_order_asc THEN 
  ( row_number() over (ORDER BY col_1 asc ) )   --L'ordinamento ASC viene garantito 
           --dalla funzione row_number() 
           --applicato alla colonna " 'col_1' ASC "
 ELSE 
  ( row_number() over (ORDER BY col_1 desc ) )   --L'ordinamento DESC stessa impostazione di quello ASC.
END
LIMIT 1
;

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1;

Faccio notare l'ordinamento:

ORDER BY 
CASE 
 WHEN p_order_asc THEN 
  ( row_number() over (ORDER BY col_1 asc ) 
 ELSE 
  ( row_number() over (ORDER BY col_1 desc ) )
END

Il piano di esecuzione, senza l'utilizzo e con l'utilizzo della funzione 'row_number()'.
La differenza è minima, e lavorando su una migliore indicizzazione dei campi interessati, probabilmente si può ulteriormente migliorare.
Va considerato che il piano di esecuzione senza l'utilizzo della funzione 'row_number()' (il primo) è al netto del costo della funzione 'EXECUTE'. 

Limit  (cost=445.73..445.73 rows=1 width=42)
  ->  Sort  (cost=445.73..446.16 rows=172 width=42)
        Sort Key: "timestamp", state_id
        ->  Index Scan using [...]  (cost=0.57..444.87 rows=172 width=42)
              Index Cond: (ad_id = 123)

Limit  (cost=455.55..455.56 rows=1 width=42)
  ->  Sort  (cost=455.55..455.98 rows=172 width=42)
        Sort Key: (row_number() OVER (?))
        ->  WindowAgg  (cost=451.25..454.69 rows=172 width=42)
              ->  Sort  (cost=451.25..451.68 rows=172 width=42)
                    Sort Key: "timestamp", state_id
                    ->  Index Scan using [...]  (cost=0.57..444.87 rows=172 width=42)
                          Index Cond: (ad_id = 123)