Query con ordinamento naturale

Vediamo in questa breve tip come realizzare un ordinamento naturale in postgresql 9.x
 
Supponiamo di avere una tabella testtable fatta nel modo seguente :
 
CREATE TABLE testtable
(
  id serial NOT NULL,
  valore text,
  CONSTRAINT testtable_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE testtable
  OWNER TO postgres;
 
con i seguenti valori:
 
mio=# select * from testtable;
 id |  valore  
----+----------
  1 | pippo
  2 | pippo1
  3 | pippo12
  4 | pippo2
  5 | pippo3
  6 | pippo14
  7 | pippo121
  8 | pippo21
  9 | pippo v
 10 | pippov
(10 rows)
 
Supponiamo quindi di avere una prima parte della stringa alfanumerica e una seconda parte numerica, quello che vogliamo ottenere è un ordinamento naturale ossia qualcosa del tipo:
 
  id |  valore  
----+----------
  1 | pippo
  2 | pippo1
  4 | pippo2
  5 | pippo3
  3 | pippo12
  6 | pippo14
  8 | pippo21
  7 | pippo121
  9 | pippo v
 10 | pippov
(10 rows)
 
Ossia i dati ordinati secondo la parte numerica con ordinamento numerico
e non posizionale come avviene per gli ordinamenti testuali.
 
Cioè se semplicemente scrivessi:
 
select * from testtable order by valore;
 
Otterrei qualcosa del tipo:
 id |  valore  
----+----------
  1 | pippo
  9 | pippo v
  2 | pippo1
  3 | pippo12
  7 | pippo121
  6 | pippo14
  4 | pippo2
  8 | pippo21
  5 | pippo3
 10 | pippov
(10 rows)
 
in quanto l'ordinamento dei numeri memorizzati in un campo testuale
avviene in maniera posizionale cioè se avessi memorizzato sul
campo valore i valori 1 , 2 , 10 se ordinassi sul campo valore
otterrei 1,10,2 in quanto il campo valore è di tipo testuale.
 
Vediamo allora come procedere. Per prima cosa ho creato due funzioni utlizzando il linguaggio plpgsql la prima che data una stringa ritorni solo
il valore alfanumerico:
 
CREATE OR REPLACE FUNCTION btrsort_txt(text)
  RETURNS text AS
$BODY$
begin
 return SUBSTRING($1 FROM '[^0-9]+');
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION btrsort_txt(text)
  OWNER TO postgres;
  
Se eseguiamo la funzione appena scritta:
 
mio=# select btrsort_txt('pippo10');
 btrsort_txt 
-------------
 pippo
(1 row)
 
otteniamo in uscita la sola parte alfanumerica.
 
Come seconda cosa scriviamo una funzione simile che però estragga solo la parte numerica della stringa e la restituisca in formato numerico:
 
CREATE OR REPLACE FUNCTION btrsort_num(text)
  RETURNS integer AS
$BODY$
begin
 return coalesce(SUBSTRING($1 FROM '[0-9]+'),'0')::int4;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION btrsort_num(text)
  OWNER TO postgres;
  
Se eseguiamo ora questa seconda funziona con gli stessi parametri di
ingresso della prima otteniamo:
 
mio=# select btrsort_num('pippo10');
 btrsort_num 
-------------
          10
(1 row)
 
Il coalesce serve per far si che se non c'è valore nella parte numerica viene restituito uno zero:
 
mio=# select btrsort_num('pippo');
 btrsort_num 
-------------
           0
(1 row)
 
questo perché così in fase di ordinamento quella riga finisce sopra a tutte le altre righe con 'pippo' seguite da numeri. Ora,come si può vedere abbiamo creato le due funzioni di tipo IMMUTABLE in modo tale che sia possibile
creare degli indici basati su queste funzioni. Creiamo quindi tali indici:
 
CREATE INDEX btrsort_idx
  ON testtable
  USING btree
  (btrsort_txt(valore) );
  
CREATE INDEX btrsort_idx2
  ON testtable
  USING btree
  (btrsort_num(valore) );  
 
A questo punto è abbastanza semplice ottenere il risultato che vogliamo
basta scrivere una query di select ordinando prima per btrsort_txt(valore)
e poi per btrsort_num(valore). eseguiamo quindi:
 
mio=# select * from testtable order by btrsort_txt(valore),btrsort_num(valore);
 id |  valore  
----+----------
  1 | pippo
  2 | pippo1
  4 | pippo2
  5 | pippo3
  3 | pippo12
  6 | pippo14
  8 | pippo21
  7 | pippo121
  9 | pippo v
 10 | pippov
(10 rows)
 
 
E il gioco è fatto ;)
 
Enjoy !!!
 
Un saluto a tutti Enrico

Back to top

Privacy Policy | Project hosted by linuxtime.it | pgTheme is a Drupal 6.x Theme creato da Massimiliano Marini per PSQL.it
Original Theme Designed by tinysofa for The Official Site of PostgreSQL