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