Dear hackers,
please find attached a quick proof of concept for a 'pg_advisor' schema.
Well, the name is 'xpg_advisor' at the time, because it is not a system
schema hence it cannot starts with 'pg_'.
It appears that some support functions would be useful. I've noticed some
are available from pg_catalog, but I have not found yet what I was looking
for.
If you do not like some advices, just "DROP VIEW the_advice;"
If you do not like advices at all, just "DROP SCHEMA xpg_advisor;"
Have a nice day,
--
Fabien Coelho - [EMAIL PROTECTED]
--
-- $Id: pg_advisor.sql,v 1.3 2004/03/19 10:41:47 coelho Exp $
--
-- pg_advisor maybe future system schema?
--
-- at the time it is xpg_advisor as pg_ is reserved.
--
DROP SCHEMA xpg_advisor CASCADE;
CREATE SCHEMA xpg_advisor;
COMMENT ON SCHEMA xpg_advisor
IS 'various advices about database design or performance' ;
SET search_path TO xpg_advisor,pg_catalog;
--
-- DESIGN ADVICES (da_*)
--
--
-- tables without primary keys
--
CREATE VIEW da_tables_without_primary_key AS
SELECT n.nspname AS Schema, c.relname AS Name
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
-- no comments about system catalogs.
n.nspname NOT IN ('pg_catalog','pg_toast','information_schema','xpg_advisor')
-- only tables
AND c.relkind = 'r'
-- no primary key
AND NOT c.relhaspkey
ORDER BY Schema ASC, Name ASC;
COMMENT ON VIEW da_tables_without_primary_key
IS 'it is better to have a primary key on your tables';
-- SELECT * FROM da_tables_without_primary_key;
--
-- tables with composite primary keys?
--
CREATE VIEW da_tables_with_composite_primary_key AS
SELECT n.nspname AS Schema, c.relname AS Name
FROM pg_class AS c
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
JOIN pg_constraint AS o ON (o.conrelid=c.oid)
WHERE
-- no comments about system catalogs.
n.nspname NOT IN ('pg_catalog','pg_toast','information_schema','xpg_advisor')
-- only tables
AND c.relkind = 'r'
-- with a primary key
AND c.relhaspkey
-- the primary key constraint
AND contype = 'p'
-- more than 1 element in conkey
AND array_upper(o.conkey, 1)>1
ORDER BY Schema ASC, Name ASC;
COMMENT ON VIEW da_tables_with_composite_primary_key
IS 'it may be considered a better design to have simple primary keys';
-- SELECT * FROM da_tables_with_composite_primary_key;
--
-- other ideas for later:
--
-- foreign keys which do not exactly match their target key types?
-- foreign keys which do match their target types but with different sizes?
-- tables with large primary keys (such as TEXT, VARCHAR(64)...)?
--
-- count summary of design advices?
-- how to SELECT COUNT(*) FROM [the view of which I have the oid]?
-- Do I need a function that count tuples in a table given its oid?
-- CREATE FUNCTION pg_count_tuples(INTEGER oid) RETURNS INTEGER...
CREATE VIEW design_advices_summary AS
SELECT c.relname AS Name, d.description AS Description
-- , pg_count_tuples(c.oid) AS Count
FROM pg_class AS c
JOIN pg_description AS d ON (d.objoid=c.oid)
JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
-- xpg_advisor views
n.nspname = 'xpg_advisor'
-- only design advices
AND c.relname LIKE 'da_%';
-- SELECT * FROM design_advices_summary;
--
-- PERFORMANCE ADVICES (pa_*)
--
-- no usable primary key index for foreign key referencial integrity checks?
-- no usable index for foreigh key on deletes?
--
-- count summary of performance advices?
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
