ITPub博客

首页 > 应用开发 > IT综合 > test0001

test0001

原创 IT综合 作者:victor1010 时间:2014-11-18 15:13:26 2 删除 编辑


[postgres@myLinux002 lib]$ pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
server starting
[postgres@myLinux002 lib]$ LOG:  database system was shut down at 2014-11-15 08:12:53 UTC
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


[postgres@myLinux002 lib]$ 


postgres=# \dnp
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)


postgres=# \dnp+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)


postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)


postgres=# create schemas sa;
ERROR:  syntax error at or near "schemas"
LINE 1: create schemas sa;
               ^
postgres=# create schema sa;
CREATE SCHEMA
postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 sa     | postgres
(2 rows)


postgres=# 


postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 sa     | postgres
(2 rows)


postgres=# \dnp
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 sa     | postgres
(2 rows)


postgres=# \dnp+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
 sa     | postgres |                      | 
(2 rows)


b1=#  \dt
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | a1        | table | postgres
 public | bb        | table | test
 public | cities    | table | postgres
 public | f         | table | postgres
 public | foo       | table | postgres
 public | mytable   | table | postgres
 public | on_hand   | table | postgres
 public | p         | table | postgres
 public | sal_emp   | table | postgres
 public | t1        | table | postgres
 public | test      | table | postgres
 public | test1     | table | postgres
 public | testdate  | table | postgres
 public | testtable | table | postgres
 public | testtrue  | table | postgres
 public | weather   | table | postgres
(16 rows)


db1=# \dts
            List of relations
 Schema |   Name    |   Type   |  Owner   
--------+-----------+----------+----------
 public | a1        | table    | postgres
 public | bb        | table    | test
 public | cities    | table    | postgres
 public | f         | table    | postgres
 public | foo       | table    | postgres
 public | mytable   | table    | postgres
 public | on_hand   | table    | postgres
 public | p         | table    | postgres
 public | sal_emp   | table    | postgres
 public | t1        | table    | postgres
 public | t1_id_seq | sequence | postgres
 public | test      | table    | postgres
 public | test1     | table    | postgres
 public | testdate  | table    | postgres
 public | testtable | table    | postgres
 public | testtrue  | table    | postgres
 public | weather   | table    | postgres
(17 rows)


db1=# \dtS
                    List of relations
   Schema   |          Name           | Type  |  Owner   
------------+-------------------------+-------+----------
 pg_catalog | pg_aggregate            | table | postgres
 pg_catalog | pg_am                   | table | postgres
 pg_catalog | pg_amop                 | table | postgres
 pg_catalog | pg_amproc               | table | postgres
 pg_catalog | pg_attrdef              | table | postgres
 pg_catalog | pg_attribute            | table | postgres
 pg_catalog | pg_auth_members         | table | postgres
 pg_catalog | pg_authid               | table | postgres
 pg_catalog | pg_cast                 | table | postgres
 pg_catalog | pg_class                | table | postgres
 pg_catalog | pg_collation            | table | postgres
 pg_catalog | pg_constraint           | table | postgres
 pg_catalog | pg_conversion           | table | postgres
 pg_catalog | pg_database             | table | postgres
 pg_catalog | pg_db_role_setting      | table | postgres
 pg_catalog | pg_default_acl          | table | postgres
 pg_catalog | pg_depend               | table | postgres
 pg_catalog | pg_description          | table | postgres
 pg_catalog | pg_enum                 | table | postgres
 pg_catalog | pg_event_trigger        | table | postgres
 pg_catalog | pg_extension            | table | postgres
 pg_catalog | pg_foreign_data_wrapper | table | postgres
 pg_catalog | pg_foreign_server       | table | postgres
 pg_catalog | pg_foreign_table        | table | postgres
 pg_catalog | pg_index                | table | postgres
 pg_catalog | pg_inherits             | table | postgres
 pg_catalog | pg_language             | table | postgres
 pg_catalog | pg_largeobject          | table | postgres
 pg_catalog | pg_largeobject_metadata | table | postgres
 pg_catalog | pg_namespace            | table | postgres
 pg_catalog | pg_opclass              | table | postgres
 pg_catalog | pg_operator             | table | postgres
 pg_catalog | pg_opfamily             | table | postgres
 pg_catalog | pg_pltemplate           | table | postgres
 pg_catalog | pg_proc                 | table | postgres
 pg_catalog | pg_range                | table | postgres
 pg_catalog | pg_rewrite              | table | postgres
 pg_catalog | pg_seclabel             | table | postgres
 pg_catalog | pg_shdepend             | table | postgres
 pg_catalog | pg_shdescription        | table | postgres
 pg_catalog | pg_shseclabel           | table | postgres
 pg_catalog | pg_statistic            | table | postgres
 pg_catalog | pg_tablespace           | table | postgres
 pg_catalog | pg_trigger              | table | postgres
 pg_catalog | pg_ts_config            | table | postgres
 pg_catalog | pg_ts_config_map        | table | postgres
 pg_catalog | pg_ts_dict              | table | postgres
 pg_catalog | pg_ts_parser            | table | postgres
 pg_catalog | pg_ts_template          | table | postgres
 pg_catalog | pg_type                 | table | postgres
 pg_catalog | pg_user_mapping         | table | postgres
 public     | a1                      | table | postgres
 public     | bb                      | table | test
 public     | cities                  | table | postgres
 public     | f                       | table | postgres
 public     | foo                     | table | postgres
 public     | mytable                 | table | postgres
 public     | on_hand                 | table | postgres
 public     | p                       | table | postgres
 public     | sal_emp                 | table | postgres
 public     | t1                      | table | postgres
 public     | test                    | table | postgres
 public     | test1                   | table | postgres
 public     | testdate                | table | postgres
 public     | testtable               | table | postgres
 public     | testtrue                | table | postgres
 public     | weather                 | table | postgres
