Skip site navigation (1) Skip section navigation (2)

Re: Costruttore degli array con record "completi"

From: rotellaro(at)gmail(dot)com
To: pgsql-it-generale(at)postgresql(dot)org
Subject: Re: Costruttore degli array con record "completi"
Date: 2008-07-10 11:56:28
Message-ID: a3e8e2210807100456s5d253815yb7037f5754c0bc36@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-it-generale
Questo giocattolino l'ho sviluppato in prima battuta per un
performance tuning e successivamente per un cms che mi sono deciso a
scrivere per abbandonare drupal che e' poco performante sotto
postgresql.

Ad ogni modo ecco la situazione.

Abbiamo tre tabelle, una relativa alle categorie, una relativa agli
articoli e una di relazione articoli<->categorie.

Il mio problema principale era posizionare gli articoli in maniera
libera nelle categorie e poterli ordinare in maniera indipendente per
categorie.

La soluzione l'ho trovata andando in una direzione totalmente nuova
rispetto alla teoria relazionale.

La tabella di relazione e' infatti cosi' costituita:

CREATE TABLE art_article_category (
				i_id_cat    integer                   NOT NULL,
				a_id_art    integer[]                 NOT NULL
		);

Praticamente vado a creare una riga per ogni categoria mentre il
posizionamento e l'ordinamento viene gestito dal campo array a_id_art.
In questo modo ho preso i proverbiali due piccioni con una fava.

L'inserimento dei dati avvengono attraverso una funzione pl/pgsql che
fa quello che ho ribattezzato "array juggling" ovvero tiene conto
della struttura dell'array facendo attenzione che non si vadano a
sforare le dimensioni.
L'ordinamento avviene in maniera del tutto simile mentre l'estrazione
ordinata, data la categoria avviene in questo modo.

Come primo passo ho creato un tipo personalizzato necessario a
PostgreSQL per determinare cosa viene restituito dalla funzione.

Banalmente:

CREATE TYPE public.glo_list_articles AS
	(
	i_id_art	integer, --id articolo
    	v_art_titl	character varying, --titolo dell'articolo
	t_art_abst	text, --abstract articolo
	v_img_sml	character varying --immagine dell'abstract
	);


Fatto questo la funzione che restituisce gli articoli, data la
categoria di appartenzenza e' la seguente:

Come potrai notare questa funzione accetta un parametro integer (l'id
di categoria)  e ritorna un setof del tipo dato precedentemente
definito.


CREATE OR REPLACE FUNCTION glo_art_list(integer) RETURNS SETOF
glo_list_articles AS
	$BODY$
		DECLARE	
				al_i_id_cat ALIAS FOR $1;
				var_id_art	integer[];
				r_result    	record;
				i_max_art_pos integer;
		BEGIN
			SELECT a_id_art INTO var_id_art
			FROM   art_article_category
			WHERE  i_id_cat=al_i_id_cat;	
			i_max_art_pos:=array_upper(var_id_art,1);
			FOR cnt_art IN 1..i_max_art_pos LOOP
				SELECT 	i_id_art,
	    				v_art_titl,
						t_art_abst,
						v_img_sml
				INTO 	r_result
				FROM	art_article
				WHERE	i_id_art=var_id_art[cnt_art] AND
						b_art_act=TRUE AND
						b_art_arc=FALSE;
				IF r_result IS NOT NULL THEN
					RETURN NEXT r_result;
				END IF;
			END LOOP;

			RETURN;

		END;
		
	$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Il cuore di tutta l'operazione sta nella for che itera sull'array
ricavato dalla select sulla tabella art_article_category e costruisce
ad hoc un record che viene memorizzato in r_result.
Per evitare di avere dei buchi nella visualizzazione verifica che
r_result non sia NULL. Se il valore e' valido allora lo restituisce
con il comando RETURN NEXT che memorizza nella memoria utente il
valore recuperato e passa al successivo.
In questo modo una funzione del genere puo' operare in maniera anche
molto complessa qualsiasi tipo di elaborazione interna al database che
viene gestita in memoria e solo al termine, quando viene invocata
l'istruzione RETURN l'intero set viene mandato al backend con tutti i
pro della situazione.

Detto questo vanno tenute alcune cose in considerazione.

1) se sei sulla 8.2 e precedenti attenzione se vai a manipolare
oggetti di sistema. Il pl/pgsql in queste versioni non ha
l'invalidation plan e cio' puo' produrre degli errori fatali
nell'elaborazione.
2) attenzione alla memoria utente. Se i dati trattati sono tanti ci
sta che si esaurisca producendo swap su disco e degradando le
prestazioni. In tal caso va aumentato il parametro work_mem.

Per tirare fuori i dati da una funzione del genere la select e'
leggermente diversa da quella di chiamata delle funzioni normali.

SELECT * FROM glo_art_list(10);

Da quello che leggo sulla documentazione ufficiale la funzione "di
fatto" si comporta come se fosse una tabella.
Maggiori info le trovi qui.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Spero di esserti stato utile.
Ciao
Federico

2008/7/9 Giorgio Valoti <giorgio_v(at)mac(dot)com>:
> Ciao a tutti, avrei una curiosità da soddisfare. So che è possibile scrivere
> una query del tipo:
>
> select a, b, c, ..., array (select x from table_x...) as t_x from table_a
>
> ma non riesco a creare una query di questo tipo:
>
> select a, b, c, ..., array (select * from table_x...) as t_x from table_a
>
> L'errore che dà è: "ERROR:  subquery must return only one column". La cosa è
> documentata, in effetti
> (http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html), ma
> volevo sapere se c'è un qualche modo di aggirare questa limitazione. L'idea,
> come forse avrete già capito, è quella di nidificare dei valori in modo da
> poter creare query che restituiscano un singolo record al punto della più
> classica join, facilitando il campo all'applicazione.
> Qualcuno ha qualche suggerimento?
>
> Grazie in anticipo
> --
> Giorgio Valoti
> --
> Sent via pgsql-it-generale mailing list (pgsql-it-generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-it-generale
>



-- 
(all opinions expressed are my own)
Federico Campoli
PostgreSQL Consulting -> PGHost http://www.pghost.eu

In response to

Responses

pgsql-it-generale by date

Next:From: Giorgio ValotiDate: 2008-07-10 14:02:50
Subject: Re: Costruttore degli array con record "completi"
Previous:From: rotellaroDate: 2008-07-09 20:06:39
Subject: Re: Costruttore degli array con record "completi"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group