-- om5_RAD
-- ce script permet de créer des vues sur information_schema et les droits
-- SET search_path = om5, public, pg_catalog;
-- changer le nom du schema si ce n est pas om5
-- \set schema 'om5'

CREATE OR REPLACE VIEW om_tables  AS 
 SELECT   tables.table_name,
		  table_schema,
		  table_type,
		  obj_description((table_schema||'.'||tables.table_name)::regclass::oid) as comment,
          parametres->>'libelle' as libelle,
          parametres->>'col1' as col1,
          parametres->>'col2' as col2,
          parametres->>'col3' as col3,
          parametres->>'affichage' as affichage,
          parametres->>'nombre_colonne' as nombre_colonne,
          parametres->>'recherche' as recherche  
          FROM information_schema.tables
       left join om_tables_parametre on tables.table_name=om_tables_parametre.table_name
       where table_type = 'BASE TABLE' and table_schema = :'schema'; -- ***

CREATE OR REPLACE VIEW om_champs  AS 
			SELECT   concat(columns.table_name,'.',columns.column_name) as column_name, 
			columns.table_name,
			table_schema,
			data_type,
			is_nullable,
			character_maximum_length,
			column_default,
			col_description((table_schema||'.'||columns.table_name)::regclass::oid, ordinal_position) as comment	 
			FROM information_schema.columns 
			where table_schema =  :'schema'; -- ***

CREATE OR REPLACE VIEW om_contraintes  AS 
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 USING (constraint_schema, constraint_name) 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
WHERE constraint_type = 'FOREIGN KEY'  AND tc.table_schema = :'schema'; -- ***


--SET search_path = om5, public, pg_catalog;
CREATE OR REPLACE VIEW om_vues  AS 
 SELECT   
	table_name,
	table_schema,
	view_definition	
 FROM information_schema.views
where table_schema= :'schema'; -- where schema_name = :'schema' ;


CREATE OR REPLACE VIEW om_proc  AS 
	SELECT 
		proname, 
		prosrc   
	from pg_proc 
	inner join pg_namespace on pg_namespace.oid=pg_proc.pronamespace 
	where nspname= :'schema'; 

CREATE OR REPLACE VIEW om_triggers  AS 
	SELECT 
		trigger_name, 
		trigger_schema, 
		event_manipulation, 
		event_object_table, 
		action_order, 
		action_condition,
		action_statement, 
		action_orientation, 
		action_timing
	from information_schema.triggers
	where trigger_schema = :'schema';
 
 
 


