Equivalent PostgreSQL des associative arrays (table of index by) d'Oracle Database

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Equivalent PostgreSQL des associative arrays (table of index by) d'Oracle Database

Message par Phil »

Merci à Guillaume pour sa question :

Sous PL/SQL, j’utilise ce genre de variable :
TYPE montypedetest IS TABLE OF varchar(30) index by varchar(30);

Connaitriez-vous un équivalent sous PostgreSQL ?


Réponse :
Il est possible d'utiliser JSON ou encore pour du clé/valeur simple l'extension et le type HSTORE.
Exemples de code avec HSTORE :

Code : Tout sélectionner

select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 ligne)

create table t1(cle text, valeur text);

insert into t1 values('a', 'b'),('c', 'd'),('e', 'f'),(null::text,'h'),('i',null::text);

-- remplir un hstore depuis 2 colonnes d'une table utilisées respectivement comme clé et comme valeur et le renvoyer

Create or replace function essai_2() returns hstore
as $$
declare 
newTables hstore;
begin
newtables :=  hstore(array_agg(cle ORDER BY cle), array_agg(valeur ORDER BY cle)) FROM (SELECT cle, valeur from t1 where cle is not null) as t1a ;
return newtables;
end;
$$ language plpgsql;

select essai_2();
                 essai_2
-----------------------------------------
"a"=>"b", "c"=>"d", "e"=>"f", "i"=>NULL
(1 ligne)

-- remplir un hstore depuis une table et et renvoyer une valeur en fonction d’une clé fournie en argument

create or replace function essai_1(arg_cle text) returns text
as $$
declare 
newTables hstore;
begin
newtables :=  hstore(array_agg(cle ORDER BY cle), array_agg(valeur ORDER BY cle)) FROM (SELECT cle, valeur from t1 where cle is not null) as t1a;
return newtables->arg_cle;
end;
$$ language plpgsql;

select essai_1('c');
essai_1
---------
d
(1 ligne)

-- remplir un hstore depuis une table et renvoyer vrai si une clé donnée en argument est présente, faux sinon

create or replace function essai_3(arg_cle text) returns text
as $$
declare 
newTables hstore;
begin
newtables :=  hstore(array_agg(cle ORDER BY cle), array_agg(valeur ORDER BY cle)) FROM (SELECT cle, valeur from t1 where cle is not null) as t1a;
return exist(newtables, arg_cle);
end;
$$ language plpgsql;

select essai_3('c');
essai_3
---------
true
(1 ligne)

select essai_3('z');
essai_3
---------
false
(1 ligne)

-- remplir un hstore depuis un curseur et le renvoyer

create or replace function essai_4() returns hstore
as $$
declare 
newTables hstore;
c1 CURSOR FOR SELECT cle, valeur FROM t1 where cle is not null;
r1 record;
begin
open c1;
FETCH c1 INTO r1;
newTables := hstore(r1.cle, r1.valeur);
LOOP
      FETCH c1 INTO r1;
      EXIT WHEN NOT FOUND;    
      newTables := newTables || hstore(r1.cle, r1.valeur);
END LOOP;
close c1;
return newTables;
end;
$$ language plpgsql;

select essai_4();
                 essai_4
-----------------------------------------
"a"=>"b", "c"=>"d", "e"=>"f", "i"=>NULL
(1 ligne)

-- parcourir le hstore et sortir lorsqu'une clé particulière est atteinte 

create or replace function essai_6() returns void
as $$
declare 
newTables hstore;
var_cle text;
begin
newtables :=  hstore(array_agg(cle ORDER BY cle), array_agg(valeur ORDER BY cle)) FROM (SELECT cle, valeur from t1 where cle is not null) as t1a ;
FOREACH var_cle IN ARRAY akeys(newtables)
   LOOP
        RAISE NOTICE 'key=%', var_cle;
       IF var_cle = 'e' THEN
                               exit;
      END IF;
   END LOOP;
end;
$$ language plpgsql;

select essai_6();
NOTICE:  key=a
NOTICE:  key=c
NOTICE:  key=e
essai_6
---------

(1 ligne)

...
Cdlt. Phil - pgphil.ovh
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Equivalent PostgreSQL des associative arrays (table of index by) d'Oracle Database

Message par Phil »

Merci à Guillaume pour sa question :

"Dans essai_4, est-il possible d'initialiser ou de vider un hstore sans qu'il soit NULL pour commencer à boucler et concaténer directement ?"

Réponse :

Oui, pas de problème. Exemple :

Code : Tout sélectionner


select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 ligne)

create or replace function essai_9() returns hstore
as $$
declare 
newTables hstore;
begin
newtables :=  hstore(array_agg(cle ORDER BY cle), array_agg(valeur ORDER BY cle)) FROM (SELECT cle, valeur from t1 where cle is not null) as t1a ;
newtables :=  hstore('');
newtables :=  newtables || hstore('a','b');
return newtables;
end;
$$ language plpgsql;

select essai_9();                                                    
essai_8
----------
"a"=>"b"
(1 ligne)
Cdlt. Phil - pgphil.ovh
Répondre