2727# Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
2828# Portions Copyright (c) 1994, Regents of the University of California
2929#
30- # $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.164 2002/08/04 06:26:38 thomas Exp $
30+ # $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.165 2002/08/08 19:39:05 tgl Exp $
3131#
3232# -------------------------------------------------------------------------
3333
@@ -803,26 +803,29 @@ CREATE VIEW pg_views AS \
803803
804804CREATE VIEW pg_tables AS\
805805 SELECT\
806+ N.nspname AS schemaname,\
806807 C.relname AS tablename,\
807808 pg_get_userbyid(C.relowner) AS tableowner,\
808809 C.relhasindex AS hasindexes,\
809810 C.relhasrules AS hasrules,\
810811 (C.reltriggers > 0) AS hastriggers\
811- FROM pg_class C\
812+ FROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
812813 WHERE C.relkind IN ('r', 's');
813814
814815CREATE VIEW pg_indexes AS\
815816 SELECT\
817+ N.nspname AS schemaname,\
816818 C.relname AS tablename,\
817819 I.relname AS indexname,\
818- pg_get_indexdef(X.indexrelid ) AS indexdef\
819- FROM pg_index X, pg_class C, pg_class I \
820- WHERE C.relkind = 'r' AND I.relkind ='i' \
821- AND C .oid =X.indrelid \
822- oid =X.indexrelid ;
820+ pg_get_indexdef(I.oid ) AS indexdef\
821+ FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) \
822+ JOIN pg_class I ON (I.oid =X.indexrelid) \
823+ LEFT JOIN pg_namespace N ON (N .oid =C.relnamespace) \
824+ WHERE C.relkind = 'r' AND I.relkind ='i' ;
823825
824826CREATE VIEW pg_stats AS\
825827 SELECT\
828+ nspname AS schemaname,\
826829 relname AS tablename,\
827830 attname AS attname,\
828831 stanullfrac AS null_frac,\
@@ -852,16 +855,17 @@ CREATE VIEW pg_stats AS \
852855 WHEN stakind3 THEN stanumbers3[1]\
853856 WHEN stakind4 THEN stanumbers4[1]\
854857 END AS correlation\
855- FROM pg_class c, pg_attribute a, pg_statistic s \
856- WHERE c.oid =s.starelid ANDc.oid =a.attrelid \
857- AND a.attnum =s.staattnum \
858- AND has_table_privilege(c.oid, 'select');
858+ FROMpg_statistic s JOIN pg_class c ON (c.oid = s.starelid) \
859+ JOIN pg_attribute a ON ( c.oid =attrelid ANDattnum =s.staattnum) \
860+ LEFT JOIN pg_namespace n ON (n.oid =c.relnamespace) \
861+ WHERE has_table_privilege(c.oid, 'select');
859862
860863REVOKE ALL on pg_statistic FROM public;
861864
862865CREATE VIEW pg_stat_all_tables AS\
863866 SELECT\
864867 C.oid AS relid,\
868+ N.nspname AS schemaname,\
865869 C.relname AS relname,\
866870 pg_stat_get_numscans(C.oid) AS seq_scan,\
867871 pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,\
@@ -870,22 +874,24 @@ CREATE VIEW pg_stat_all_tables AS \
870874 pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,\
871875 pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,\
872876 pg_stat_get_tuples_deleted(C.oid) AS n_tup_del\
873- FROM pg_class C LEFTOUTER JOIN\
877+ FROM pg_class C LEFT JOIN\
874878 pg_index I ON C.oid = I.indrelid\
879+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
875880 WHERE C.relkind = 'r'\
876- GROUP BY C.oid, C.relname;
881+ GROUP BY C.oid,N.nspname, C.relname;
877882
878883CREATE VIEW pg_stat_sys_tables AS\
879884 SELECT * FROM pg_stat_all_tables\
880- WHERErelname ~ '^pg_' ;
885+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
881886
882887CREATE VIEW pg_stat_user_tables AS\
883888 SELECT * FROM pg_stat_all_tables\
884- WHERErelname !~ '^pg_' ;
889+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
885890
886891CREATE VIEW pg_statio_all_tables AS\
887892 SELECT\
888893 C.oid AS relid,\
894+ N.nspname AS schemaname,\
889895 C.relname AS relname,\
890896 pg_stat_get_blocks_fetched(C.oid) -\
891897 pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,\
@@ -899,86 +905,89 @@ CREATE VIEW pg_statio_all_tables AS \
899905 pg_stat_get_blocks_fetched(X.oid) -\
900906 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,\
901907 pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit\
902- FROM pg_class C LEFTOUTER JOIN\
903- pg_index I ON C.oid = I.indrelid LEFTOUTER JOIN\
904- pg_class T ON C.reltoastrelid = T.oid LEFTOUTER JOIN\
908+ FROM pg_class C LEFT JOIN\
909+ pg_index I ON C.oid = I.indrelid LEFT JOIN\
910+ pg_class T ON C.reltoastrelid = T.oid LEFT JOIN\
905911 pg_class X ON T.reltoastidxid = X.oid\
912+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
906913 WHERE C.relkind = 'r'\
907- GROUP BY C.oid, C.relname, T.oid, X.oid;
914+ GROUP BY C.oid,N.nspname, C.relname, T.oid, X.oid;
908915
909916CREATE VIEW pg_statio_sys_tables AS\
910917 SELECT * FROM pg_statio_all_tables\
911- WHERErelname ~ '^pg_' ;
918+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
912919
913920CREATE VIEW pg_statio_user_tables AS\
914921 SELECT * FROM pg_statio_all_tables\
915- WHERErelname !~ '^pg_' ;
922+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
916923
917924CREATE VIEW pg_stat_all_indexes AS\
918925 SELECT\
919926 C.oid AS relid,\
920927 I.oid AS indexrelid,\
928+ N.nspname AS schemaname,\
921929 C.relname AS relname,\
922930 I.relname AS indexrelname,\
923931 pg_stat_get_numscans(I.oid) AS idx_scan,\
924932 pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,\
925933 pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch\
926- FROM pg_class C,\
927- pg_class I,\
928- pg_index X\
929- WHERE C.relkind = 'r' AND\
930- X.indrelid = C.oid AND\
931- X.indexrelid = I.oid;
934+ FROM pg_class C JOIN\
935+ pg_index X ON C.oid = X.indrelid JOIN\
936+ pg_class I ON I.oid = X.indexrelid\
937+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
938+ WHERE C.relkind = 'r';
932939
933940CREATE VIEW pg_stat_sys_indexes AS\
934941 SELECT * FROM pg_stat_all_indexes\
935- WHERErelname ~ '^pg_' ;
942+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
936943
937944CREATE VIEW pg_stat_user_indexes AS\
938945 SELECT * FROM pg_stat_all_indexes\
939- WHERErelname !~ '^pg_' ;
946+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
940947
941948CREATE VIEW pg_statio_all_indexes AS\
942949 SELECT\
943950 C.oid AS relid,\
944951 I.oid AS indexrelid,\
952+ N.nspname AS schemaname,\
945953 C.relname AS relname,\
946954 I.relname AS indexrelname,\
947955 pg_stat_get_blocks_fetched(I.oid) -\
948956 pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,\
949957 pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit\
950- FROM pg_class C,\
951- pg_class I,\
952- pg_index X\
953- WHERE C.relkind = 'r' AND\
954- X.indrelid = C.oid AND\
955- X.indexrelid = I.oid;
958+ FROM pg_class C JOIN\
959+ pg_index X ON C.oid = X.indrelid JOIN\
960+ pg_class I ON I.oid = X.indexrelid\
961+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
962+ WHERE C.relkind = 'r';
956963
957964CREATE VIEW pg_statio_sys_indexes AS\
958965 SELECT * FROM pg_statio_all_indexes\
959- WHERErelname ~ '^pg_' ;
966+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
960967
961968CREATE VIEW pg_statio_user_indexes AS\
962969 SELECT * FROM pg_statio_all_indexes\
963- WHERErelname !~ '^pg_' ;
970+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
964971
965972CREATE VIEW pg_statio_all_sequences AS\
966973 SELECT\
967974 C.oid AS relid,\
975+ N.nspname AS schemaname,\
968976 C.relname AS relname,\
969977 pg_stat_get_blocks_fetched(C.oid) -\
970978 pg_stat_get_blocks_hit(C.oid) AS blks_read,\
971979 pg_stat_get_blocks_hit(C.oid) AS blks_hit\
972980 FROM pg_class C\
981+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
973982 WHERE C.relkind = 'S';
974983
975984CREATE VIEW pg_statio_sys_sequences AS\
976985 SELECT * FROM pg_statio_all_sequences\
977- WHERErelname ~ '^pg_' ;
986+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
978987
979988CREATE VIEW pg_statio_user_sequences AS\
980989 SELECT * FROM pg_statio_all_sequences\
981- WHERErelname !~ '^pg_' ;
990+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
982991
983992CREATE VIEW pg_stat_activity AS\
984993 SELECT\