(67 rows)


db1=# \dtS+
                                 List of relations
   Schema   |          Name           | Type  |  Owner   |    Size    | Description 
------------+-------------------------+-------+----------+------------+-------------
 pg_catalog | pg_aggregate            | table | postgres | 40 kB      | 
 pg_catalog | pg_am                   | table | postgres | 40 kB      | 
 pg_catalog | pg_amop                 | table | postgres | 64 kB      | 
 pg_catalog | pg_amproc               | table | postgres | 56 kB      | 
 pg_catalog | pg_attrdef              | table | postgres | 16 kB      | 
 pg_catalog | pg_attribute            | table | postgres | 408 kB     | 
 pg_catalog | pg_auth_members         | table | postgres | 40 kB      | 
 pg_catalog | pg_authid               | table | postgres | 40 kB      | 
 pg_catalog | pg_cast                 | table | postgres | 48 kB      | 
 pg_catalog | pg_class                | table | postgres | 104 kB     | 
 pg_catalog | pg_collation            | table | postgres | 248 kB     | 
 pg_catalog | pg_constraint           | table | postgres | 48 kB      | 
 pg_catalog | pg_conversion           | table | postgres | 56 kB      | 
 pg_catalog | pg_database             | table | postgres | 40 kB      | 
 pg_catalog | pg_db_role_setting      | table | postgres | 8192 bytes | 
 pg_catalog | pg_default_acl          | table | postgres | 0 bytes    | 
 pg_catalog | pg_depend               | table | postgres | 416 kB     | 
 pg_catalog | pg_description          | table | postgres | 288 kB     | 
 pg_catalog | pg_enum                 | table | postgres | 0 bytes    | 
 pg_catalog | pg_event_trigger        | table | postgres | 0 bytes    | 
 pg_catalog | pg_extension            | table | postgres | 40 kB      | 
 pg_catalog | pg_foreign_data_wrapper | table | postgres | 0 bytes    | 
 pg_catalog | pg_foreign_server       | table | postgres | 0 bytes    | 
 pg_catalog | pg_foreign_table        | table | postgres | 0 bytes    | 
 pg_catalog | pg_index                | table | postgres | 56 kB      | 
 pg_catalog | pg_inherits             | table | postgres | 0 bytes    | 
 pg_catalog | pg_language             | table | postgres | 40 kB      | 
 pg_catalog | pg_largeobject          | table | postgres | 0 bytes    | 
 pg_catalog | pg_largeobject_metadata | table | postgres | 0 bytes    | 
 pg_catalog | pg_namespace            | table | postgres | 40 kB      | 
 pg_catalog | pg_opclass              | table | postgres | 48 kB      | 
 pg_catalog | pg_operator             | table | postgres | 144 kB     | 
 pg_catalog | pg_opfamily             | table | postgres | 48 kB      | 
 pg_catalog | pg_pltemplate           | table | postgres | 40 kB      | 
 pg_catalog | pg_proc                 | table | postgres | 544 kB     | 
 pg_catalog | pg_range                | table | postgres | 40 kB      | 
 pg_catalog | pg_rewrite              | table | postgres | 512 kB     | 
 pg_catalog | pg_seclabel             | table | postgres | 8192 bytes | 
 pg_catalog | pg_shdepend             | table | postgres | 40 kB      | 
 pg_catalog | pg_shdescription        | table | postgres | 48 kB      | 
 pg_catalog | pg_shseclabel           | table | postgres | 0 bytes    | 
 pg_catalog | pg_statistic            | table | postgres | 256 kB     | 
 pg_catalog | pg_tablespace           | table | postgres | 40 kB      | 
 pg_catalog | pg_trigger              | table | postgres | 48 kB      | 
 pg_catalog | pg_ts_config            | table | postgres | 40 kB      | 
 pg_catalog | pg_ts_config_map        | table | postgres | 48 kB      | 
 pg_catalog | pg_ts_dict              | table | postgres | 40 kB      | 
 pg_catalog | pg_ts_parser            | table | postgres | 40 kB      | 
 pg_catalog | pg_ts_template          | table | postgres | 40 kB      | 
 pg_catalog | pg_type                 | table | postgres | 104 kB     | 
 pg_catalog | pg_user_mapping         | table | postgres | 0 bytes    | 
 public     | a1                      | table | postgres | 40 kB      | 
 public     | bb                      | table | test     | 744 kB     | 
 public     | cities                  | table | postgres | 40 kB      | 
 public     | f                       | table | postgres | 872 kB     | 
 public     | foo                     | table | postgres | 0 bytes    | 
 public     | mytable                 | table | postgres | 8192 bytes | 
 public     | on_hand                 | table | postgres | 8192 bytes | 
 public     | p                       | table | postgres | 792 kB     | 
 public     | sal_emp                 | table | postgres | 16 kB      | 
 public     | t1                      | table | postgres | 16 kB      | 
 public     | test                    | table | postgres | 792 kB     | 
 public     | test1                   | table | postgres | 792 kB     | 
 public     | testdate                | table | postgres | 8192 bytes | 
 public     | testtable               | table | postgres | 8192 bytes | 
 public     | testtrue                | table | postgres | 16 kB      | 
 public     | weather                 | table | postgres | 40 kB      | 
