SQL conception et mise en œuvre

SELECT 'toto'::integer;
ERROR:  invalid input syntax for integer: "toto"
LINE 1: SELECT 'toto'::integer;

SELECT substring('Bonjour, Monde' from 5 for 4);
 substring
-----------
 our,
(1 row)

SELECT version();
                            version
-------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) ...
(1 row)

SELECT substring(version() from 1 for position(' on' in version()));
    substring
-----------------
 PostgreSQL 10.0

SELECT current_date;
 current_date
--------------
 2017-10-04
(1 row)

SELECT current_time;
    current_time
--------------------
 16:32:47.386689+02
(1 row)

SELECT current_timestamp;
       current_timestamp
-------------------------------
 2017-10-04 16:32:50.314897+02
(1 row)

SELECT now();
              now
-------------------------------
 2017-10-04 16:32:53.684813+02
(1 row)

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  dateheure_creation TIMESTAMP DEFAULT current_timestamp,
  valeur VARCHAR);

INSERT INTO test (valeur) VALUES ('Bonjour, monde!');

SELECT * FROM test;
 id |     dateheure_creation     |     valeur
----+----------------------------+-----------------
  1 | 2017-10-04 16:33:46.961088 | Bonjour, monde!
(1 row)

SELECT date_trunc('year',
    age(date '1997-01-05', date '1957-02-28')) AS age_lagaffe;
 age_lagaffe
-------------
 39 years

SELECT extract('year' from
    age(date '1997-01-05', date '1957-02-28')) AS age_lagaffe;
 age_lagaffe
-------------
          39

SELECT timestamp '2012-01-01 10:23:10' - date '0001-01-01' AS soustraction;
     soustraction
----------------------
 734502 days 10:23:10

SELECT timestamp '2001-01-01 10:34:12' + time '23:56:13' AS addition;
      addition
---------------------
 2001-01-02 10:30:25

SELECT date '2001-01-01' + time '23:56:13' AS addition;
      addition
---------------------
 2001-01-01 23:56:13

SELECT timestamp with time zone '2001-01-01 10:34:12' +
    interval '1 day 1 hour' AS addition;
        addition
------------------------
 2001-01-02 11:34:12+01

SELECT date '2001-01-01'  + interval '1 day 1 hour' AS addition;
      addition
---------------------
 2001-01-02 01:00:00

SELECT time '10:34:24' + interval '1 day 1 hour' AS addition;
 addition
----------
 11:34:24

SELECT interval '1 day 1 hour' * 2 AS multiplication;
 multiplication
-----------------
 2 days 02:00:00

SELECT interval '1 day 1 hour' / 2 AS division;
 division
----------
 12:30:00

SELECT interval '1 day 1 hour' + interval '2 hour' AS addition;
    addition
----------------
 1 day 03:00:00

SELECT interval '1 day 1 hour' - interval '2 hour' AS soustraction;
  soustraction
-----------------
 1 day -01:00:00

SELECT current_timestamp;
       current_timestamp
-------------------------------
 2017-10-04 16:35:39.321341+02

SELECT to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS');
       to_char
---------------------
 04/10/2017 16:35:43

SELECT to_timestamp('04/12/2000 12:00:00', 'DD/MM/YYYY HH24:MI:SS');
to_timestamp
------------------------
 2000-12-04 12:00:00+01

SET datestyle = 'ISO, DMY';

SELECT current_timestamp;
              now
-------------------------------
 2017-10-04 16:36:38.189973+02

SET datestyle = 'SQL, DMY';

SELECT current_timestamp;
              now
---------------------------------
 04/10/2017 16:37:04.307034 CEST

SELECT generate_series(date '2012-01-01',date '2012-12-31',interval '1 month');
    generate_series
------------------------
 2012-01-01 00:00:00+01
 2012-02-01 00:00:00+01
 2012-03-01 00:00:00+01
 2012-04-01 00:00:00+02
 2012-05-01 00:00:00+02
 2012-06-01 00:00:00+02
 2012-07-01 00:00:00+02
 2012-08-01 00:00:00+02
 2012-09-01 00:00:00+02
 2012-10-01 00:00:00+02
 2012-11-01 00:00:00+01
 2012-12-01 00:00:00+01
(12 rows)

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW nom
    [ ( nom_colonne [, ...] ) ]
    [ WITH ( nom_option_vue [= valeur_option_vue] [, ... ] ) ]
    AS requete

CREATE TABLE phone_data (person text, phone text, private boolean);

CREATE VIEW phone_number (person, phone) AS
    SELECT person, CASE WHEN NOT private THEN phone END AS phone
    FROM phone_data;

GRANT SELECT ON phone_number TO secretary;

CREATE TABLE phone_data (person text, phone text, private boolean);

CREATE VIEW phone_number (person, phone) AS
    SELECT person, CASE WHEN NOT private THEN phone END AS phone
    FROM phone_data;

INSERT INTO phone_data (person, phone, private)
  VALUES ('Titi', '0123456789', true);

INSERT INTO phone_data (person, phone, private)
  VALUES ('Rominet', '0123456788', false);

