Re: Costruttore degli array con record "completi"

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