Voir le flux RSS

Alassane Diakité

Création de type personnalisé dans PostgreSQL

Noter ce billet
par , 23/08/2016 à 00h28 (258 Affichages)
PostgreSQL permet la création d'un type personnalisé de donnée.
Dans notre exemple nous créerons le type « fraction » et mettrons en place la possibilité de définir un index sur notre type.

La commande CREATE TYPE permet de créer votre type.
Dans notre cas (fraction) nous devons tenir compte des contraintes…
  • le dénominateur ne peut être nul (zéro !)
  • ni le numérateur et ni le dénominateur ne peuvent être NULL (absence de valeur !)


Le deuxième point peut amener une confusion : la fraction (globalement) peut être NULL (fraction non définie) mais si une fraction est définie (non NULL) alors le deuxième point ci-dessous s’applique.
Pour cela nous créerons deux DOMAINEs (un type existant avec contrainte)
Création des Domaines
dénominateur…
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
CREATE DOMAIN ddenominateur 
AS INTEGER
DEFAULT 1 --la contrainte de NOT NULL prend le dessus sur DEFAULT!!!
CHECK (VALUE IS NOT NULL AND VALUE <> 0);
numérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
CREATE DOMAIN dnumerateur
AS INTEGER
DEFAULT 0 --la contrainte de NOT NULL prend le dessus sur DEFAULT!!!
CHECK (VALUE IS NOT NULL);

Creation du type fraction…
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
CREATE TYPE fraction 
AS (numerateur dnumerateur, denominateur ddenominateur)

Les functions outis pour la simplification
Le pgcd (plus grand commun diviseur)
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE FUNCTION public.pgcd(
    a integer,
    b integer)
  RETURNS integer AS
$BODY$
declare
t integer;
begin
a=abs(a);
b=abs(b);
if(a=0 and b=0) then
raise EXCEPTION 'Les deux valeurs ne peuvent être nulles';
elsif(a=0 or b=0) then
return a+b;
else
	if(a<b) then 
	t=a;
	a=b;
	b=t;
	end if;
	t=a-b;
	if(t=0) then
	return a;
	else
	return pgcd(b,t);
	end if;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE

La simplification
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION public.simplifie_fraction(a fraction)
  RETURNS fraction AS
$BODY$
declare
p integer;
begin
p=pgcd(a.numerateur, a.denominateur);
a.numerateur=a.numerateur/p;
a.denominateur=a.denominateur/p;
return a;
end
 
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Les opérateurs arithmétiques (+, -, /, *)
Pour chaque opérateur on défini une function et la dite opérateur.
Remarque : il est possible de definir ces fonctions en C, nous utiliserons plpgsql.
Addition

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION public.add_fraction(
    gauche fraction,
    droite fraction)
  RETURNS fraction AS
$BODY$
declare 
	n integer;
	d integer;
begin
n=gauche.numerateur*droite.denominateur+gauche.denominateur*droite.numerateur;
d=gauche.denominateur*droite.denominateur;
return simplifie_fraction((n,d)::fraction);
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE OPERATOR public.+(
  PROCEDURE = add_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = +);
Soustraction

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION public.sous_fraction(
    gauche fraction,
    droite fraction)
  RETURNS fraction AS
$BODY$
declare 
	n integer;
	d integer;
begin
n=gauche.numerateur*droite.denominateur-gauche.denominateur*droite.numerateur;
d=gauche.denominateur*droite.denominateur;
return simplifie_fraction((n,d)::fraction);
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
CREATE OPERATOR public.-(
  PROCEDURE = sous_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction);
Multiplication

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION public.mult_fraction(
    gauche fraction,
    droite fraction)
  RETURNS fraction AS
$BODY$
declare 
	n integer;
	d integer;
begin
n=gauche.numerateur*droite.numerateur;
d=gauche.denominateur*droite.denominateur;
return simplifie_fraction((n,d)::fraction);
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE OPERATOR public.*(
  PROCEDURE = mult_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = *);
Division

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION public.div_fraction(
    gauche fraction,
    droite fraction)
  RETURNS fraction AS
$BODY$
declare 
	n integer;
	d integer;
begin
n=gauche.numerateur*droite.denominateur;
d=gauche.denominateur*droite.numerateur;
return simplifie_fraction((n,d)::fraction);
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
CREATE OPERATOR public./(
  PROCEDURE = div_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction);