(67 rows)


db1=#    \dt+
                        List of relations
 Schema |   Name    | Type  |  Owner   |    Size    | Description 
--------+-----------+-------+----------+------------+-------------
 public | a1        | table | postgres | 40 kB      | 
 public | bb        | table | test     | 744 kB     | 
 public | cities    | table | postgres | 40 kB      | 
 public | f         | table | postgres | 872 kB     | 
 public | foo       | table | postgres | 0 bytes    | 
 public | mytable   | table | postgres | 8192 bytes | 
 public | on_hand   | table | postgres | 8192 bytes | 
 public | p         | table | postgres | 792 kB     | 
 public | sal_emp   | table | postgres | 16 kB      | 
 public | t1        | table | postgres | 16 kB      | 
 public | test      | table | postgres | 792 kB     | 
 public | test1     | table | postgres | 792 kB     | 
 public | testdate  | table | postgres | 8192 bytes | 
 public | testtable | table | postgres | 8192 bytes | 
 public | testtrue  | table | postgres | 16 kB      | 
 public | weather   | table | postgres | 40 kB      | 
(16 rows)


db1=#    \dtS+
                                 List of relations
   Schema   |          Name           | Type  |  Owner   |    Size    | Description 
------------+-------------------------+-------+----------+------------+-------------
 pg_catalog | pg_aggregate            | table | postgres | 40 kB      | 
 pg_catalog | pg_am                   | table | postgres | 40 kB      | 
 pg_catalog | pg_amop                 | table | postgres | 64 kB      | 
 pg_catalog | pg_amproc               | table | postgres | 56 kB      | 
 pg_catalog | pg_attrdef              | table | postgres | 16 kB      | 
 pg_catalog | pg_attribute            | table | postgres | 408 kB     | 
 pg_catalog | pg_auth_members         | table | postgres | 40 kB      | 
 pg_catalog | pg_authid               | table | postgres | 40 kB      | 
 pg_catalog | pg_cast                 | table | postgres | 48 kB      | 
 pg_catalog | pg_class                | table | postgres | 104 kB     | 
 pg_catalog | pg_collation            | table | postgres | 248 kB     | 
 pg_catalog | pg_constraint           | table | postgres | 48 kB      | 
 pg_catalog | pg_conversion           | table | postgres | 56 kB      | 
 pg_catalog | pg_database             | table | postgres | 40 kB      | 
 pg_catalog | pg_db_role_setting      | table | postgres | 8192 bytes | 
 pg_catalog | pg_default_acl          | table | postgres | 0 bytes    | 
 pg_catalog | pg_depend               | table | postgres | 416 kB     | 
 pg_catalog | pg_description          | table | postgres | 288 kB     | 
 pg_catalog | pg_enum                 | table | postgres | 0 bytes    | 
 pg_catalog | pg_event_trigger        | table | postgres | 0 bytes    | 
 pg_catalog | pg_extension            | table | postgres | 40 kB      | 
 pg_catalog | pg_foreign_data_wrapper | table | postgres | 0 bytes    | 
 pg_catalog | pg_foreign_server       | table | postgres | 0 bytes    | 
 pg_catalog | pg_foreign_table        | table | postgres | 0 bytes    | 
 pg_catalog | pg_index                | table | postgres | 56 kB      | 
 pg_catalog | pg_inherits             | table | postgres | 0 bytes    | 
 pg_catalog | pg_language             | table | postgres | 40 kB      | 
 pg_catalog | pg_largeobject          | table | postgres | 0 bytes    | 
 pg_catalog | pg_largeobject_metadata | table | postgres | 0 bytes    | 
 pg_catalog | pg_namespace            | table | postgres | 40 kB      | 
 pg_catalog | pg_opclass              | table | postgres | 48 kB      | 
 pg_catalog | pg_operator             | table | postgres | 144 kB     | 
 pg_catalog | pg_opfamily             | table | postgres | 48 kB      | 
 pg_catalog | pg_pltemplate           | table | postgres | 40 kB      | 
 pg_catalog | pg_proc                 | table | postgres | 544 kB     | 
 pg_catalog | pg_range                | table | postgres | 40 kB      | 
 pg_catalog | pg_rewrite              | table | postgres | 512 kB     | 
 pg_catalog | pg_seclabel             | table | postgres | 8192 bytes | 
 pg_catalog | pg_shdepend             | table | postgres | 40 kB      | 
 pg_catalog | pg_shdescription        | table | postgres | 48 kB      | 
 pg_catalog | pg_shseclabel           | table | postgres | 0 bytes    | 
 pg_catalog | pg_statistic            | table | postgres | 256 kB     | 
 pg_catalog | pg_tablespace           | table | postgres | 40 kB      | 
 pg_catalog | pg_trigger              | table | postgres | 48 kB      | 
 pg_catalog | pg_ts_config            | table | postgres | 40 kB      | 
 pg_catalog | pg_ts_config_map        | table | postgres | 48 kB      | 
 pg_catalog | pg_ts_dict              | table | postgres | 40 kB      | 
 pg_catalog | pg_ts_parser            | table | postgres | 40 kB      | 
 pg_catalog | pg_ts_template          | table | postgres | 40 kB      | 
 pg_catalog | pg_type                 | table | postgres | 104 kB     | 
 pg_catalog | pg_user_mapping         | table | postgres | 0 bytes    | 
 public     | a1                      | table | postgres | 40 kB      | 
 public     | bb                      | table | test     | 744 kB     | 
 public     | cities                  | table | postgres | 40 kB      | 
 public     | f                       | table | postgres | 872 kB     | 
 public     | foo                     | table | postgres | 0 bytes    | 
 public     | mytable                 | table | postgres | 8192 bytes | 
 public     | on_hand                 | table | postgres | 8192 bytes | 
 public     | p                       | table | postgres | 792 kB     | 
 public     | sal_emp                 | table | postgres | 16 kB      | 
 public     | t1                      | table | postgres | 16 kB      | 
 public     | test                    | table | postgres | 792 kB     | 
 public     | test1                   | table | postgres | 792 kB     | 
 public     | testdate                | table | postgres | 8192 bytes | 
 public     | testtable               | table | postgres | 8192 bytes | 
 public     | testtrue                | table | postgres | 16 kB      | 
 public     | weather                 | table | postgres | 40 kB      | 
(67 rows)


db1=#   select now();
              now              
-------------------------------
 2014-11-16 11:36:17.394054+00
(1 row)




db1=# select current_date;
    date    
------------
 2014-11-16
(1 row)


db1=# select now()-current_date;
    ?column?     
-----------------
 11:38:09.004296
(1 row)


db1=#   select now();
              now              
-------------------------------
 2014-11-16 11:38:14.844081+00
(1 row)


db1=# 


db117=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime; 
     uptime      
-----------------
 3 days 22:37:18
(1 row)


db117=# select current_timestamp,pg_postmaster_start_time() ;
              now              |   pg_postmaster_start_time   
-------------------------------+------------------------------
 2014-11-18 22:11:48.952187+08 | 2014-11-14 23:34:05.29886+08
(1 row)




               ^
db117=# select current_date;
    date    
------------
 2014-11-18
(1 row)




               ^
db117=# select now();
              now              
-------------------------------
 2014-11-18 22:12:33.448104+08
(1 row)


db117=# select current_timestamp;
              now              
-------------------------------
 2014-11-18 22:12:51.963146+08
(1 row)




db117=# select pg_postmaster_start_time();
   pg_postmaster_start_time   
------------------------------
 2014-11-14 23:34:05.29886+08
(1 row)


db117=# 




root@TestLinux01 bin]# ./pg_controldata
pg_controldata: no data directory specified
Try "pg_controldata --help" for more information.
[root@TestLinux01 bin]# ./pg_controldata --help
pg_controldata displays control information of a PostgreSQL database cluster.


Usage:
  pg_controldata [OPTION] [DATADIR]


Options:
  -V, --version  output version information, then exit
  -?, --help     show this help, then exit


If no data directory (DATADIR) is specified, the environment variable PGDATA
is used.


Report bugs to .
[root@TestLinux01 bin]# ./pg_controldata /usr/local/pgsql/data
pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           6081584468901461246
Database cluster state:               in production
pg_control last modified:             Tue 18 Nov 2014 10:42:20 PM CST
Latest checkpoint location:           0/1839470
Prior checkpoint location:            0/1839408
Latest checkpoint's REDO location:    0/1839470
Latest checkpoint's REDO WAL file:    000000010000000000000001
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1814
Latest checkpoint's NextOID:          24576
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1799
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Tue 18 Nov 2014 10:42:20 PM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            minimal
Current max_connections setting:      100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
[root@TestLinux01 bin]# 




postgres=# \x
Expanded display is on.
postgres=# select * from pg_database;  
-[ RECORD 1 ]-+------------------------------------
datname       | template1
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | t
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12891
datfrozenxid  | 1799
datminmxid    | 1
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 2 ]-+------------------------------------
datname       | template0
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | t
datallowconn  | f
datconnlimit  | -1
datlastsysoid | 12891
datfrozenxid  | 1799
datminmxid    | 1
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 3 ]-+------------------------------------
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12891
datfrozenxid  | 1799
datminmxid    | 1
dattablespace | 1663
datacl        | 
-[ RECORD 4 ]-+------------------------------------
datname       | db117
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12891
datfrozenxid  | 1799
datminmxid    | 1
dattablespace | 1663
datacl        | 


postgres=# 




SELECT count(*) FROM information_schema.tables  
 WHERE table_schema  
NOT IN ('information_schema', 'pg_catalog');  




postgres=# \d information_schema.tables
                       View "information_schema.tables"
            Column            |               Type                | Modifiers 
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier | 
 table_schema                 | information_schema.sql_identifier | 
 table_name                   | information_schema.sql_identifier | 
 table_type                   | information_schema.character_data | 
 self_referencing_column_name | information_schema.sql_identifier | 
 reference_generation         | information_schema.character_data | 
 user_defined_type_catalog    | information_schema.sql_identifier | 
 user_defined_type_schema     | information_schema.sql_identifier | 
 user_defined_type_name       | information_schema.sql_identifier | 
 is_insertable_into           | information_schema.yes_or_no      | 
 is_typed                     | information_schema.yes_or_no      | 
 commit_action                | information_schema.character_data | 
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            WHEN c.relkind = 'f'::"char" THEN 'FOREIGN TABLE'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type,
    NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name,
    NULL::character varying::information_schema.character_data AS reference_generation,
        CASE
            WHEN t.typname IS NOT NULL THEN current_database()
            ELSE NULL::name
        END::information_schema.sql_identifier AS user_defined_type_catalog,
    nt.nspname::information_schema.sql_identifier AS user_defined_type_schema,
    t.typname::information_schema.sql_identifier AS user_defined_type_name,
        CASE
            WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY (ARRAY['v'::"char", 'f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8 THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_insertable_into,
        CASE
            WHEN t.typname IS NOT NULL THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_typed,
    NULL::character varying::information_schema.character_data AS commit_action
   FROM pg_namespace nc
     JOIN pg_class c ON nc.oid = c.relnamespace
     LEFT JOIN (pg_type t
     JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));


postgres=#   

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8102208/viewspace-1337680/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-29

  • 博文量
    296
  • 访问量
    566697