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 or replace function fet_exemple() | |
returns event_trigger AS | |
$$ | |
declare | |
l_obj record; | |
l_rename character varying := ''; | |
l_col character varying := ''; | |
l_function_table character varying := ''; | |
l_function_view character varying := ''; | |
l_trigger_table character varying := ''; |
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 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 | |
-- 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 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 | |
-- 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), |
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
select | |
d1,ca,cb,cc,c1,c2,c3,c4,c5,c6,c7,c8 | |
from | |
table(f_requete_metier(&dte_min,&dte_max,&filtre1,&filtre2,&filtre3)); |