SELECT person, phone FROM phone_number;
 person  |   phone
---------+------------
 Titi    |
 Rominet | 0123456788
(2 rows)

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] nom_table [, ...]
         | ALL TABLES IN SCHEMA nom_schéma [, ...] }
    TO { [ GROUP ] nom_rôle | PUBLIC } [, ...] [ WITH GRANT OPTION ]

postgres=# CREATE TABLE elements (id serial, contenu text, prive boolean);
CREATE TABLE
postgres=# INSERT INTO elements (contenu, prive)
VALUES ('a', false), ('b', false), ('c super prive', true),
       ('d', false), ('e prive aussi', true);
INSERT 0 5
postgres=# SELECT * FROM elements;
 id |    contenu    | prive
----+---------------+-------
  1 | a             | f
  2 | b             | f
  3 | c super prive | t
  4 | d             | f
  5 | e prive aussi | t
(5 rows)

postgres=# CREATE OR REPLACE VIEW elements_public AS
SELECT * FROM elements
WHERE CASE WHEN current_user='postgres' THEN TRUE ELSE NOT prive END;
CREATE VIEW
postgres=# SELECT * FROM elements_public;
 id |    contenu    | prive
----+---------------+-------
  1 | a             | f
  2 | b             | f
  3 | c super prive | t
  4 | d             | f
  5 | e prive aussi | t
(5 rows)

postgres=# CREATE USER u1;
CREATE ROLE
postgres=# GRANT SELECT ON elements_public TO u1;
GRANT
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> SELECT * FROM elements;
ERROR:  permission denied for relation elements
postgres=> SELECT * FROM elements_public ;
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f
(3 rows)

postgres=> CREATE OR REPLACE FUNCTION abracadabra(integer, text, boolean)
RETURNS bool AS $$
BEGIN
RAISE NOTICE '% - % - %', $ 1, $ 2, $ 3;
RETURN true;
END$$
LANGUAGE plpgsql
COST 0.0000000000000000000001;
CREATE FUNCTION
postgres=> SELECT * FROM elements_public WHERE abracadabra(id, contenu, prive);
NOTICE:  1 - a - f
NOTICE:  2 - b - f
NOTICE:  3 - c super prive - t
NOTICE:  4 - d - f
NOTICE:  5 - e prive aussi - t
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f
(3 rows)

postgres=> EXPLAIN SELECT * FROM elements_public
WHERE abracadabra(id, contenu, prive);
                                                        QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on elements  (cost=0.00..28.15 rows=202 width=37)
   Filter: (abracadabra(id, contenu, prive) AND
           CASE WHEN ("current_user"() = 'u1'::name)
                THEN (NOT prive) ELSE true END)
(2 rows)

postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE OR REPLACE VIEW elements_public WITH (security_barrier) AS
SELECT * FROM elements
WHERE CASE WHEN current_user='postgres' THEN true ELSE NOT prive END;

CREATE VIEW
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> SELECT * FROM elements_public WHERE abracadabra(id, contenu, prive);
NOTICE:  1 - a - f
NOTICE:  2 - b - f
NOTICE:  4 - d - f
 id | contenu | prive
----+---------+-------
  1 | a       | f
  2 | b       | f
  4 | d       | f
(3 rows)

postgres=> EXPLAIN SELECT * FROM elements_public WHERE
abracadabra(id, contenu, prive);
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Subquery Scan on elements_public  (cost=0.00..34.20 rows=202 width=37)
   Filter: abracadabra(elements_public.id, elements_public.contenu,
           elements_public.prive)
   ->  Seq Scan on elements  (cost=0.00..28.15 rows=605 width=37)
         Filter: CASE WHEN ("current_user"() = 'u1'::name)
                 THEN (NOT prive) ELSE true END
(4 rows)

CREATE TABLE phone_data (person text, phone text, private boolean);


CREATE VIEW maj_phone_number (person, phone, private) AS
    SELECT person, phone, private
      FROM phone_data
     WHERE private = false;


CREATE VIEW phone_number (person, phone) AS
    SELECT person, CASE WHEN NOT private THEN phone END AS phone
      FROM phone_data;


INSERT INTO phone_number VALUES ('Titi', '0123456789');
ERROR:  cannot insert into column "phone" of view "phone_number"
DETAIL:  View columns that are not columns of their base relation are not updatable.
HINT:  To make the view insertable, provide an unconditional ON INSERT
       DO INSTEAD rule or an INSTEAD OF INSERT trigger.


INSERT INTO maj_phone_number VALUES ('Titi', '0123456789', false);


CREATE OR REPLACE FUNCTION phone_number_insert_row()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO phone_data (person, phone, private)
    VALUES (NEW.person, NEW.phone, false);
  RETURN NEW;
END;
$function$;


CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON phone_number
    FOR EACH ROW
    EXECUTE PROCEDURE phone_number_insert_row();


INSERT INTO phone_number VALUES ('Rominet', '0123456788');


SELECT * FROM phone_number;
 person  |   phone
---------+------------
 Titi    | 0123456789
 Rominet | 0123456788
