Fonctionnement de Base¶

l’installation de postgresql se fait simplement par:

lancement d’un exe pour windows (http://www.postgresql.org/download/)
en utilisant votre installateur préféré (apt-get install postgresql)

l’outil principal est le client postgresql psql

gestion du serveur¶

via l’outil pg_ctl

pg_ctl — initialiser, démarrer, arrêter ou contrôler le serveur PostgreSQL

les différentes utilisations:

pg_ctl init[db] [-s] [-D répertoire_données] [-o options-initdb]
pg_ctl start [-w] [-t secondes] [-s] [-D répertoire_données] [-l nomfichier] [-o options] [-p chemin] [-c]
pg_ctl stop [-W] [-t secondes] [-s] [-D répertoire_données] [-m [s[mart]] | [f[ast]] | [i[mmediate]] ]
pg_ctl restart [-w] [-t secondes] [-s] [-D répertoire_données] [-c] [-m [s[mart]] | [f[ast]] | [i[mmediate]] ] [-o options]
pg_ctl reload [-D répertoire_données]
pg_ctl status [-s] [-D répertoire_données]
pg_ctl promote [-s] [-D répertoire_données]
pg_ctl kill nom_signal id_processus
pg_ctl register [-N nom_service] [-U nom_utilisateur] [-P mot_de_passe] [-D répertoire_données] [-S [a[uto]] | [d[emand]] ] [-w] [-t secondes] [-s] [-o options]
pg_ctl unregister [-N nom_service]

Création d’une base¶

CREATE DATABASE Namedatabase WITH ENCODING='UTF8' CONNECTION LIMIT=-1;

Suppression d’une base¶

Il faut d’abord identifier qui est connecté à la base

select *
from pg_stat_activity
where datname = 'mydatabase';

Puis bloquer les nouvelles connections pour le role PUBLIC

REVOKE CONNECT ON DATABASE mycompta FROM PUBLIC;

puis tuer les connexions

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid in (4004, 5384)

ou

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid in (select procpid from pg_stat_activity where datname= 'mydatabase')

et enfin supprimer la base

drop database mydatabase;

Création d’une table¶

un exemple simple

CREATE TABLE cpte (
    id        serial CONSTRAINT key_cpte PRIMARY KEY,
    lib       varchar(100) NOT NULL
);

Note

dans cet exemple une sequence est crée pour gérer l’id de la table cpte

un autre exemple avec:

des contraintes
des foreign clé
des défault

CREATE TABLE op (
    id        serial CONSTRAINT key_op PRIMARY KEY,
    cpt       integer REFERENCES cpte(id),
    dat       date NOT NULL,
    tiers     varchar(100) DEFAULT 'inconnu',
    type      varchar(100) DEFAULT 'autre',
    debit     numeric DEFAULT 0,
    credit    numeric DEFAULT 0,
    etat      char(1) DEFAULT 'N'
);

Création d’un index¶

création d’un index op_idx sur la table op

CREATE UNIQUE INDEX op_idx ON op (cpt,dat,id);

Création d’une view¶

CREATE VIEW op_view AS SELECT   op1.id,
                           op1.cpt,
                           op1.dat,
                           op1.tiers,
                           op1.type,
                           op1.etat,
                           op1.debit,
                           op1.credit,
                           (select sum(op2.credit-op2.debit)
                                   from op as op2
                                   where op2.cpt = op1.cpt
                                   and (op2.dat < op1.dat or (op2.dat = op1.dat and op1.id <= op2.id))) as solde
                   FROM op as op1
                   ORDER BY op1.dat, op1.id;

Note

il faut noter dans cet exemple que les deux requêtes sql sont imbriquées

Intégration d’un trigger¶

Un trigger permet de lancer une action avant ou après une action sur une table.

Les actions prises en compte sont:

INSERT
UPDATE
DELETE

l’implémentation d’un triggers nécessite

la création d’une fonction
la création d’un trigger

exemple de triggers sur la tabke op

CREATE OR REPLACE FUNCTION  mal_helper_trigger() RETURNS TRIGGER AS
 $BODY$
BEGIN
    -- NEW correspond à la ligne qui vient d'être créé/modifiée
    IF EXISTS(
        SELECT *
        FROM helper as tbl
        WHERE tbl.tiers = NEW.tiers
    ) THEN
       UPDATE helper  SET type=NEW.type, debit=NEW.debit, credit=NEW.credit where tiers=NEW.tiers;
    ELSE
       INSERT INTO helper VALUES (NEW.tiers, NEW.type, NEW.debit, NEW.credit);
    END IF;
    RETURN NEW ;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

CREATE TRIGGER maj_helper AFTER INSERT OR UPDATE ON op
    FOR EACH ROW EXECUTE PROCEDURE mal_helper_trigger();

Règle¶

Une règle permet de modifier le comportement d’un select ou d’un update.

Un exemple contraint est la modification de la gestion du DELETE.

Dans la table client on ne souhaite pas supprimer un client mais plutôt le rendre inactif

CREATE RULE supp_client AS ON DELETE TO client
DO INSTEAD UPDATE client set actif = false where nom = OLD.nom

Séquence¶

Une séquence permet d’avoir une suite logique

CREATE [ TEMPORARY | TEMP ] SEQUENCE nom [ INCREMENT [ BY ] incrément ]
[ MINVALUE valeurmin | NO MINVALUE ]
[ MAXVALUE valeurmax | NO MAXVALUE ]
[ START [ WITH ] début ]
[ CACHE cache ]
[ [ NO ] CYCLE ]
[ OWNED BY { table.colonne | NONE } ]

On peut donc indiquer:

un nombre pour le départ
un min
un max
si la séquence boucle (CYCLE)
incrémentation

Créer une séquence ascendante appelée serie, démarrant à 101 :

CREATE SEQUENCE serie START 101;

Sélectionner le prochain numéro de cette séquence :

SELECT nextval('serie');

Utiliser cette séquence dans une commande INSERT :

INSERT INTO distributors VALUES (nextval('serie'), 'nothing');

Information schéma¶

description d’une table

SELECT table_name, column_name, data_type, is_nullable, character_maximum_length, udt_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'mytable'
ORDER BY ordinal_position

liste des clé étrangères

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND table_name='mytable';

liste des clés primaires

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name,
    tc.constraint_type
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name='mytable' where tc.constraint_type="PRIMARY_KEY";

liste des index (issue d’un pk, fk ou index)

SELECT c.indexrelid,  t.relname, a.attname, a.attnum, c.*
     FROM pg_index c
LEFT JOIN pg_class t
       ON c.indrelid  = t.oid
LEFT JOIN pg_attribute a
       ON a.attrelid = t.oid
      AND a.attnum = ANY(indkey)
    WHERE t.relname = 'mytable'

liste des vues

SELECT * FROM pg_views

Création explicite de locks¶

test=# BEGIN WORK;
BEGIN
test=# LOCK TABLE m_user IN EXCLUSIVE MODE;
LOCK TABLE
test=# commit work;
COMMIT
test=# BEGIN WORK;
BEGIN
test=# LOCK TABLE m_user IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
test=# COMMIT WORK;

la visualisation des locks peut être réalisé par la requête suivante:

SELECT *
FROM pg_locks