gwtipstricks:idxtipstricks:estrazionemodellodatifisico_querypostgres [GeowebFramework]

Questa pagina è in sola lettura. Puoi visualizzare il sorgente, ma non puoi modificarlo. Contatta l'amministratore se pensi che ci sia un errore.

====== Estrazione Modello dati FISICO - Query Postgres ======
Query da utilizzare per ottenere un tracciato, esportabile in excel e ovviamente da impaginare, che descrive il modello dati fisico (tabelle e viste) di un DB.

Rif. ABA

<code sql>

select a.table_schema as schema,a.table_name,a.ordinal_position, a.column_name, a.is_nullable, a.data_type,a.character_maximum_length,b.description as comment,
case when a.data_type is not null and a.data_type is not null then a.data_type||' ('||a.character_maximum_length||')'  else a.data_type end as final_data_type,
cos.constraint_type,tb.table_type
from INFORMATION_SCHEMA.COLUMNS a
left join (SELECT c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
  inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
  inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
    and  c.table_schema=st.schemaname and c.table_name=st.relname)) b on b.table_name||'.'||b.column_name=a.table_name||'.'||a.column_name
 left join (SELECT c.table_name,c.column_name, tc.constraint_type
FROM information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
  AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE constraint_type not in ('CHECK')) cos on cos.table_name||'.'||cos.column_name = a.table_name||'.'||a.column_name
left join (select table_name, table_type from INFORMATION_SCHEMA.TABLES)
 tb on tb.table_name=a.table_name
where a.table_schema ='cde_data' and a.table_name not like 'act%' 
order by a.table_name,a.ordinal_position

</code>