(2 rows)

CREATE TABLE t1 (c1 integer primary key, c2 text);
INSERT INTO t1 select i, md5(random()::text)
FROM generate_series(1, 1000000) AS i;

$ for i in $(seq 1 100000); do
    echo "SELECT * FROM t1 WHERE c1=$i;";
done > requetes_std.sql
echo "PREPARE req AS SELECT * FROM t1 WHERE c1=\$1;" > requetes_prep.sql
for i in $(seq 1 100000); do echo "EXECUTE req($i);"; done >> requetes_prep.sql

$ time psql -f requetes_std.sql postgres >/dev/null

real  0m12.742s
user  0m2.633s
sys 0m0.771s
$ time psql -f requetes_std.sql postgres >/dev/null

real  0m12.781s
user  0m2.573s
sys 0m0.852s
$ time psql -f requetes_prep.sql postgres >/dev/null

real  0m10.186s
user  0m2.500s
sys 0m0.814s
$ time psql -f requetes_prep.sql postgres >/dev/null

real  0m10.131s
user  0m2.521s
sys 0m0.808s

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ]
  ON table_name
  ( { column_name | ( expression ) }
    [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
  [ TABLESPACE tablespace_name ]
  [ WHERE predicate ]

CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;

\timing
Timing is on.

select * from t1 where c1=500000;
   c1   |      c2
--------+--------------
 500000 | Ligne 500000
(1 row)

Time: 109.569 ms

select * from t1 where c1=500000;
   c1   |      c2
--------+--------------
 500000 | Ligne 500000
(1 row)

Time: 109.386 ms

CREATE INDEX ON t1(c1);

SELECT * FROM t1 WHERE c1=500000;
   c1   |      c2
--------+--------------
 500000 | Ligne 500000
(1 row)

Time: 0.675 ms

DROP TABLE t1;
CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
Time: 2761.236 ms

DROP TABLE t1;
CREATE TABLE t1 (c1 integer, c2 text);
CREATE INDEX ON t1(c1);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
Time: 5401.505 ms

\o /dev/null
DROP TABLE t1;
CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
\timing

SELECT * FROM t1 ORDER BY c1;
Time: 618.203 ms

CREATE INDEX ON t1(c1);
SELECT * FROM t1 ORDER BY c1;
Time: 359.369 ms

CREATE TABLE mere (id integer, t text);
CREATE TABLE fille (id integer, mere_id integer, t text);
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
ALTER TABLE fille
  ADD CONSTRAINT fk_mere_fille
      FOREIGN KEY (mere_id)
      REFERENCES mere (id)
         MATCH FULL
         ON UPDATE NO ACTION
         ON DELETE CASCADE
         DEFERRABLE;

BEGIN TRANSACTION;
SET CONSTRAINTS all deferred;

INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');

INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');

COMMIT;

CREATE TABLE personnes (id SERIAL PRIMARY KEY);

CREATE TABLE personne_attributs (
  id_pers INTEGER NOT NULL,
  nom_attr varchar(20) NOT NULL,
  val_attr varchar(100) NOT NULL
);

INSERT INTO personnes (id) VALUES (nextval('personnes_id_seq')) RETURNING id;
 id
----
  1

INSERT INTO personne_attributs (id_pers, nom_attr, val_attr)
  VALUES (1, 'nom', 'Prunelle'),
         (1, 'prenom', 'Léon');
(...)

CREATE EXTENSION hstore;
CREATE TABLE personnes (id SERIAL PRIMARY KEY, attributs hstore);

INSERT INTO personnes (attributs) VALUES ('nom=>Prunelle, prenom=>Léon');
INSERT INTO personnes (attributs) VALUES ('prenom=>Gaston,nom=>Lagaffe');
INSERT INTO personnes (attributs) VALUES ('nom=>DeMaesmaker');

SELECT * FROM personnes;
 id |              attributs
----+--------------------------------------
  1 | "nom"=>"Prunelle", "prenom"=>"Léon"
  2 | "nom"=>"Lagaffe", "prenom"=>"Gaston"
  3 | "nom"=>"DeMaesmaker"
(3 rows)

SELECT id, attributs->'prenom' AS prenom FROM personnes;
 id |  prenom
----+----------
  1 | Léon
  2 | Gaston
  3 |
(3 rows)

SELECT id, attributs->'nom' AS nom FROM personnes;
 id |     nom
----+-------------
  1 | Prunelle
  2 | Lagaffe
  3 | DeMaesmaker
(3 rows)

CREATE TABLE personnes (
  per_id SERIAL PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  pnom VARCHAR(50) NOT NULL,
  ...
);

CREATE TABLE telephones (
  per_id INTEGER NOT NULL,
  numero VARCHAR(20),
  PRIMARY KEY (per_id, numero),
  FOREIGN KEY (per_id) REFERENCES personnes (per_id)
);

CREATE TABLE personnes (
  per_id SERIAL PRIMARY KEY,
  nom VARCHAR(50) NOT NULL,
  pnom VARCHAR(50) NOT NULL,
  numero VARCHAR(20)[]
);

-- Ajout d'une personne
INSERT INTO personnes (nom, pnom, numero)
     VALUES ('Simpson', 'Omer', '{0607080910}');

SELECT *
  FROM personnes;
 per_id |   nom   | pnom |    numero
--------+---------+------+--------------
      1 | Simpson | Omer | {0607080910}
(1 ligne)

-- Ajout d'un numéro de téléphone pour une personne donnée :
UPDATE personnes
   SET numero = numero || '{0102030420}'
 WHERE per_id = 1;

-- Vérification de l'ajout :
SELECT *
  FROM personnes;
 per_id |   nom   | pnom |         numero
--------+---------+------+-------------------------
      1 | Simpson | Omer | {0607080910,0102030420}

-- Séparation des éléments du tableau :
SELECT per_id, nom, pnom, unnest(numero) AS numero
  FROM personnes;
 per_id |   nom   | pnom |   numero
--------+---------+------+------------
      1 | Simpson | Omer | 0607080910
      1 | Simpson | Omer | 0102030420
(2 lignes)

test=# CREATE TABLE comptes (compte_id SERIAL PRIMARY KEY, solde FLOAT);
CREATE TABLE

test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4

test=# SELECT SUM(solde) FROM comptes;
       sum
-----------------
 100000100010010
(1 row)

test=# CREATE TABLE comptes (compte_id SERIAL PRIMARY KEY, solde NUMERIC);
CREATE TABLE

test=# INSERT INTO comptes (solde) VALUES (100000000.1), (10.1), (10000.2),
(100000000000000.1);
INSERT 0 4

test=# SELECT SUM(solde) FROM comptes;
        sum
-------------------
 100000100010010.5
(1 row)

ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username

CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);

INSERT INTO insere VALUES (1, 'XX', 10);

SELECT * FROM insere ;
 id | col1 | col2
----+------+------
  1 | XX   |   10

ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;

INSERT INTO insere VALUES (2, 'XXX', 10);
ERROR:  invalid input syntax for integer: "XXX"
LINE 1: INSERT INTO insere VALUES (2, 'XXX', 10);
                                      ^

SELECT * FROM insere ;
 id | col2 | col1
----+------+------
  1 |   10 | XX

SELECT  Article.datem                          AS Article_1_9,
    Article.degre_alcool                   AS Article_1_10,
    Article.id                             AS Article_1_19,
    Article.iddf_categor                   AS Article_1_20,
    Article.iddp_clsvtel                   AS Article_1_21,
    Article.iddp_cdelist                   AS Article_1_22,
    Article.iddf_cd_prix                   AS Article_1_23,
    Article.iddp_agreage                   AS Article_1_24,
    Article.iddp_codelec                   AS Article_1_25,
    Article.idda_compo                     AS Article_1_26,
    Article.iddp_comptex                   AS Article_1_27,
    Article.iddp_cmptmat                   AS Article_1_28,
    Article.idda_articleparent             AS Article_1_29,
    Article.iddp_danger                    AS Article_1_30,
    Article.iddf_fabric                    AS Article_1_33,
    Article.iddp_marqcom                   AS Article_1_34,
    Article.iddp_nomdoua                   AS Article_1_35,
    Article.iddp_pays                      AS Article_1_37,
    Article.iddp_recept                    AS Article_1_40,
    Article.idda_unalvte                   AS Article_1_42,
    Article.iddb_sitecl                   AS Article_1_43,
    Article.lib_caisse                     AS Article_1_49,
    Article.lib_com                        AS Article_1_50,
    Article.maj_en_attente                 AS Article_1_61,
    Article.qte_stk                        AS Article_1_63,
    Article.ref_tech                       AS Article_1_64,
    1                                      AS Article_1_70,
    CASE
      WHEN (SELECT COUNT(MA.id)
            FROM   da_majart MA
                   join da_majmas MM
                     ON MM.id = MA.idda_majmas
                   join gt_tmtprg TMT
                     ON TMT.id = MM.idgt_tmtprg
                   join gt_prog PROG
                     ON PROG.id = TMT.idgt_prog
            WHERE  idda_article = Article.id
               AND TO_DATE(TO_CHAR(PROG.date_lancement, 'DDMMYYYY')
                           || TO_CHAR(PROG.heure_lancement, ' HH24:MI:SS'),
                            'DDMMYYYY HH24:MI:SS') >= SYSDATE) >= 1 THEN 1
      ELSE 0
    END                                           AS Article_1_74,
    Article.iddp_compnat                          AS Article_2_0,
    Article.iddp_modven                           AS Article_2_1,
    Article.iddp_nature                           AS Article_2_2,
    Article.iddp_preclin                          AS Article_2_3,
    Article.iddp_raybala                          AS Article_2_4,
    Article.iddp_sensgrt                          AS Article_2_5,
    Article.iddp_tcdtfl                           AS Article_2_6,
    Article.iddp_unite                            AS Article_2_8,
    Article.idda_untgrat                          AS Article_2_9,
    Article.idda_unpoids                          AS Article_2_10,
    Article.iddp_unilogi                          AS Article_2_11,
    ArticleComplement.datem                       AS ArticleComplement_5_6,
    ArticleComplement.extgar_depl                 AS ArticleComplement_5_9,
    ArticleComplement.extgar_mo                   AS ArticleComplement_5_10,
    ArticleComplement.extgar_piece                AS ArticleComplement_5_11,
    ArticleComplement.id                          AS ArticleComplement_5_20,
    ArticleComplement.iddf_collect                AS ArticleComplement_5_22,
    ArticleComplement.iddp_gpdtcul                AS ArticleComplement_5_23,
    ArticleComplement.iddp_support                AS ArticleComplement_5_25,
    ArticleComplement.iddp_typcarb                AS ArticleComplement_5_27,
    ArticleComplement.mt_ext_gar                  AS ArticleComplement_5_36,
    ArticleComplement.pres_cpt                    AS ArticleComplement_5_44,
    GenreProduitCulturel.code                     AS GenreProduitCulturel_6_0,
    Collection.libelle                            AS Collection_8_1,
    Gtin.date_dern_vte                            AS Gtin_10_0,
    Gtin.gtin                                     AS Gtin_10_1,
    Gtin.id                                       AS Gtin_10_3,
    Fabricant.code                                AS Fabricant_14_0,
    Fabricant.nom                                 AS Fabricant_14_2,
    ClassificationVenteLocale.niveau1             AS ClassificationVenteL_16_2,
    ClassificationVenteLocale.niveau2             AS ClassificationVenteL_16_3,
    ClassificationVenteLocale.niveau3             AS ClassificationVenteL_16_4,
    ClassificationVenteLocale.niveau4             AS ClassificationVenteL_16_5,
    MarqueCommerciale.code                        AS MarqueCommerciale_18_0,
    MarqueCommerciale.libellelong                 AS MarqueCommerciale_18_4,
    Composition.code                              AS Composition_20_0,
    CompositionTextile.code                       AS CompositionTextile_21_0,
    AssoArticleInterfaceBalance.datem             AS AssoArticleInterface_23_0,
    AssoArticleInterfaceBalance.lib_envoi         AS AssoArticleInterface_23_3,
    AssoArticleInterfaceCaisse.datem              AS AssoArticleInterface_24_0,
    AssoArticleInterfaceCaisse.lib_envoi          AS AssoArticleInterface_24_3,
    NULL                                          AS TypeTraitement_25_0,
    NULL                                          AS TypeTraitement_25_1,
    RayonBalance.code                             AS RayonBalance_31_0,
    RayonBalance.max_cde_article                  AS RayonBalance_31_5,
    RayonBalance.min_cde_article                  AS RayonBalance_31_6,
    TypeTare.code                                 AS TypeTare_32_0,
    GrilleDePrix.datem                            AS GrilleDePrix_34_1,
    GrilleDePrix.libelle                          AS GrilleDePrix_34_3,
    FicheAgreage.code                             AS FicheAgreage_38_0,
    Codelec.iddp_periact                          AS Codelec_40_1,
    Codelec.libelle                               AS Codelec_40_2,
    Codelec.niveau1                               AS Codelec_40_3,
    Codelec.niveau2                               AS Codelec_40_4,
    Codelec.niveau3                               AS Codelec_40_5,
    Codelec.niveau4                               AS Codelec_40_6,
    PerimetreActivite.code                        AS PerimetreActivite_41_0,
    DonneesPersonnalisablesCodelec.gestionreftech AS DonneesPersonnalisab_42_0,
    ClassificationArticleInterne.id               AS ClassificationArticl_43_0,
    ClassificationArticleInterne.niveau1          AS ClassificationArticl_43_2,
    DossierCommercial.id                          AS DossierCommercial_52_0,
    DossierCommercial.codefourndc                 AS DossierCommercial_52_1,
    DossierCommercial.anneedc                     AS DossierCommercial_52_3,
    DossierCommercial.codeclassdc                 AS DossierCommercial_52_4,
    DossierCommercial.numversiondc                AS DossierCommercial_52_5,
    DossierCommercial.indice                      AS DossierCommercial_52_6,
    DossierCommercial.code_ss_classement          AS DossierCommercial_52_7,
    OrigineNegociation.code                       AS OrigineNegociation_53_0,
    MotifBlocageInformation.libellelong           AS MotifBlocageInformat_54_3,
    ArbreLogistique.id                            AS ArbreLogistique_63_1,
    ArbreLogistique.codesap                       AS ArbreLogistique_63_5,
    Fournisseur.code                              AS Fournisseur_66_0,
    Fournisseur.nom                               AS Fournisseur_66_2,
    Filiere.code                                  AS Filiere_67_0,
    Filiere.nom                                   AS Filiere_67_2,
    ValorisationAchat.val_ach_patc                AS Valorisation_74_3,
    LienPrixVente.code                            AS LienPrixVente_76_0,
    LienPrixVente.datem                           AS LienPrixVente_76_1,
    LienGratuite.code                             AS LienGratuite_78_0,
    LienGratuite.datem                            AS LienGratuite_78_1,
    LienCoordonnable.code                         AS LienCoordonnable_79_0,
    LienCoordonnable.datem                        AS LienCoordonnable_79_1,
    LienStatistique.code                          AS LienStatistique_81_0,
    LienStatistique.datem                         AS LienStatistique_81_1
FROM   da_article Article
       join (SELECT idarticle,
                    poids,
                    ROW_NUMBER()
                      over (
                        PARTITION BY RNA.id
                        ORDER BY INNERSEARCH.poids) RN,
                    titre,
                    nom,
                    prenom
             FROM   da_article RNA
                    join (SELECT idarticle,
                        pkg_db_indexation.CALCULPOIDSMOTS(chaine,
                            'foire vins%') AS POIDS,
                        DECODE(index_clerecherche, 'Piste.titre', chaine,
                                                   '')                AS TITRE,
                        DECODE(index_clerecherche, 'Artiste.nom_prenom',
                            SUBSTR(chaine, 0, INSTR(chaine, '_') - 1),
                                                   '')               AS NOM,
                        DECODE(index_clerecherche, 'Artiste.nom_prenom',
                            SUBSTR(chaine, INSTR(chaine, '_') + 1),
                                                   '')               AS PRENOM
                          FROM   ((SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticle'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticle'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art_bal AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticleBalance'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.cde_art_bal AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.codeArticleBalance'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT index_idenreg AS IDARTICLE,
                                          C.lib_com     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.libelleCommercial'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT index_idenreg AS IDARTICLE,
                                          C.lib_com     AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Article.libelleCommercial'
                                          join da_article C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT idda_article AS IDARTICLE,
                                          C.gtin       AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                 'Gtin.gtin'
                                          join da_gtin C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT idda_article AS IDARTICLE,
                                          C.gtin       AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                                'Gtin.gtin'
                                          join da_gtin C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1)
                                  UNION ALL
                                  (SELECT idda_article AS IDARTICLE,
                                          C.ref_frn    AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                        'ArbreLogistique.referenceFournisseur'
                                          join da_arblogi C
                                            ON id = index_idenreg
                                   WHERE  mots_mot = 'foire'
                                   INTERSECT
                                   SELECT idda_article AS IDARTICLE,
                                          C.ref_frn    AS CHAINE,
                                          index_clerecherche
                                   FROM   cstd_mots M
                                          join cstd_index I
                                            ON I.mots_id = M.mots_id
                                               AND index_clerecherche =
                                        'ArbreLogistique.referenceFournisseur'
                                          join da_arblogi C
                                            ON id = index_idenreg
                                   WHERE  mots_mot LIKE 'vins%'
                                      AND 1 = 1))) INNERSEARCH
                      ON INNERSEARCH.idarticle = RNA.id) SEARCHMC
         ON SEARCHMC.idarticle = Article.id
            AND 1 = 1
       left join da_artcmpl ArticleComplement
              ON Article.id = ArticleComplement.idda_article
       left join dp_gpdtcul GenreProduitCulturel
              ON ArticleComplement.iddp_gpdtcul = GenreProduitCulturel.id
       left join df_collect Collection
              ON ArticleComplement.iddf_collect = Collection.id
       left join da_gtin Gtin
              ON Article.id = Gtin.idda_article
                 AND Gtin.principal = 1
                 AND Gtin.db_suplog = 0
       left join df_fabric Fabricant
              ON Article.iddf_fabric = Fabricant.id
       left join dp_clsvtel ClassificationVenteLocale
              ON Article.iddp_clsvtel = ClassificationVenteLocale.id
       left join dp_marqcom MarqueCommerciale
              ON Article.iddp_marqcom = MarqueCommerciale.id
       left join da_compo Composition
              ON Composition.id = Article.idda_compo
       left join dp_comptex CompositionTextile
              ON CompositionTextile.id = Article.iddp_comptex
       left join da_arttrai AssoArticleInterfaceBalance
              ON AssoArticleInterfaceBalance.idda_article = Article.id
                 AND AssoArticleInterfaceBalance.iddp_tinterf = 1
       left join da_arttrai AssoArticleInterfaceCaisse
              ON AssoArticleInterfaceCaisse.idda_article = Article.id
                 AND AssoArticleInterfaceCaisse.iddp_tinterf = 4
       left join dp_raybala RayonBalance
              ON Article.iddp_raybala = RayonBalance.id
       left join dp_valdico TypeTare
              ON TypeTare.id = RayonBalance.iddp_typtare
       left join df_categor Categorie
              ON Categorie.id = Article.iddf_categor
       left join df_grille GrilleDePrix
              ON GrilleDePrix.id = Categorie.iddf_grille
       left join dp_agreage FicheAgreage
              ON FicheAgreage.id = Article.iddp_agreage
       join dp_codelec Codelec
         ON Article.iddp_codelec = Codelec.id
       left join dp_periact PerimetreActivite
              ON PerimetreActivite.id = Codelec.iddp_periact
       left join dp_perscod DonneesPersonnalisablesCodelec
              ON Codelec.id = DonneesPersonnalisablesCodelec.iddp_codelec
                 AND DonneesPersonnalisablesCodelec.db_suplog = 0
                 AND DonneesPersonnalisablesCodelec.iddb_sitecl = 1012124
       left join dp_clsart ClassificationArticleInterne
              ON DonneesPersonnalisablesCodelec.iddp_clsart =
                ClassificationArticleInterne.id
       left join da_artdeno ArticleDenormalise
              ON Article.id = ArticleDenormalise.idda_article
       left join df_clasmnt ClassementFournisseur
              ON ArticleDenormalise.iddf_clasmnt = ClassementFournisseur.id
       left join tr_dosclas DossierDeClassement
              ON ClassementFournisseur.id = DossierDeClassement.iddf_clasmnt
                 AND DossierDeClassement.date_deb <= '2013-09-27'
                 AND COALESCE(DossierDeClassement.date_fin,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
       left join tr_doscomm DossierCommercial
              ON DossierDeClassement.idtr_doscomm = DossierCommercial.id
       left join dp_valdico OrigineNegociation
              ON DossierCommercial.iddp_dossref = OrigineNegociation.id
       left join dp_motbloc MotifBlocageInformation
              ON MotifBlocageInformation.id = ArticleDenormalise.idda_motinf
       left join da_arblogi ArbreLogistique
              ON Article.id = ArbreLogistique.idda_article
                 AND ArbreLogistique.princ = 1
                 AND ArbreLogistique.db_suplog = 0
       left join df_filiere Filiere
              ON ArbreLogistique.iddf_filiere = Filiere.id
       left join df_fourn Fournisseur
              ON Filiere.iddf_fourn = Fournisseur.id
       left join od_dosal dossierALValo
              ON dossierALValo.idda_arblogi = ArbreLogistique.id
                 AND dossierALValo.idod_dossier IS NULL
       left join tt_val_dal valoDossier
              ON valoDossier.idod_dosal = dossierALValo.id
                 AND valoDossier.estarecalculer = 0
       left join tt_valo ValorisationAchat
              ON ValorisationAchat.idtt_val_dal = valoDossier.id
                 AND ValorisationAchat.date_modif_retro IS NULL
                 AND ValorisationAchat.date_debut_achat <= '2013-09-27'
                 AND COALESCE(ValorisationAchat.date_fin_achat,
                    TO_DATE('31129999', 'DDMMYYYY')) >= '2013-09-27'
                 AND ValorisationAchat.val_ach_pab IS NOT NULL
       left join da_lienart assoALPXVT
              ON assoALPXVT.idda_article = Article.id
                 AND assoALPXVT.iddp_typlien = 14893
       left join da_lien LienPrixVente
              ON LienPrixVente.id = assoALPXVT.idda_lien
       left join da_lienart assoALGRAT
              ON assoALGRAT.idda_article = Article.id
                 AND assoALGRAT.iddp_typlien = 14894
       left join da_lien LienGratuite
              ON LienGratuite.id = assoALGRAT.idda_lien
       left join da_lienart assoALCOOR
              ON assoALCOOR.idda_article = Article.id
                 AND assoALCOOR.iddp_typlien = 14899
       left join da_lien LienCoordonnable
              ON LienCoordonnable.id = assoALCOOR.idda_lien
       left join da_lienal assoALSTAT
              ON assoALSTAT.idda_arblogi = ArbreLogistique.id
                 AND assoALSTAT.iddp_typlien = 14897
       left join da_lien LienStatistique
              ON LienStatistique.id = assoALSTAT.idda_lien WHERE
SEARCHMC.rn = 1
   AND ( ValorisationAchat.id IS NULL
          OR ValorisationAchat.date_debut_achat = (
                 SELECT MAX(VALMAX.date_debut_achat)
                 FROM   tt_valo VALMAX
                 WHERE  VALMAX.idtt_val_dal = ValorisationAchat.idtt_val_dal
                    AND VALMAX.date_modif_retro IS NULL
                    AND VALMAX.val_ach_pab IS NOT NULL
                    AND VALMAX.date_debut_achat <= '2013-09-27') )
   AND ( Article.id IN (SELECT A.id
                        FROM   da_article A
                               join du_ucutiar AssoUcUtiAr
                                 ON AssoUcUtiAr.idda_article = A.id
                               join du_asucuti AssoUcUti
                                 ON AssoUcUti.id = AssoUcUtiAr.iddu_asucuti
                        WHERE  ( AssoUcUti.iddu_uti IN ( 90000000000022 ) )
                           AND a.iddb_sitecl = 1012124) )
   AND Article.db_suplog = 0
ORDER  BY SEARCHMC.poids ASC

CREATE INDEX idx_appellation_libelle ON appellation
USING btree (libelle varchar_pattern_ops);

EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on appellation  (cost=0.00..6.99 rows=3 width=24)
   Filter: (libelle ~~ '%wur%'::text)

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_appellation_libelle_trgm ON appellation
USING gist (libelle gist_trgm_ops);

EXPLAIN SELECT * FROM appellation WHERE libelle LIKE '%wur%';
                                        QUERY PLAN

-----------------------------------------------------------------------------
 Bitmap Heap Scan on appellation  (cost=4.27..7.41 rows=3 width=24)
   Recheck Cond: (libelle ~~ '%wur%'::text)
   ->  Bitmap Index Scan on idx_appellation_libelle_trgm  (cost=0.00..4.27...)
         Index Cond: (libelle ~~ '%wur%'::text)

BEGIN ;

UPDATE contacts
SET email = nom||'@dalibo.com'
;

COMMIT ;

UPDATE contacts
SET email = nom||'@dalibo.com'
WHERE email IS NULL
;

SELECT nom||' <'||email||'>'
FROM contacts
;

SELECT upper(nom)||' <'||lower(email)||'>'
FROM contacts
;

ALTER TABLE commandes ADD COLUMN prix_total numeric(10,2);
BEGIN ;

UPDATE commandes c
SET prix_total= (
   /* cette sous-requête fait une jointure entre lignes_commandes et la table
      commandes à mettre à jour pour calculer le prix par commande */
   SELECT SUM(quantite * prix_unitaire - remise)
   FROM lignes_commandes lc
   WHERE lc.numero_commande=c.numero_commande
)
-- on peut récupérer le détail de la mise à jour directement dans la requête :
-- RETURNING numero_commande, prix_total
;

COMMIT ;

UPDATE commandes c SET prix_total=prix_calc
FROM (
    SELECT numero_commande, SUM(quantite * prix_unitaire - remise) AS prix_calc
        FROM lignes_commandes
       GROUP BY numero_commande
) as prix_detail
WHERE prix_detail.numero_commande = c.numero_commande

SELECT extract('month' from date_commande) AS numero_mois,
    round(sum(prix_total),2) AS montant_total
FROM commandes
WHERE date_commande >= to_date('01/01/2010', 'DD/MM/YYYY')
   AND date_commande < to_date('01/01/2011', 'DD/MM/YYYY')
GROUP BY 1
ORDER BY 1
;

BEGIN;

/* en raison de la présence de clés étrangères, il faut en premier leur
   supprimer les lignes de la table lignes_commandes correspondant aux
   commandes à supprimer */
DELETE
FROM lignes_commandes
WHERE numero_commande IN (
   SELECT numero_commande
   FROM commandes
   WHERE date_commande >= to_date('01/05/2010', 'DD/MM/YYYY')
      AND date_commande < to_date('01/06/2010', 'DD/MM/YYYY')
   )
;

-- ensuite seulement on peut supprimer les commandes
DELETE
FROM commandes
WHERE date_commande >= to_date('01/05/2010', 'DD/MM/YYYY')
   AND date_commande < to_date('01/06/2010', 'DD/MM/YYYY')
;

COMMIT ;

WITH del_lc AS (
   /* ici on déclare la CTE qui va se charger de supprimer les lignes dans la
      table lignes_commandes et retourner les numéros de commandes supprimés */
   DELETE
   FROM lignes_commandes
   WHERE numero_commande IN (
      SELECT numero_commande
      FROM commandes
      WHERE date_commande >= to_date('01/05/2010', 'DD/MM/YYYY')
         AND date_commande < to_date('01/06/2010', 'DD/MM/YYYY')
      )
   RETURNING numero_commande
)
/* requête principale, qui supprime les commandes dont les numéros
   correspondent aux numéros de commandes remontés par la CTE */
DELETE
FROM commandes c
WHERE EXISTS (
   SELECT 1
   FROM del_lc
   WHERE del_lc.numero_commande = c.numero_commande
   )
;

SELECT extract('month' from date_commande) AS numero_mois,
    round(sum(prix_total),2) AS montant_total
FROM commandes
GROUP BY 1
ORDER BY 1
;

SELECT numero_mois, round(coalesce(sum(prix_total), 0.0),2) AS montant_total
  /* la fonction generate_series permet de générer une pseudo-table d'une
     colonne contenant les chiffres de 1 à 12 */
FROM generate_series(1, 12) AS numero_mois
  /* le LEFT JOIN entre cette pseudo-table et la table commandes permet de
     s'assurer que, même si aucune commande n'a eu lieu sur un mois, la ligne
     correspondante sera tout de même présente */
LEFT JOIN commandes ON extract('month' from date_commande) = numero_mois
      AND date_commande >= to_date('01/01/2010', 'DD/MM/YYYY')
      AND date_commande < to_date('01/01/2011', 'DD/MM/YYYY')
GROUP BY 1
ORDER BY 1
;

CREATE VIEW commande_montant AS
SELECT
   numero_commande,
   sum(quantite * prix_unitaire - remise) AS total_commande
FROM lignes_commandes
GROUP BY numero_commande
;

SELECT extract('month' from date_commande) AS numero_mois,
    round(sum(total_commande),2) AS montant_total
FROM commandes c
JOIN commande_montant cm ON cm.numero_commande = c.numero_commande
WHERE date_commande >= to_date('01/01/2010', 'DD/MM/YYYY')
   AND date_commande < to_date('01/01/2011', 'DD/MM/YYYY')
GROUP BY 1
ORDER BY 1
;