Instantly share code, notes, and snippets.

Embed
What would you like to do?
C:\Users\Administrateur>psql -U postgres test
psql (11.0)
Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
Les caractères 8 bits peuvent ne pas fonctionner correctement.
Voir la section « Notes aux utilisateurs de Windows » de la page
référence de psql pour les détails.
Connexion SSL (protocole : TLSv1.2, chiffrement : ECDHE-RSA-AES256-GCM-SHA384, bits : 256, compression : désactivé)
Saisissez « help » pour l'aide.
test=# create table personnes(nom character varying(100),prenom character varying(100));
NOTICE: alter table public.personnes rename to exemple_personnes
NOTICE: create or replace function public.tf_exemple_personnes() returns trigger AS $TG$
begin
if tg_op = 'INSERT' or tg_op = 'UPDATE' then
new.nom := '####' || new.nom;
new.prenom := '####' || new.prenom;
return new;
end if;
end; $TG$
language plpgsql volatile
cost 100;
NOTICE: create trigger tg_exemple_personnes before insert or update on public.exemple_personnes for each row
execute procedure public.tf_exemple_personnes();
NOTICE: create or replace view public.personnes as select oid,substr(nom,5) nom,substr(prenom,5) prenom from exemple_personnes
NOTICE: create or replace function public.tf_personnes() returns trigger AS $FC$
begin
if tg_op = 'INSERT' then
insert into exemple_personnes(nom,prenom) values (NEW.nom,NEW.prenom);
return new;
end if;
if tg_op = 'UPDATE' then
raise notice '%', 'update exemple_personnes set nom = NEW.nom,prenom = NEW.prenom where oid = OLD.oid';
update exemple_personnes set nom = NEW.nom,prenom = NEW.prenom where oid = OLD.oid;
return new;
end if;
if tg_op = 'DELETE' then
delete from exemple_personnes where oid = OLD.oid;
return new;
end if;
end; $FC$
language plpgsql volatile
cost 100;
NOTICE: create trigger tg_personnes instead of insert or update or delete on public.personnes for each row
execute procedure public.tf_personnes();
CREATE TABLE
test=# insert into personnes(prenom,nom) values('Marie','Curie');
INSERT 0 1
test=# insert into personnes(prenom,nom) values('Ada','Lovelace');
INSERT 0 1
test=# insert into personnes(prenom,nom) values('Alan','Turing');
INSERT 0 1
test=# select * from personnes;
oid | nom | prenom
-------+----------+--------
39360 | Curie | Marie
39362 | Lovelace | Ada
39366 | Turing | Alan
(3 lignes)
test=# select * from exemple_personnes;
nom | prenom
--------------+-----------
####Curie | ####Marie
####Lovelace | ####Ada
####Turing | ####Alan
(3 lignes)
test=# \d
Liste des relations
SchÚma | Nom | Type | PropriÚtaire
--------+-------------------+-------+--------------
public | exemple_personnes | table | postgres
public | personnes | vue | postgres
(2 lignes)
test=# insert into personnes(prenom,nom) values('Ablert','Eintsein');
INSERT 0 1
test=# select * from exemple_personnes;
nom | prenom
--------------+------------
####Curie | ####Marie
####Lovelace | ####Ada
####Turing | ####Alan
####Eintsein | ####Ablert
(4 lignes)
test=# update personnes set prenom = 'Albert',nom = 'Einstein' where prenom = 'Ablert';
NOTICE: update exemple_personnes set nom = NEW.nom,prenom = NEW.prenom where oid = OLD.oid
UPDATE 1
test=# select * from exemple_personnes;
nom | prenom
--------------+------------
####Curie | ####Marie
####Lovelace | ####Ada
####Turing | ####Alan
####Einstein | ####Albert
(4 lignes)
test=# select * from personnes;
oid | nom | prenom
-------+----------+--------
39360 | Curie | Marie
39362 | Lovelace | Ada
39366 | Turing | Alan
39371 | Einstein | Albert
(4 lignes)
test=# insert into personnes(prenom,nom) values('Maya','L''Abeille');
INSERT 0 1
test=# select * from exemple_personnes;
nom | prenom
---------------+------------
####Curie | ####Marie
####Lovelace | ####Ada
####Turing | ####Alan
####Einstein | ####Albert
####L'Abeille | ####Maya
(5 lignes)
test=# delete from personnes where prenom = 'Maya';
DELETE 0
test=# select * from personnes;
oid | nom | prenom
-------+----------+--------
39360 | Curie | Marie
39362 | Lovelace | Ada
39366 | Turing | Alan
39371 | Einstein | Albert
(4 lignes)
test=# select * from exemple_personnes;
nom | prenom
--------------+------------
####Curie | ####Marie
####Lovelace | ####Ada
####Turing | ####Alan
####Einstein | ####Albert
(4 lignes)
test=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment