27
27
# Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
28
28
# Portions Copyright (c) 1994, Regents of the University of California
29
29
#
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 $
31
31
#
32
32
# -------------------------------------------------------------------------
33
33
@@ -803,26 +803,29 @@ CREATE VIEW pg_views AS \
803
803
804
804
CREATE VIEW pg_tables AS\
805
805
SELECT\
806
+ N.nspname AS schemaname,\
806
807
C.relname AS tablename,\
807
808
pg_get_userbyid(C.relowner) AS tableowner,\
808
809
C.relhasindex AS hasindexes,\
809
810
C.relhasrules AS hasrules,\
810
811
(C.reltriggers > 0) AS hastriggers\
811
- FROM pg_class C\
812
+ FROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
812
813
WHERE C.relkind IN ('r', 's');
813
814
814
815
CREATE VIEW pg_indexes AS\
815
816
SELECT\
817
+ N.nspname AS schemaname,\
816
818
C.relname AS tablename,\
817
819
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
- AND I.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' ;
823
825
824
826
CREATE VIEW pg_stats AS\
825
827
SELECT\
828
+ nspname AS schemaname,\
826
829
relname AS tablename,\
827
830
attname AS attname,\
828
831
stanullfrac AS null_frac,\
@@ -852,16 +855,17 @@ CREATE VIEW pg_stats AS \
852
855
WHEN stakind3 THEN stanumbers3[1]\
853
856
WHEN stakind4 THEN stanumbers4[1]\
854
857
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');
859
862
860
863
REVOKE ALL on pg_statistic FROM public;
861
864
862
865
CREATE VIEW pg_stat_all_tables AS\
863
866
SELECT\
864
867
C.oid AS relid,\
868
+ N.nspname AS schemaname,\
865
869
C.relname AS relname,\
866
870
pg_stat_get_numscans(C.oid) AS seq_scan,\
867
871
pg_stat_get_tuples_returned(C.oid) AS seq_tup_read,\
@@ -870,22 +874,24 @@ CREATE VIEW pg_stat_all_tables AS \
870
874
pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,\
871
875
pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,\
872
876
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\
874
878
pg_index I ON C.oid = I.indrelid\
879
+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
875
880
WHERE C.relkind = 'r'\
876
- GROUP BY C.oid, C.relname;
881
+ GROUP BY C.oid,N.nspname, C.relname;
877
882
878
883
CREATE VIEW pg_stat_sys_tables AS\
879
884
SELECT * FROM pg_stat_all_tables\
880
- WHERErelname ~ '^pg_' ;
885
+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
881
886
882
887
CREATE VIEW pg_stat_user_tables AS\
883
888
SELECT * FROM pg_stat_all_tables\
884
- WHERErelname !~ '^pg_' ;
889
+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
885
890
886
891
CREATE VIEW pg_statio_all_tables AS\
887
892
SELECT\
888
893
C.oid AS relid,\
894
+ N.nspname AS schemaname,\
889
895
C.relname AS relname,\
890
896
pg_stat_get_blocks_fetched(C.oid) -\
891
897
pg_stat_get_blocks_hit(C.oid) AS heap_blks_read,\
@@ -899,86 +905,89 @@ CREATE VIEW pg_statio_all_tables AS \
899
905
pg_stat_get_blocks_fetched(X.oid) -\
900
906
pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read,\
901
907
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\
905
911
pg_class X ON T.reltoastidxid = X.oid\
912
+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
906
913
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;
908
915
909
916
CREATE VIEW pg_statio_sys_tables AS\
910
917
SELECT * FROM pg_statio_all_tables\
911
- WHERErelname ~ '^pg_' ;
918
+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
912
919
913
920
CREATE VIEW pg_statio_user_tables AS\
914
921
SELECT * FROM pg_statio_all_tables\
915
- WHERErelname !~ '^pg_' ;
922
+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
916
923
917
924
CREATE VIEW pg_stat_all_indexes AS\
918
925
SELECT\
919
926
C.oid AS relid,\
920
927
I.oid AS indexrelid,\
928
+ N.nspname AS schemaname,\
921
929
C.relname AS relname,\
922
930
I.relname AS indexrelname,\
923
931
pg_stat_get_numscans(I.oid) AS idx_scan,\
924
932
pg_stat_get_tuples_returned(I.oid) AS idx_tup_read,\
925
933
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';
932
939
933
940
CREATE VIEW pg_stat_sys_indexes AS\
934
941
SELECT * FROM pg_stat_all_indexes\
935
- WHERErelname ~ '^pg_' ;
942
+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
936
943
937
944
CREATE VIEW pg_stat_user_indexes AS\
938
945
SELECT * FROM pg_stat_all_indexes\
939
- WHERErelname !~ '^pg_' ;
946
+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
940
947
941
948
CREATE VIEW pg_statio_all_indexes AS\
942
949
SELECT\
943
950
C.oid AS relid,\
944
951
I.oid AS indexrelid,\
952
+ N.nspname AS schemaname,\
945
953
C.relname AS relname,\
946
954
I.relname AS indexrelname,\
947
955
pg_stat_get_blocks_fetched(I.oid) -\
948
956
pg_stat_get_blocks_hit(I.oid) AS idx_blks_read,\
949
957
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';
956
963
957
964
CREATE VIEW pg_statio_sys_indexes AS\
958
965
SELECT * FROM pg_statio_all_indexes\
959
- WHERErelname ~ '^pg_' ;
966
+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
960
967
961
968
CREATE VIEW pg_statio_user_indexes AS\
962
969
SELECT * FROM pg_statio_all_indexes\
963
- WHERErelname !~ '^pg_' ;
970
+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
964
971
965
972
CREATE VIEW pg_statio_all_sequences AS\
966
973
SELECT\
967
974
C.oid AS relid,\
975
+ N.nspname AS schemaname,\
968
976
C.relname AS relname,\
969
977
pg_stat_get_blocks_fetched(C.oid) -\
970
978
pg_stat_get_blocks_hit(C.oid) AS blks_read,\
971
979
pg_stat_get_blocks_hit(C.oid) AS blks_hit\
972
980
FROM pg_class C\
981
+ LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\
973
982
WHERE C.relkind = 'S';
974
983
975
984
CREATE VIEW pg_statio_sys_sequences AS\
976
985
SELECT * FROM pg_statio_all_sequences\
977
- WHERErelname ~ '^pg_' ;
986
+ WHEREschemaname IN ('pg_catalog', 'pg_toast') ;
978
987
979
988
CREATE VIEW pg_statio_user_sequences AS\
980
989
SELECT * FROM pg_statio_all_sequences\
981
- WHERErelname !~ '^pg_' ;
990
+ WHEREschemaname NOT IN ('pg_catalog', 'pg_toast') ;
982
991
983
992
CREATE VIEW pg_stat_activity AS\
984
993
SELECT\