Last active
November 16, 2018 21:12
-
-
Save achampav/d35e16b658db145543fb0015ab556770 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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