### [PostgreSQL 系统表查看系统信息](http://lovejuan1314.iteye.com/blog/1167671)** **博客分类：** - [数据库(Oracle/Postgresql/Hypertable等)](http://lovejuan1314.iteye.com/category/32183)   摘自[http://www.alberton.info/postgresql_meta_info.html](http://www.alberton.info/postgresql_meta_info.html)  测试数据  Sql代码  [](# "收藏这段代码") 1. -- sample data to test PostgreSQL INFORMATION_SCHEMA   1.     1. -- TABLE TEST   1. CREATE TABLE TEST (   1.   TEST_NAME CHAR(30) NOT NULL,   1.   TEST_ID INTEGER DEFAULT '0' NOT NULL,   1.   TEST_DATE TIMESTAMP NOT NULL   1. );   1. ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);   1.     1. -- TABLE TEST2 with some CONSTRAINTs and an INDEX   1. CREATE TABLE TEST2 (   1.   ID INTEGER NOT NULL,   1.   FIELD1 INTEGER,   1.   FIELD2 CHAR(15),   1.   FIELD3 VARCHAR(50),   1.   FIELD4 INTEGER,   1.   FIELD5 INTEGER,   1.   ID2 INTEGER NOT NULL   1. );   1. ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID2);   1. ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD1ID_IDX UNIQUE (ID, FIELD1);   1. ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD4_IDX UNIQUE (FIELD4);   1. CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);   1.     1. -- TABLE NUMBERS   1. CREATE TABLE NUMBERS (   1.   NUMBER INTEGER DEFAULT '0' NOT NULL,   1.   EN CHAR(100) NOT NULL,   1.   FR CHAR(100) NOT NULL   1. );   1.     1. -- TABLE NEWTABLE   1. CREATE TABLE NEWTABLE (   1.   ID INT DEFAULT 0 NOT NULL,   1.   SOMENAME VARCHAR (12),   1.   SOMEDATE TIMESTAMP NOT NULL   1. );   1. ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);   1. CREATE SEQUENCE NEWTABLE_SEQ INCREMENT 1 START 1;   1.     1. -- VIEW on TEST   1. CREATE VIEW "testview"(   1.   TEST_NAME,   1.   TEST_ID,   1.   TEST_DATE   1. ) AS   1. SELECT *   1. FROM TEST   1. WHERE TEST_NAME LIKE 't%';   1.     1. -- VIEW on NUMBERS   1. CREATE VIEW "numbersview"(   1.   NUMBER,   1.   TRANS_EN,   1.   TRANS_FR   1. ) AS   1. SELECT *   1. FROM NUMBERS   1. WHERE NUMBER > 100;   1.     1. -- TRIGGER on NEWTABLE   1. CREATE FUNCTION add_stamp() RETURNS OPAQUE AS '   1.     BEGIN   1.       IF (NEW.somedate IS NULL OR NEW.somedate = 0) THEN   1.         NEW.somedate := CURRENT_TIMESTAMP;   1.         RETURN NEW;   1.       END IF;   1.     END;   1. ' LANGUAGE 'plpgsql';   1.     1. CREATE TRIGGER ADDCURRENTDATE   1. BEFORE INSERT OR UPDATE   1. ON newtable FOR EACH ROW   1.   EXECUTE PROCEDURE add_stamp();   1.     1. -- TABLEs for testing CONSTRAINTs   1. CREATE TABLE testconstraints (   1.   someid integer NOT NULL,   1.   somename character varying(10) NOT NULL,   1.   CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)   1. );   1. CREATE TABLE testconstraints2 (   1.   ext_id integer NOT NULL,   1.   modified date,   1.   uniquefield character varying(10) NOT NULL,   1.   usraction integer NOT NULL,   1.   CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)   1.       REFERENCES testconstraints (someid) MATCH SIMPLE   1.       ON UPDATE CASCADE ON DELETE CASCADE,   1.   CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),   1.   CONSTRAINT uniquefld_idx UNIQUE (uniquefield)   1. );   列出所有数据库中的表名  Sql代码  [](# "收藏这段代码") 1. SELECT relname   1.   FROM pg_class   1.  WHERE relname !~ '^(pg_|sql_)'   1.    AND relkind = 'r';   1. <!--   1. SELECT c.relname AS "Name"   1.   FROM pg_class c, pg_user u   1.  WHERE c.relowner = u.usesysid   1.    AND c.relkind = 'r'   1.    AND NOT EXISTS (   1.        SELECT 1   1.          FROM pg_views   1.         WHERE viewname = c.relname   1.        )   1.    AND c.relname !~ '^(pg_|sql_)'   1. UNION   1. SELECT c.relname AS "Name"   1.   FROM pg_class c   1.  WHERE c.relkind = 'r'   1.    AND NOT EXISTS (   1.        SELECT 1   1.          FROM pg_views   1.         WHERE viewname = c.relname   1.        )   1.    AND NOT EXISTS (   1.        SELECT 1   1.          FROM pg_user   1.         WHERE usesysid = c.relowner   1.        )   1.    AND c.relname !~ '^pg_';   1. -->    1. -- using INFORMATION_SCHEMA:   1.     1. SELECT table_name   1.   FROM information_schema.tables   1.  WHERE table_type = 'BASE TABLE'   1.    AND table_schema NOT IN   1.        ('pg_catalog', 'information_schema');   列出所有视图  Sql代码  [](# "收藏这段代码") 1. -- with postgresql 7.2:   1.     1. SELECT viewname   1.   FROM pg_views   1.  WHERE viewname !~ '^pg_';   1.     1. -- with postgresql 7.4 and later:   1.     1. SELECT viewname   1.   FROM pg_views   1.  WHERE schemaname NOT IN   1.        ('pg_catalog', 'information_schema')   1.    AND viewname !~ '^pg_';   1.     1. -- using INFORMATION_SCHEMA:   1.     1. SELECT table_name   1.   FROM information_schema.tables   1.  WHERE table_type = 'VIEW'   1.    AND table_schema NOT IN   1.        ('pg_catalog', 'information_schema')   1.    AND table_name !~ '^pg_';   1.     1. -- or   1.     1. SELECT table_name   1.   FROM information_schema.views   1.  WHERE table_schema NOT IN ('pg_catalog', 'information_schema')   1.    AND table_name !~ '^pg_';   1. <!--   1. # show only the VIEWs referencing a given table   1.     1.       SELECT viewname   1.         FROM pg_views   1. NATURAL JOIN pg_tables   1.        WHERE tablename ='test';   1. -->   列出所有用户  Sql代码  [](# "收藏这段代码") 1. SELECT usename   1.   FROM pg_user;   列出某表中得所有字段  Sql代码  [](# "收藏这段代码") 1. SELECT a.attname   1.   FROM pg_class c, pg_attribute a, pg_type t   1.  WHERE c.relname = 'test2'   1.    AND a.attnum > 0   1.    AND a.attrelid = c.oid   1.    AND a.atttypid = t.oid   1.     1. -- with INFORMATION_SCHEMA:   1.     1. SELECT column_name   1.   FROM information_schema.columns   1.  WHERE table_name = 'test2';   列出某表字段的信息  Sql代码  [](# "收藏这段代码") 1. SELECT a.attnum AS ordinal_position,   1.          a.attname AS column_name,   1.          t.typname AS data_type,   1.          a.attlen AS character_maximum_length,   1.          a.atttypmod AS modifier,   1.          a.attnotnull AS notnull,   1.          a.atthasdef AS hasdefault   1.     FROM pg_class c,   1.          pg_attribute a,   1.          pg_type t   1.    WHERE c.relname = 'test2'   1.      AND a.attnum > 0   1.      AND a.attrelid = c.oid   1.      AND a.atttypid = t.oid   1. ORDER BY a.attnum;   1.     1. -- with INFORMATION_SCHEMA:   1.     1.   SELECT ordinal_position,   1.          column_name,   1.          data_type,   1.          column_default,   1.          is_nullable,   1.          character_maximum_length,   1.          numeric_precision   1.     FROM information_schema.columns   1.    WHERE table_name = 'test2'   1. ORDER BY ordinal_position;   List INDICES  Here's the query that will return the names of the INDICES defined in the TEST2 table. Unfortunately I have no idea how to extract them from the INFORMATION_SCHEMA. If you do, please let me know.  NB: the CONSTRAINTs are not listed  Sql代码  [](# "收藏这段代码") 1. SELECT relname   1.   FROM pg_class   1.  WHERE oid IN (   1.     SELECT indexrelid   1.       FROM pg_index, pg_class   1.      WHERE pg_class.relname='test2'   1.        AND pg_class.oid=pg_index.indrelid   1.        AND indisunique != 't'   1.        AND indisprimary != 't'   1.        );   列出表的索引信息  Sql代码  [](# "收藏这段代码") 1. SELECT relname, indkey   1.   FROM pg_class, pg_index   1.  WHERE pg_class.oid = pg_index.indexrelid   1.    AND pg_class.oid IN (   1.     SELECT indexrelid   1.       FROM pg_index, pg_class   1.      WHERE pg_class.relname='test2'   1.        AND pg_class.oid=pg_index.indrelid   1.        AND indisunique != 't'   1.        AND indisprimary != 't'   1. );   1.    1. SELECT t.relname, a.attname, a.attnum   1.      FROM pg_index c   1. LEFT JOIN pg_class t   1.        ON c.indrelid  = t.oid   1. LEFT JOIN pg_attribute a   1.        ON a.attrelid = t.oid   1.       AND a.attnum = ANY(indkey)   1.     WHERE t.relname = 'test2'   1.       AND a.attnum = 6; -- this is the index key   列出表的约束  Sql代码  [](# "收藏这段代码") 1. SELECT c.conname AS constraint_name,   1.           CASE c.contype   1.             WHEN 'c' THEN 'CHECK'   1.             WHEN 'f' THEN 'FOREIGN KEY'   1.             WHEN 'p' THEN 'PRIMARY KEY'   1.             WHEN 'u' THEN 'UNIQUE'   1.           END AS "constraint_type",   1.           CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,   1.           CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,   1.           t.relname AS table_name,   1.           array_to_string(c.conkey, ' ') AS constraint_key,   1.           CASE confupdtype   1.             WHEN 'a' THEN 'NO ACTION'   1.             WHEN 'r' THEN 'RESTRICT'   1.             WHEN 'c' THEN 'CASCADE'   1.             WHEN 'n' THEN 'SET NULL'   1.             WHEN 'd' THEN 'SET DEFAULT'   1.           END AS on_update,   1.           CASE confdeltype   1.             WHEN 'a' THEN 'NO ACTION'   1.             WHEN 'r' THEN 'RESTRICT'   1.             WHEN 'c' THEN 'CASCADE'   1.             WHEN 'n' THEN 'SET NULL'   1.             WHEN 'd' THEN 'SET DEFAULT'   1.           END AS on_delete,   1.           CASE confmatchtype   1.             WHEN 'u' THEN 'UNSPECIFIED'   1.             WHEN 'f' THEN 'FULL'   1.             WHEN 'p' THEN 'PARTIAL'   1.           END AS match_type,   1.           t2.relname AS references_table,   1.           array_to_string(c.confkey, ' ') AS fk_constraint_key   1.      FROM pg_constraint c   1. LEFT JOIN pg_class t  ON c.conrelid  = t.oid   1. LEFT JOIN pg_class t2 ON c.confrelid = t2.oid   1.     WHERE t.relname = 'testconstraints2'   1.      AND c.conname = 'testconstraints_id_fk';   1.         1. -- with INFORMATION_SCHEMA:   1.     1.    SELECT tc.constraint_name,   1.           tc.constraint_type,   1.           tc.table_name,   1.           kcu.column_name,   1.       tc.is_deferrable,   1.           tc.initially_deferred,   1.           rc.match_option AS match_type,   1.           rc.update_rule AS on_update,   1.           rc.delete_rule AS on_delete,   1.           ccu.table_name AS references_table,   1.           ccu.column_name AS references_field   1.      FROM information_schema.table_constraints tc   1. LEFT JOIN information_schema.key_column_usage kcu   1.        ON tc.constraint_catalog = kcu.constraint_catalog   1.       AND tc.constraint_schema = kcu.constraint_schema   1.       AND tc.constraint_name = kcu.constraint_name   1. LEFT JOIN information_schema.referential_constraints rc   1.        ON tc.constraint_catalog = rc.constraint_catalog   1.       AND tc.constraint_schema = rc.constraint_schema   1.       AND tc.constraint_name = rc.constraint_name   1. LEFT JOIN information_schema.constraint_column_usage ccu   1.        ON rc.unique_constraint_catalog = ccu.constraint_catalog   1.       AND rc.unique_constraint_schema = ccu.constraint_schema   1.       AND rc.unique_constraint_name = ccu.constraint_name   1.     WHERE tc.table_name = 'testconstraints2'   1.       AND tc.constraint_name = 'testconstraints_id_fk';   列出所有序列  Sql代码  [](# "收藏这段代码") 1. SELECT relname   1.   FROM pg_class   1.  WHERE relkind = 'S'   1.    AND relnamespace IN (   1.         SELECT oid   1.           FROM pg_namespace   1.          WHERE nspname NOT LIKE 'pg_%'   1.            AND nspname != 'information_schema'   1. );   列出所有触发器  Sql代码  [](# "收藏这段代码") 1. SELECT trg.tgname AS trigger_name   1.   FROM pg_trigger trg, pg_class tbl   1.  WHERE trg.tgrelid = tbl.oid   1.    AND tbl.relname !~ '^pg_';   1. -- or   1. SELECT tgname AS trigger_name   1.   FROM pg_trigger   1.  WHERE tgname !~ '^pg_';   1.     1. -- with INFORMATION_SCHEMA:   1.     1. SELECT DISTINCT trigger_name   1.   FROM information_schema.triggers   1.  WHERE trigger_schema NOT IN   1.        ('pg_catalog', 'information_schema');   1.    1. SELECT trg.tgname AS trigger_name   1.   FROM pg_trigger trg, pg_class tbl   1.  WHERE trg.tgrelid = tbl.oid   1.    AND tbl.relname = 'newtable';   1.     1. -- with INFORMATION_SCHEMA:   1.     1. SELECT DISTINCT trigger_name   1.   FROM information_schema.triggers   1.  WHERE event_object_table = 'newtable'   1.    AND trigger_schema NOT IN   1.        ('pg_catalog', 'information_schema');   列出所有触发器的信息  Sql代码  [](# "收藏这段代码") 1. SELECT trg.tgname AS trigger_name,   1.        tbl.relname AS table_name,   1.        p.proname AS function_name,   1.        CASE trg.tgtype & cast(2 as int2)   1.          WHEN 0 THEN 'AFTER'   1.          ELSE 'BEFORE'   1.        END AS trigger_type,   1.        CASE trg.tgtype & cast(28 as int2)   1.          WHEN 16 THEN 'UPDATE'   1.          WHEN  8 THEN 'DELETE'   1.          WHEN  4 THEN 'INSERT'   1.          WHEN 20 THEN 'INSERT, UPDATE'   1.          WHEN 28 THEN 'INSERT, UPDATE, DELETE'   1.          WHEN 24 THEN 'UPDATE, DELETE'   1.          WHEN 12 THEN 'INSERT, DELETE'   1.        END AS trigger_event,   1.        CASE trg.tgtype & cast(1 as int2)   1.          WHEN 0 THEN 'STATEMENT'   1.          ELSE 'ROW'   1.        END AS action_orientation   1.   FROM pg_trigger trg,   1.        pg_class tbl,   1.        pg_proc p   1.  WHERE trg.tgrelid = tbl.oid   1.    AND trg.tgfoid = p.oid   1.    AND tbl.relname !~ '^pg_';   1.     1. -- with INFORMATION_SCHEMA:   1.     1. SELECT *   1.   FROM information_schema.triggers   1.  WHERE trigger_schema NOT IN   1.        ('pg_catalog', 'information_schema');   列出所有函数  Sql代码  [](# "收藏这段代码") 1. SELECT proname   1.   FROM pg_proc pr,   1.        pg_type tp   1.  WHERE tp.oid = pr.prorettype   1.    AND pr.proisagg = FALSE   1.    AND tp.typname <> 'trigger'   1.    AND pr.pronamespace IN (   1.        SELECT oid   1.          FROM pg_namespace   1.         WHERE nspname NOT LIKE 'pg_%'   1.           AND nspname != 'information_schema'   1. );   1.     1. -- with INFORMATION_SCHEMA:   1.     1. SELECT routine_name   1.   FROM information_schema.routines   1.  WHERE specific_schema NOT IN   1.        ('pg_catalog', 'information_schema')   1.    AND type_udt_name != 'trigger';   Albe Laurenz sent me the following function that is even more informative: for a function name and schema, it selects the position in the argument list, the direction, the name and the data-type of each argument. This procedure requires PostgreSQL 8.1 or later.  Sql代码  [](# "收藏这段代码") 1. CREATE OR REPLACE FUNCTION public.function_args(   1.   IN funcname character varying,   1.   IN schema character varying,   1.   OUT pos integer,   1.   OUT direction character,   1.   OUT argname character varying,   1.   OUT datatype character varying)   1. RETURNS SETOF RECORD AS $$DECLARE   1.   rettype character varying;   1.   argtypes oidvector;   1.   allargtypes oid[];   1.   argmodes "char"[];   1.   argnames text[];   1.   mini integer;   1.   maxi integer;   1. BEGIN   1.   /* get object ID of function */   1.   SELECT INTO rettype, argtypes, allargtypes, argmodes, argnames   1.          CASE   1.          WHEN pg_proc.proretset   1.          THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)   1.          ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END,   1.          pg_proc.proargtypes,   1.          pg_proc.proallargtypes,   1.          pg_proc.proargmodes,   1.          pg_proc.proargnames   1.     FROM pg_catalog.pg_proc   1.          JOIN pg_catalog.pg_namespace   1.          ON (pg_proc.pronamespace = pg_namespace.oid)   1.    WHERE pg_proc.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype   1.      AND (pg_proc.proargtypes[0] IS NULL   1.       OR pg_proc.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)   1.      AND NOT pg_proc.proisagg   1.      AND pg_proc.proname = funcname   1.      AND pg_namespace.nspname = schema   1.      AND pg_catalog.pg_function_is_visible(pg_proc.oid);   1.     1.   /* bail out if not found */   1.   IF NOT FOUND THEN   1.     RETURN;   1.   END IF;   1.     1.   /* return a row for the return value */   1.   pos = 0;   1.   direction = 'o'::char;   1.   argname = 'RETURN VALUE';   1.   datatype = rettype;   1.   RETURN NEXT;   1.     1.   /* unfortunately allargtypes is NULL if there are no OUT parameters */   1.   IF allargtypes IS NULL THEN   1.     mini = array_lower(argtypes, 1); maxi = array_upper(argtypes, 1);   1.   ELSE   1.     mini = array_lower(allargtypes, 1); maxi = array_upper(allargtypes, 1);   1.   END IF;   1.   IF maxi < mini THEN RETURN; END IF;   1.     1.   /* loop all the arguments */   1.   FOR i IN mini .. maxi LOOP   1.     pos = i - mini + 1;   1.     IF argnames IS NULL THEN   1.       argname = NULL;   1.     ELSE   1.       argname = argnames[pos];   1.     END IF;   1.     IF allargtypes IS NULL THEN   1.       direction = 'i'::char;   1.       datatype = pg_catalog.format_type(argtypes[i], NULL);   1.     ELSE   1.       direction = argmodes[i];   1.       datatype = pg_catalog.format_type(allargtypes[i], NULL);   1.     END IF;   1.     RETURN NEXT;   1.   END LOOP;   1.     1.   RETURN;   1. END;$$ LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER;   1. COMMENT ON FUNCTION public.function_args(character varying, character   1. varying)   1. IS $$For a function name and schema, this procedure selects for each   1. argument the following data:   1. - position in the argument list (0 for the return value)   1. - direction 'i', 'o', or 'b'   1. - name (NULL if not defined)   1. - data type$$;   列出所有存储过程  Sql代码  [](# "收藏这段代码") 1. SELECT p.proname AS procedure_name,   1.           p.pronargs AS num_args,   1.           t1.typname AS return_type,   1.           a.rolname AS procedure_owner,   1.           l.lanname AS language_type,   1.           p.proargtypes AS argument_types_oids,   1.           prosrc AS body   1.      FROM pg_proc p   1. LEFT JOIN pg_type t1 ON p.prorettype=t1.oid      1. LEFT JOIN pg_authid a ON p.proowner=a.oid    1. LEFT JOIN pg_language l ON p.prolang=l.oid   1.     WHERE proname = :PROCEDURE_NAME;