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
alter database <ddd> set default_with_oids to on; |
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)); |
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
-- Connexion à l'utilisateur postgres sur la base de données postgres, | |
-- pour les deux serveurs postgresql1101 et postgresql1102. | |
-- Instruction hors transaction | |
create database cadastredb; | |
-- Après création de la base de données cadastredb sur les deux serveurs : | |
create user cadastre with password 'Welcome!2019' login; | |
alter database cadastredb owner to cadastre; |
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
-- Connexion à l'utilisateur postgres sur la base de données cadastredb du serveur postgresql1102 | |
-- Ajout de l'extension Foreign Data Wrapper spécifique à PostgreSQL | |
create extension postgres_fdw; | |
-- Création d'un serveur pointant sur l'autre instance du serveur postgresql1101 | |
create server postgresql1101 | |
foreign data wrapper postgres_fdw | |
options (host 'postgresql1101', port '5432', dbname 'cadastredb'); | |
-- Changement de propriétaire du serveur pour l'utilisateur cadastre |
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
-- Connexion à l'utilisateur cadastre sur la base de données cadastredb du serveur postgresql1102 | |
-- Création d'un lien entre l'utilisateur cadastre local avec l'utilisateur cadastre distant du serveur postgresql1101 | |
create user mapping for cadastre | |
server postgresql1101 | |
options (user 'cadastre', password 'Welcome!2019'); | |
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
-- Connexion à l'utilisateur cadastre sur la base de données cadastredb du serveur postgresql1101 | |
create table villes(id serial,ville character varying(400) not null,constraint pk_villes primary key(id)); | |
insert into villes(ville) values ('Valbonne'),('Antibes'),('Seillans'),('La Gaude'), | |
('Biot'),('Vallauris'),('Golfe-Juan'),('Juan-Les-Pins'),('Villeneuve-Loubet'); |
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
-- Connexion à l'utilisateur cadastre sur la base de données cadastredb du serveur postgresql1102 | |
-- La table est mise en lecture seule. En l'occurrence la table source contient une séquence | |
-- sur la colonne id et il est fortement déconseillé de mettre telle table en lecture/écriture, | |
-- les séquences ne suivant pas d'un serveur à un autre. | |
create foreign table cadastredb_villes( | |
id integer not null, | |
ville character varying(400) not null | |
) | |
server postgresql1101 | |
options (schema_name 'public', table_name 'villes', updatable 'false'); |
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
with agregat_metier as ( | |
select | |
a.d1,a.c1,a.c2,b.c3,b.c4,b.c5,c.c6,c.c7,sum(c.c8) c8 | |
from | |
t1 a | |
join t2 b on a.t2_id = b.id | |
join t3 c on b.t3_id = c.id | |
where | |
a.d1 between to_date(&dte_min,'YYYY-MM-DD') and to_date(&dte_max,'YYYY-MM-DD') | |
group by |
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
create global temporary table temp_agregat_metier( | |
d1 date, | |
c1 varchar2(100), | |
c2 varchar2(100), | |
c3 varchar2(20), | |
c4 number, | |
c5 varchar2(30), | |
c6 number, | |
c7 number, | |
c8 number |
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
create type t_obj_requete_metier as object( | |
d1 date, | |
ca varchar2(60), | |
cb number, | |
cc varchar2(10), | |
c1 varchar2(100), | |
c2 varchar2(100), | |
c3 varchar2(20), | |
c4 number, | |
c5 varchar2(30), |