Kenia Milene wrote:
> Oi Pessoal ....
> Bem ... Estou num mato sem cachorro .....
> Preciso montar um SQL que me retorne a quantidade de objetos, separado
> por tipo e por schema.
> Eu consigo fazer os benditos selects separadamente mas preciso disso
> tudo em uma unica linha para melhor visuazizar os resultados.
> Sendo assim o que eu preciso é algo do tipo:
> SCHEMA TABELAS VIEW SEQUENCES ....
> SCHEMA TABELAS VIEW SEQUENCES ....
> SCHEMA TABELAS VIEW SEQUENCES ....
> SCHEMA TABELAS VIEW SEQUENCES ....
>
> Segue abaixo os selects que montei separadamente ... (Agora só falta
> juntar tudo !!!!)
>
> SELECT specific_schema as "Schema", count(data_type) AS "Funções"
> FROM information_schema.routines
> WHERE specific_schema NOT IN
> ('dbateste','information_schema','pg_catalog','pg_temp_1',
> 'pg_toast','xmg','postgres','publico','public')
> AND data_type <> '"trigger"'
> GROUP BY specific_schema;
>
>
> SELECT sequence_schema as "Schema", count(data_type) as "Sequence"
> FROM information_schema.sequences
> WHERE sequence_schema NOT IN
> ('dbateste','information_schema','pg_catalog','pg_temp_1',
> 'pg_toast','xmg','postgres','publico','public')
> GROUP BY sequence_schema;
>
>
> SELECT table_schema as "Schema", count(table_type) as "Tabelas"
> FROM information_schema.tables
> WHERE table_schema NOT IN
> ('dbateste','information_schema','pg_catalog','pg_temp_1',
> 'pg_toast','xmg','postgres','publico','public')
> AND table_type = 'BASE TABLE'
> GROUP BY table_schema;
>
>
> SELECT specific_schema as "Schema", count(data_type) AS "Funções de
> Triggers"
> FROM information_schema.routines
> WHERE specific_schema NOT IN
> ('dbateste','information_schema','pg_catalog','pg_temp_1',
> 'pg_toast','xmg','postgres','publico','public')
> AND data_type = '"trigger"'
> GROUP BY specific_schema;
>
>
>
> SELECT table_schema as "Schema", count(table_type) as "Views"
> FROM information_schema.tables
> WHERE table_schema NOT IN
> ('dbateste','information_schema','pg_catalog','pg_temp_1',
> 'pg_toast','xmg','postgres','publico','public')
> AND table_type = 'VIEW'
> GROUP BY table_schema;
>
>
> SELECT trigger_schema as "Schema", count(trigger_catalog) AS "Triggers"
> FROM information_schema.triggers
> WHERE trigger_schema NOT IN
> ('dbateste','information_schema','pg_catalog','pg_temp_1',
> 'pg_toast','xmg','postgres','publico','public')
> GROUP BY trigger_schema;
>
Põe cada SELECT como uma coluna de um outro SELECT, fazendo assim
subselects.
Por exemplo:
SELECT
(SELECT count(data_type)
FROM information_schema.routines
WHERE specific_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
AND data_type <> '"trigger"'
GROUP BY specific_schema) AS "Funções",
(SELECT count(table_type)
FROM information_schema.tables
WHERE table_schema NOT IN
('dbateste','information_schema','pg_catalog','pg_temp_1',
'pg_toast','xmg','postgres','publico','public')
AND table_type = 'BASE TABLE'
GROUP BY table_schema) as "Tabelas"
e assim vai...
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