Les converstions: INTEGER à fraction et fraction à NUMERIC
Une conversion est réalisée par une fonction et objet CAST
INTEGER à fraction

Fonction CAST
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION public.inttofraction(intsrc integer)
  RETURNS fraction AS
$BODY$
declare resultat fraction;
begin
resultat.numerateur=$1::dnumerateur;
resultat.denominateur=1::ddenominateur;
return resultat;
end 
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
CREATE CAST (integer AS fraction)
  WITH FUNCTION public.inttofraction(integer)
  AS ASSIGNMENT;

fraction à NUMERIC

Fonction CAST
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION public.fractiontonumeric(fractionsrc fraction)
  RETURNS numeric AS
$BODY$
declare resultat numeric;
begin
resultat=(fractionsrc.numerateur::numeric/fractionsrc.denominateur::numeric)::numeric;
return resultat;
end 
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
CREATE CAST (fraction AS numeric)
  WITH FUNCTION public.fractiontonumeric(fraction)
  AS ASSIGNMENT;

Les opérateurs de comparaison:=, >, < >=, <=
L'égalité =

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION public.egal_fraction(
    gauche fraction,
    droite fraction)
  RETURNS boolean AS
$BODY$
begin
if $1.numerateur*$2.denominateur=$2.numerateur*$1.denominateur
then return true;
else
return false;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE OPERATOR public.=(
  PROCEDURE = egal_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = =);

Supérieur >

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION public.supp_fraction(
    gauche fraction,
    droite fraction)
  RETURNS boolean AS
$BODY$
begin
if $1::numeric>$2::numeric
then return true;
else
return false;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE OPERATOR public.>(
  PROCEDURE = supp_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = <);

Supérieur ou égal>=

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION public.suppouegal_fraction(
    gauche fraction,
    droite fraction)
  RETURNS boolean AS
$BODY$
begin
if (supp_fraction($1,$2)=true) or (egal_fraction($1,$2)=true)
then return true;
else
return false;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE OPERATOR public.>=(
  PROCEDURE = suppouegal_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = <=);


Inférieur <

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION public.inf_fraction(
    gauche fraction,
    droite fraction)
  RETURNS boolean AS
$BODY$
begin
if $1::numeric<$2::numeric
then return true;
else
return false;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE OPERATOR public.<(
  PROCEDURE = inf_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = >);

Inférieur ou égal>=

Fonction Opérateur
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION public.infouegal_fraction(
    gauche fraction,
    droite fraction)
  RETURNS boolean AS
$BODY$
begin
if (inf_fraction($1,$2)=true) or (egal_fraction($1,$2)=true)
then return true;
else
return false;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
	CREATE OPERATOR public.<=(
  PROCEDURE = infouegal_fraction,
  LEFTARG = fraction,
  RIGHTARG = fraction,
  COMMUTATOR = >=);


Classe et famille d’opérateurs
Pour un définir un index sur notre type nous avons besoin de créer une classe d’opérateur
La classe
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
CREATE OPERATOR CLASS public.fraction_ops DEFAULT
   FOR TYPE fraction USING btree AS
   OPERATOR 1  <,
   OPERATOR 2  <=,
   OPERATOR 3  =,
   OPERATOR 4  >=,
   OPERATOR 5  >,
   FUNCTION 1  public.compare_fraction(fraction, fraction);
... la fonction de comparaison
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE FUNCTION public.compare_fraction(
    gauche fraction,
    droite fraction)
  RETURNS integer AS
$BODY$
begin
if $1>$2
then return 1;
elsif $1<$2
then return -1;
else
return 0;
end if;
end
$BODY$
  LANGUAGE plpgsql IMMUTABLE

La famille (automatiquement créée)
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
CREATE OPERATOR FAMILY public.fraction_ops USING btree;

Création d'une table avec index
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
CREATE TABLE public.lesfractions
(
  idfraction serial NOT NULL,
  unevaleur fraction,
  CONSTRAINT lesfractions_pkey PRIMARY KEY (idfraction)
);
CREATE INDEX ind2
  ON public.lesfractions
  USING btree
  (unevaleur);

Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Viadeo Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Twitter Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Google Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Facebook Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Digg Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Delicious Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog MySpace Envoyer le billet « Création de type personnalisé dans PostgreSQL » dans le blog Yahoo

Mis à jour 17/08/2017 à 14h29 par Malick (Ajout balises code)

Catégories
Bases de données , SQL , PostgreSQL

Commentaires