Re: Costruttore degli array con record "completi"

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

Ciao Giorgio,
un problema simile l'ho avuto nell'utilizzo dei dati array mixati con
i dati normali per produrre set ordinati.

La cosa l'ho risolta con una funzione plpgsql ritornante un recordset.
Appena rientro ti posto un esempio.

Bisogna giocare un po' anche con i tipi personalizzati ma il gioco
vale la candela in quanto le prestazioni sono decisamente interessanti
in quanto questo approccio determina l'elaborazione dei recordset
nello shared buffer e limita al minimo indispensabile il transito dei
dati tra l'applicazione e il database.

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

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

On 10/lug/08, at 13:56, rotellaro(at)gmail(dot)com wrote:

> 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.

wow, bello! Dal punto di vista di filosofia di utilizzo ci siamo: è
l’approccio che vorrei seguire. Tuttavia quello che ho in mente è
probabilmente diverso, ma forse mi hai messo sulla buona strada. Il
punto cruciale è il data type: date ad es. due tabelle, A e B, puoi
scrivere una cosa come:
select A.*, array(select get_B_for_id_a(100)) from A where id_a = 100;

solo se la funzione "get_B_for_id_a" restituisce un setof B e non un
generico setof record.

> […]

Grazie
--
Giorgio Valoti

On Thu, Jul 10, 2008 at 4:02 PM, Giorgio Valoti <giorgio_v(at)mac(dot)com> wrote:
> On 10/lug/08, at 13:56, rotellaro(at)gmail(dot)com wrote:
>
>> 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.
>
> wow, bello! Dal punto di vista di filosofia di utilizzo ci siamo: è
> l'approccio che vorrei seguire. Tuttavia quello che ho in mente è
> probabilmente diverso, ma forse mi hai messo sulla buona strada. Il punto
> cruciale è il data type: date ad es. due tabelle, A e B, puoi scrivere una
> cosa come:
> select A.*, array(select get_B_for_id_a(100)) from A where id_a = 100;
>
> solo se la funzione "get_B_for_id_a" restituisce un setof B e non un
> generico setof record.

In realta' puoi fare una cosa ancora piu' raffinata.
Incapsulare anche la select nella funzione e fargli ritornare un set
formattato e costruito come ti serve.
Questo for annidato restituisce oltre ai valori tirati fuori da un
array bidimensionale anche le posizioni ordinate semplicemente
posizionando nella select into r_result i contatori dei due for a mo'
di campi di tabella.

FOR cnt_zone IN i_min_zone..i_max_zone LOOP
FOR cnt_pos IN i_min_pos..i_max_pos LOOP
IF a_struct[cnt_zone][cnt_pos]>0 THEN
SELECT
id,
area_id,
name,
cnt_zone as zone,
cnt_pos as position,
INTO result
FROM tab_position
WHERE
id=a_struct[cnt_zone][cnt_pos];
RETURN NEXT r_result;
END IF;
END LOOP;
END LOOP;

PostgreSQL è molto flessibile da questo punto di vista.

Ciao
Federico

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

On 10/lug/08, at 16:26, rotellaro(at)gmail(dot)com wrote:

> On Thu, Jul 10, 2008 at 4:02 PM, Giorgio Valoti <giorgio_v(at)mac(dot)com>
> wrote:
>> On 10/lug/08, at 13:56, rotellaro(at)gmail(dot)com wrote:
>>
>>> 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.
>>
>> wow, bello! Dal punto di vista di filosofia di utilizzo ci siamo: è
>> l'approccio che vorrei seguire. Tuttavia quello che ho in mente è
>> probabilmente diverso, ma forse mi hai messo sulla buona strada. Il
>> punto
>> cruciale è il data type: date ad es. due tabelle, A e B, puoi
>> scrivere una
>> cosa come:
>> select A.*, array(select get_B_for_id_a(100)) from A where id_a =
>> 100;
>>
>> solo se la funzione "get_B_for_id_a" restituisce un setof B e non un
>> generico setof record.
>
> In realta' puoi fare una cosa ancora piu' raffinata.
> Incapsulare anche la select nella funzione e fargli ritornare un set
> formattato e costruito come ti serve.

È proprio quello che voglio fare. Nel progetto che sto sviluppando ho
deciso di non usare altro che funzioni; niente SQL generato al volo.

> […]
>
>
> PostgreSQL è molto flessibile da questo punto di vista.

v. sopra ;-)

--
Giorgio Valoti

On 10/lug/08, at 13:56, rotellaro(at)gmail(dot)com wrote:

> […]
>
> 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
> );

Stavo pensando che, almeno in certe situazioni, creare un type ad hoc
o creare una view è la stessa cosa con questi eventuali vantaggi:
semplificazione delle funzioni; uso più efficiente delle risorse dato
che il planner è in grado di usare tutte le strategie disponibile per
ottimizzare la query della view, mentre non può farlo con le
funzioni, almeno fino alla 8.4. Giusto? O mi perdo qualcosa?

--
Giorgio Valoti