淘先锋技术网

首页 1 2 3 4 5 6 7
  1. -- sample data to test PostgreSQL INFORMATION_SCHEMA   
  2.     
  3. -- TABLE TEST   
  4. CREATE TABLE TEST (   
  5.   TEST_NAME CHAR(30) NOT NULL,   
  6.   TEST_ID INTEGER DEFAULT '0' NOT NULL,   
  7.   TEST_DATE TIMESTAMP NOT NULL  
  8. );   
  9. ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);   
  10.     
  11. -- TABLE TEST2 with some CONSTRAINTs and an INDEX   
  12. CREATE TABLE TEST2 (   
  13.   ID INTEGER NOT NULL,   
  14.   FIELD1 INTEGER,   
  15.   FIELD2 CHAR(15),   
  16.   FIELD3 VARCHAR(50),   
  17.   FIELD4 INTEGER,   
  18.   FIELD5 INTEGER,   
  19.   ID2 INTEGER NOT NULL  
  20. );   
  21. ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID2);   
  22. ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD1ID_IDX UNIQUE (ID, FIELD1);   
  23. ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD4_IDX UNIQUE (FIELD4);   
  24. CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);   
  25.     
  26. -- TABLE NUMBERS   
  27. CREATE TABLE NUMBERS (   
  28.   NUMBER INTEGER DEFAULT '0' NOT NULL,   
  29.   EN CHAR(100) NOT NULL,   
  30.   FR CHAR(100) NOT NULL  
  31. );   
  32.     
  33. -- TABLE NEWTABLE   
  34. CREATE TABLE NEWTABLE (   
  35.   ID INT DEFAULT 0 NOT NULL,   
  36.   SOMENAME VARCHAR (12),   
  37.   SOMEDATE TIMESTAMP NOT NULL  
  38. );   
  39. ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);   
  40. CREATE SEQUENCE NEWTABLE_SEQ INCREMENT 1 START 1;   
  41.     
  42. -- VIEW on TEST   
  43. CREATE VIEW "testview"(   
  44.   TEST_NAME,   
  45.   TEST_ID,   
  46.   TEST_DATE   
  47. AS  
  48. SELECT *   
  49. FROM TEST   
  50. WHERE TEST_NAME LIKE 't%';   
  51.     
  52. -- VIEW on NUMBERS   
  53. CREATE VIEW "numbersview"(   
  54.   NUMBER,   
  55.   TRANS_EN,   
  56.   TRANS_FR   
  57. AS  
  58. SELECT *   
  59. FROM NUMBERS   
  60. WHERE NUMBER > 100;   
  61.     
  62. -- TRIGGER on NEWTABLE   
  63. CREATE FUNCTION add_stamp() RETURNS OPAQUE AS '   
  64.     BEGIN  
  65.       IF (NEW.somedate IS NULL OR NEW.somedate = 0) THEN  
  66.         NEW.somedate := CURRENT_TIMESTAMP;   
  67.         RETURN NEW;   
  68.       END IF;   
  69.     END;   
  70. ' LANGUAGE 'plpgsql';   
  71.     
  72. CREATE TRIGGER ADDCURRENTDATE   
  73. BEFORE INSERT OR UPDATE  
  74. ON newtable FOR EACH ROW   
  75.   EXECUTE PROCEDURE add_stamp();   
  76.     
  77. -- TABLEs for testing CONSTRAINTs   
  78. CREATE TABLE testconstraints (   
  79.   someid integer NOT NULL,   
  80.   somename character varying(10) NOT NULL,   
  81.   CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)   
  82. );   
  83. CREATE TABLE testconstraints2 (   
  84.   ext_id integer NOT NULL,   
  85.   modified date,   
  86.   uniquefield character varying(10) NOT NULL,   
  87.   usraction integer NOT NULL,   
  88.   CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)   
  89.       REFERENCES testconstraints (someid) MATCH SIMPLE   
  90.       ON UPDATE CASCADE ON DELETE CASCADE,   
  91.   CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),   
  92.   CONSTRAINT uniquefld_idx UNIQUE (uniquefield)   
  93. );  
-- sample data to test PostgreSQL INFORMATION_SCHEMA
 
-- TABLE TEST
CREATE TABLE TEST (
  TEST_NAME CHAR(30) NOT NULL,
  TEST_ID INTEGER DEFAULT '0' NOT NULL,
  TEST_DATE TIMESTAMP NOT NULL
);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);
 
-- TABLE TEST2 with some CONSTRAINTs and an INDEX
CREATE TABLE TEST2 (
  ID INTEGER NOT NULL,
  FIELD1 INTEGER,
  FIELD2 CHAR(15),
  FIELD3 VARCHAR(50),
  FIELD4 INTEGER,
  FIELD5 INTEGER,
  ID2 INTEGER NOT NULL
);
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID2);
ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD1ID_IDX UNIQUE (ID, FIELD1);
ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD4_IDX UNIQUE (FIELD4);
CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);
 
-- TABLE NUMBERS
CREATE TABLE NUMBERS (
  NUMBER INTEGER DEFAULT '0' NOT NULL,
  EN CHAR(100) NOT NULL,
  FR CHAR(100) NOT NULL
);
 
-- TABLE NEWTABLE
CREATE TABLE NEWTABLE (
  ID INT DEFAULT 0 NOT NULL,
  SOMENAME VARCHAR (12),
  SOMEDATE TIMESTAMP NOT NULL
);
ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);
CREATE SEQUENCE NEWTABLE_SEQ INCREMENT 1 START 1;
 
-- VIEW on TEST
CREATE VIEW "testview"(
  TEST_NAME,
  TEST_ID,
  TEST_DATE
) AS
SELECT *
FROM TEST
WHERE TEST_NAME LIKE 't%';
 
-- VIEW on NUMBERS
CREATE VIEW "numbersview"(
  NUMBER,
  TRANS_EN,
  TRANS_FR
) AS
SELECT *
FROM NUMBERS
WHERE NUMBER > 100;
 
-- TRIGGER on NEWTABLE
CREATE FUNCTION add_stamp() RETURNS OPAQUE AS '
    BEGIN
      IF (NEW.somedate IS NULL OR NEW.somedate = 0) THEN
        NEW.somedate := CURRENT_TIMESTAMP;
        RETURN NEW;
      END IF;
    END;
' LANGUAGE 'plpgsql';
 
CREATE TRIGGER ADDCURRENTDATE
BEFORE INSERT OR UPDATE
ON newtable FOR EACH ROW
  EXECUTE PROCEDURE add_stamp();
 
-- TABLEs for testing CONSTRAINTs
CREATE TABLE testconstraints (
  someid integer NOT NULL,
  somename character varying(10) NOT NULL,
  CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)
);
CREATE TABLE testconstraints2 (
  ext_id integer NOT NULL,
  modified date,
  uniquefield character varying(10) NOT NULL,
  usraction integer NOT NULL,
  CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)
      REFERENCES testconstraints (someid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),
  CONSTRAINT uniquefld_idx UNIQUE (uniquefield)
);

列出所有数据库中的表名
SELECT relname   
  1.   FROM pg_class   
  2.  WHERE relname !~ '^(pg_|sql_)'  
  3.    AND relkind = 'r';   
  4. <!--   
  5. SELECT c.relname AS "Name"  
  6.   FROM pg_class c, pg_user u   
  7.  WHERE c.relowner = u.usesysid   
  8.    AND c.relkind = 'r'  
  9.    AND NOT EXISTS (   
  10.        SELECT 1   
  11.          FROM pg_views   
  12.         WHERE viewname = c.relname   
  13.        )   
  14.    AND c.relname !~ '^(pg_|sql_)'  
  15. UNION  
  16. SELECT c.relname AS "Name"  
  17.   FROM pg_class c   
  18.  WHERE c.relkind = 'r'  
  19.    AND NOT EXISTS (   
  20.        SELECT 1   
  21.          FROM pg_views   
  22.         WHERE viewname = c.relname   
  23.        )   
  24.    AND NOT EXISTS (   
  25.        SELECT 1   
  26.          FROM pg_user   
  27.         WHERE usesysid = c.relowner   
  28.        )   
  29.    AND c.relname !~ '^pg_';   
  30. -->    
  31. -- using INFORMATION_SCHEMA:   
  32.     
  33. SELECT table_name   
  34.   FROM information_schema.tables   
  35.  WHERE table_type = 'BASE TABLE'  
  36.    AND table_schema NOT IN  
  37.        ('pg_catalog''information_schema');  
SELECT relname
  FROM pg_class
 WHERE relname !~ '^(pg_|sql_)'
   AND relkind = 'r';
<!--
SELECT c.relname AS "Name"
  FROM pg_class c, pg_user u
 WHERE c.relowner = u.usesysid
   AND c.relkind = 'r'
   AND NOT EXISTS (
       SELECT 1
         FROM pg_views
        WHERE viewname = c.relname
       )
   AND c.relname !~ '^(pg_|sql_)'
UNION
SELECT c.relname AS "Name"
  FROM pg_class c
 WHERE c.relkind = 'r'
   AND NOT EXISTS (
       SELECT 1
         FROM pg_views
        WHERE viewname = c.relname
       )
   AND NOT EXISTS (
       SELECT 1
         FROM pg_user
        WHERE usesysid = c.relowner
       )
   AND c.relname !~ '^pg_';
--> 
-- using INFORMATION_SCHEMA:
 
SELECT table_name
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
   AND table_schema NOT IN
       ('pg_catalog', 'information_schema');

列出所有视图
Sql代码
-- with postgresql 7.2:   
  1.     
  2. SELECT viewname   
  3.   FROM pg_views   
  4.  WHERE viewname !~ '^pg_';   
  5.     
  6. -- with postgresql 7.4 and later:   
  7.     
  8. SELECT viewname   
  9.   FROM pg_views   
  10.  WHERE schemaname NOT IN  
  11.        ('pg_catalog''information_schema')   
  12.    AND viewname !~ '^pg_';   
  13.     
  14. -- using INFORMATION_SCHEMA:   
  15.     
  16. SELECT table_name   
  17.   FROM information_schema.tables   
  18.  WHERE table_type = 'VIEW'  
  19.    AND table_schema NOT IN  
  20.        ('pg_catalog''information_schema')   
  21.    AND table_name !~ '^pg_';   
  22.     
  23. -- or   
  24.     
  25. SELECT table_name   
  26.   FROM information_schema.views   
  27.  WHERE table_schema NOT IN ('pg_catalog''information_schema')   
  28.    AND table_name !~ '^pg_';   
  29. <!--   
  30. # show only the VIEWs referencing a given table  
  31.     
  32.       SELECT viewname   
  33.         FROM pg_views   
  34. NATURAL JOIN pg_tables   
  35.        WHERE tablename ='test';   
  36. -->  
-- with postgresql 7.2:
 
SELECT viewname
  FROM pg_views
 WHERE viewname !~ '^pg_';
 
-- with postgresql 7.4 and later:
 
SELECT viewname
  FROM pg_views
 WHERE schemaname NOT IN
       ('pg_catalog', 'information_schema')
   AND viewname !~ '^pg_';
 
-- using INFORMATION_SCHEMA:
 
SELECT table_name
  FROM information_schema.tables
 WHERE table_type = 'VIEW'
   AND table_schema NOT IN
       ('pg_catalog', 'information_schema')
   AND table_name !~ '^pg_';
 
-- or
 
SELECT table_name
  FROM information_schema.views
 WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
   AND table_name !~ '^pg_';
<!--
# show only the VIEWs referencing a given table
 
      SELECT viewname
        FROM pg_views
NATURAL JOIN pg_tables
       WHERE tablename ='test';
-->

列出所有用户
SELECT usename   
  1.   FROM pg_user;  
SELECT usename
  FROM pg_user;

列出某表中得所有字段
SELECT a.attname   
  1.   FROM pg_class c, pg_attribute a, pg_type t   
  2.  WHERE c.relname = 'test2'  
  3.    AND a.attnum > 0   
  4.    AND a.attrelid = c.oid   
  5.    AND a.atttypid = t.oid   
  6.     
  7. -- with INFORMATION_SCHEMA:   
  8.     
  9. SELECT column_name   
  10.   FROM information_schema.columns   
  11.  WHERE table_name = 'test2';  
SELECT a.attname
  FROM pg_class c, pg_attribute a, pg_type t
 WHERE c.relname = 'test2'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 
-- with INFORMATION_SCHEMA:
 
SELECT column_name
  FROM information_schema.columns
 WHERE table_name = 'test2';

列出某表字段的信息
SELECT a.attnum AS ordinal_position,   
  1.          a.attname AS column_name,   
  2.          t.typname AS data_type,   
  3.          a.attlen AS character_maximum_length,   
  4.          a.atttypmod AS modifier,   
  5.          a.attnotnull AS notnull,   
  6.          a.atthasdef AS hasdefault   
  7.     FROM pg_class c,   
  8.          pg_attribute a,   
  9.          pg_type t   
  10.    WHERE c.relname = 'test2'  
  11.      AND a.attnum > 0   
  12.      AND a.attrelid = c.oid   
  13.      AND a.atttypid = t.oid   
  14. ORDER BY a.attnum;   
  15.     
  16. -- with INFORMATION_SCHEMA:   
  17.     
  18.   SELECT ordinal_position,   
  19.          column_name,   
  20.          data_type,   
  21.          column_default,   
  22.          is_nullable,   
  23.          character_maximum_length,   
  24.          numeric_precision   
  25.     FROM information_schema.columns   
  26.    WHERE table_name = 'test2'  
  27. ORDER BY ordinal_position;  
SELECT a.attnum AS ordinal_position,
         a.attname AS column_name,
         t.typname AS data_type,
         a.attlen AS character_maximum_length,
         a.atttypmod AS modifier,
         a.attnotnull AS notnull,
         a.atthasdef AS hasdefault
    FROM pg_class c,
         pg_attribute a,
         pg_type t
   WHERE c.relname = 'test2'
     AND a.attnum > 0
     AND a.attrelid = c.oid
     AND a.atttypid = t.oid
ORDER BY a.attnum;
 
-- with INFORMATION_SCHEMA:
 
  SELECT ordinal_position,
         column_name,
         data_type,
         column_default,
         is_nullable,
         character_maximum_length,
         numeric_precision
    FROM information_schema.columns
   WHERE table_name = 'test2'
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
SELECT relname   
  1.   FROM pg_class   
  2.  WHERE oid IN (   
  3.     SELECT indexrelid   
  4.       FROM pg_index, pg_class   
  5.      WHERE pg_class.relname='test2'  
  6.        AND pg_class.oid=pg_index.indrelid   
  7.        AND indisunique != 't'  
  8.        AND indisprimary != 't'  
  9.        );  
SELECT relname
  FROM pg_class
 WHERE oid IN (
    SELECT indexrelid
      FROM pg_index, pg_class
     WHERE pg_class.relname='test2'
       AND pg_class.oid=pg_index.indrelid
       AND indisunique != 't'
       AND indisprimary != 't'
       );


列出表的索引信息
SELECT relname, indkey   
  1.   FROM pg_class, pg_index   
  2.  WHERE pg_class.oid = pg_index.indexrelid   
  3.    AND pg_class.oid IN (   
  4.     SELECT indexrelid   
  5.       FROM pg_index, pg_class   
  6.      WHERE pg_class.relname='test2'  
  7.        AND pg_class.oid=pg_index.indrelid   
  8.        AND indisunique != 't'  
  9.        AND indisprimary != 't'  
  10. );   
  11.   
  12. SELECT t.relname, a.attname, a.attnum   
  13.      FROM pg_index c   
  14. LEFT JOIN pg_class t   
  15.        ON c.indrelid  = t.oid   
  16. LEFT JOIN pg_attribute a   
  17.        ON a.attrelid = t.oid   
  18.       AND a.attnum = ANY(indkey)   
  19.     WHERE t.relname = 'test2'  
  20.       AND a.attnum = 6; -- this is the index key  
SELECT relname, indkey
  FROM pg_class, pg_index
 WHERE pg_class.oid = pg_index.indexrelid
   AND pg_class.oid IN (
    SELECT indexrelid
      FROM pg_index, pg_class
     WHERE pg_class.relname='test2'
       AND pg_class.oid=pg_index.indrelid
       AND indisunique != 't'
       AND indisprimary != 't'
);

SELECT t.relname, a.attname, a.attnum
     FROM pg_index c
LEFT JOIN pg_class t
       ON c.indrelid  = t.oid
LEFT JOIN pg_attribute a
       ON a.attrelid = t.oid
      AND a.attnum = ANY(indkey)
    WHERE t.relname = 'test2'
      AND a.attnum = 6; -- this is the index key

列出表的约束
SELECT c.conname AS constraint_name,   
  1.           CASE c.contype   
  2.             WHEN 'c' THEN 'CHECK'  
  3.             WHEN 'f' THEN 'FOREIGN KEY'  
  4.             WHEN 'p' THEN 'PRIMARY KEY'  
  5.             WHEN 'u' THEN 'UNIQUE'  
  6.           END AS "constraint_type",   
  7.           CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,   
  8.           CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,   
  9.           t.relname AS table_name,   
  10.           array_to_string(c.conkey, ' 'AS constraint_key,   
  11.           CASE confupdtype   
  12.             WHEN 'a' THEN 'NO ACTION'  
  13.             WHEN 'r' THEN 'RESTRICT'  
  14.             WHEN 'c' THEN 'CASCADE'  
  15.             WHEN 'n' THEN 'SET NULL'  
  16.             WHEN 'd' THEN 'SET DEFAULT'  
  17.           END AS on_update,   
  18.           CASE confdeltype   
  19.             WHEN 'a' THEN 'NO ACTION'  
  20.             WHEN 'r' THEN 'RESTRICT'  
  21.             WHEN 'c' THEN 'CASCADE'  
  22.             WHEN 'n' THEN 'SET NULL'  
  23.             WHEN 'd' THEN 'SET DEFAULT'  
  24.           END AS on_delete,   
  25.           CASE confmatchtype   
  26.             WHEN 'u' THEN 'UNSPECIFIED'  
  27.             WHEN 'f' THEN 'FULL'  
  28.             WHEN 'p' THEN 'PARTIAL'  
  29.           END AS match_type,   
  30.           t2.relname AS references_table,   
  31.           array_to_string(c.confkey, ' 'AS fk_constraint_key   
  32.      FROM pg_constraint c   
  33. LEFT JOIN pg_class t  ON c.conrelid  = t.oid   
  34. LEFT JOIN pg_class t2 ON c.confrelid = t2.oid   
  35.     WHERE t.relname = 'testconstraints2'  
  36.      AND c.conname = 'testconstraints_id_fk';   
  37.         
  38. -- with INFORMATION_SCHEMA:   
  39.     
  40.    SELECT tc.constraint_name,   
  41.           tc.constraint_type,   
  42.           tc.table_name,   
  43.           kcu.column_name,   
  44.       tc.is_deferrable,   
  45.           tc.initially_deferred,   
  46.           rc.match_option AS match_type,   
  47.           rc.update_rule AS on_update,   
  48.           rc.delete_rule AS on_delete,   
  49.           ccu.table_name AS references_table,   
  50.           ccu.column_name AS references_field   
  51.      FROM information_schema.table_constraints tc   
  52. LEFT JOIN information_schema.key_column_usage kcu   
  53.        ON tc.constraint_catalog = kcu.constraint_catalog   
  54.       AND tc.constraint_schema = kcu.constraint_schema   
  55.       AND tc.constraint_name = kcu.constraint_name   
  56. LEFT JOIN information_schema.referential_constraints rc   
  57.        ON tc.constraint_catalog = rc.constraint_catalog   
  58.       AND tc.constraint_schema = rc.constraint_schema   
  59.       AND tc.constraint_name = rc.constraint_name   
  60. LEFT JOIN information_schema.constraint_column_usage ccu   
  61.        ON rc.unique_constraint_catalog = ccu.constraint_catalog   
  62.       AND rc.unique_constraint_schema = ccu.constraint_schema   
  63.       AND rc.unique_constraint_name = ccu.constraint_name   
  64.     WHERE tc.table_name = 'testconstraints2'  
  65.       AND tc.constraint_name = 'testconstraints_id_fk';  
SELECT c.conname AS constraint_name,
          CASE c.contype
            WHEN 'c' THEN 'CHECK'
            WHEN 'f' THEN 'FOREIGN KEY'
            WHEN 'p' THEN 'PRIMARY KEY'
            WHEN 'u' THEN 'UNIQUE'
          END AS "constraint_type",
          CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,
          CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,
          t.relname AS table_name,
          array_to_string(c.conkey, ' ') AS constraint_key,
          CASE confupdtype
            WHEN 'a' THEN 'NO ACTION'
            WHEN 'r' THEN 'RESTRICT'
            WHEN 'c' THEN 'CASCADE'
            WHEN 'n' THEN 'SET NULL'
            WHEN 'd' THEN 'SET DEFAULT'
          END AS on_update,
          CASE confdeltype
            WHEN 'a' THEN 'NO ACTION'
            WHEN 'r' THEN 'RESTRICT'
            WHEN 'c' THEN 'CASCADE'
            WHEN 'n' THEN 'SET NULL'
            WHEN 'd' THEN 'SET DEFAULT'
          END AS on_delete,
          CASE confmatchtype
            WHEN 'u' THEN 'UNSPECIFIED'
            WHEN 'f' THEN 'FULL'
            WHEN 'p' THEN 'PARTIAL'
          END AS match_type,
          t2.relname AS references_table,
          array_to_string(c.confkey, ' ') AS fk_constraint_key
     FROM pg_constraint c
LEFT JOIN pg_class t  ON c.conrelid  = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
    WHERE t.relname = 'testconstraints2'
     AND c.conname = 'testconstraints_id_fk';
     
-- with INFORMATION_SCHEMA:
 
   SELECT tc.constraint_name,
          tc.constraint_type,
          tc.table_name,
          kcu.column_name,
	  tc.is_deferrable,
          tc.initially_deferred,
          rc.match_option AS match_type,
          rc.update_rule AS on_update,
          rc.delete_rule AS on_delete,
          ccu.table_name AS references_table,
          ccu.column_name AS references_field
     FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
       ON tc.constraint_catalog = kcu.constraint_catalog
      AND tc.constraint_schema = kcu.constraint_schema
      AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
       ON tc.constraint_catalog = rc.constraint_catalog
      AND tc.constraint_schema = rc.constraint_schema
      AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
       ON rc.unique_constraint_catalog = ccu.constraint_catalog
      AND rc.unique_constraint_schema = ccu.constraint_schema
      AND rc.unique_constraint_name = ccu.constraint_name
    WHERE tc.table_name = 'testconstraints2'
      AND tc.constraint_name = 'testconstraints_id_fk';


列出所有序列
SELECT relname   
  1.   FROM pg_class   
  2.  WHERE relkind = 'S'  
  3.    AND relnamespace IN (   
  4.         SELECT oid   
  5.           FROM pg_namespace   
  6.          WHERE nspname NOT LIKE 'pg_%'  
  7.            AND nspname != 'information_schema'  
  8. );  
SELECT relname
  FROM pg_class
 WHERE relkind = 'S'
   AND relnamespace IN (
        SELECT oid
          FROM pg_namespace
         WHERE nspname NOT LIKE 'pg_%'
           AND nspname != 'information_schema'
);



列出所有触发器

SELECT trg.tgname AS trigger_name   

  1.   FROM pg_trigger trg, pg_class tbl   
  2.  WHERE trg.tgrelid = tbl.oid   
  3.    AND tbl.relname !~ '^pg_';   
  4. -- or   
  5. SELECT tgname AS trigger_name   
  6.   FROM pg_trigger   
  7.  WHERE tgname !~ '^pg_';   
  8.     
  9. -- with INFORMATION_SCHEMA:   
  10.     
  11. SELECT DISTINCT trigger_name   
  12.   FROM information_schema.triggers   
  13.  WHERE trigger_schema NOT IN  
  14.        ('pg_catalog''information_schema');   
  15.   
  16. SELECT trg.tgname AS trigger_name   
  17.   FROM pg_trigger trg, pg_class tbl   
  18.  WHERE trg.tgrelid = tbl.oid   
  19.    AND tbl.relname = 'newtable';   
  20.     
  21. -- with INFORMATION_SCHEMA:   
  22.     
  23. SELECT DISTINCT trigger_name   
  24.   FROM information_schema.triggers   
  25.  WHERE event_object_table = 'newtable'  
  26.    AND trigger_schema NOT IN  
  27.        ('pg_catalog''information_schema');  
SELECT trg.tgname AS trigger_name
  FROM pg_trigger trg, pg_class tbl
 WHERE trg.tgrelid = tbl.oid
   AND tbl.relname !~ '^pg_';
-- or
SELECT tgname AS trigger_name
  FROM pg_trigger
 WHERE tgname !~ '^pg_';
 
-- with INFORMATION_SCHEMA:
 
SELECT DISTINCT trigger_name
  FROM information_schema.triggers
 WHERE trigger_schema NOT IN
       ('pg_catalog', 'information_schema');

SELECT trg.tgname AS trigger_name
  FROM pg_trigger trg, pg_class tbl
 WHERE trg.tgrelid = tbl.oid
   AND tbl.relname = 'newtable';
 
-- with INFORMATION_SCHEMA:
 
SELECT DISTINCT trigger_name
  FROM information_schema.triggers
 WHERE event_object_table = 'newtable'
   AND trigger_schema NOT IN
       ('pg_catalog', 'information_schema');

列出所有触发器的信息
SELECT trg.tgname AS trigger_name,   
  1.        tbl.relname AS table_name,   
  2.        p.proname AS function_name,   
  3.        CASE trg.tgtype & cast(2 as int2)   
  4.          WHEN 0 THEN 'AFTER'  
  5.          ELSE 'BEFORE'  
  6.        END AS trigger_type,   
  7.        CASE trg.tgtype & cast(28 as int2)   
  8.          WHEN 16 THEN 'UPDATE'  
  9.          WHEN  8 THEN 'DELETE'  
  10.          WHEN  4 THEN 'INSERT'  
  11.          WHEN 20 THEN 'INSERT, UPDATE'  
  12.          WHEN 28 THEN 'INSERT, UPDATE, DELETE'  
  13.          WHEN 24 THEN 'UPDATE, DELETE'  
  14.          WHEN 12 THEN 'INSERT, DELETE'  
  15.        END AS trigger_event,   
  16.        CASE trg.tgtype & cast(1 as int2)   
  17.          WHEN 0 THEN 'STATEMENT'  
  18.          ELSE 'ROW'  
  19.        END AS action_orientation   
  20.   FROM pg_trigger trg,   
  21.        pg_class tbl,   
  22.        pg_proc p   
  23.  WHERE trg.tgrelid = tbl.oid   
  24.    AND trg.tgfoid = p.oid   
  25.    AND tbl.relname !~ '^pg_';   
  26.     
  27. -- with INFORMATION_SCHEMA:   
  28.     
  29. SELECT *   
  30.   FROM information_schema.triggers   
  31.  WHERE trigger_schema NOT IN  
  32.        ('pg_catalog''information_schema');  
SELECT trg.tgname AS trigger_name,
       tbl.relname AS table_name,
       p.proname AS function_name,
       CASE trg.tgtype & cast(2 as int2)
         WHEN 0 THEN 'AFTER'
         ELSE 'BEFORE'
       END AS trigger_type,
       CASE trg.tgtype & cast(28 as int2)
         WHEN 16 THEN 'UPDATE'
         WHEN  8 THEN 'DELETE'
         WHEN  4 THEN 'INSERT'
         WHEN 20 THEN 'INSERT, UPDATE'
         WHEN 28 THEN 'INSERT, UPDATE, DELETE'
         WHEN 24 THEN 'UPDATE, DELETE'
         WHEN 12 THEN 'INSERT, DELETE'
       END AS trigger_event,
       CASE trg.tgtype & cast(1 as int2)
         WHEN 0 THEN 'STATEMENT'
         ELSE 'ROW'
       END AS action_orientation
  FROM pg_trigger trg,
       pg_class tbl,
       pg_proc p
 WHERE trg.tgrelid = tbl.oid
   AND trg.tgfoid = p.oid
   AND tbl.relname !~ '^pg_';
 
-- with INFORMATION_SCHEMA:
 
SELECT *
  FROM information_schema.triggers
 WHERE trigger_schema NOT IN
       ('pg_catalog', 'information_schema');


列出所有函数
SELECT proname   
  1.   FROM pg_proc pr,   
  2.        pg_type tp   
  3.  WHERE tp.oid = pr.prorettype   
  4.    AND pr.proisagg = FALSE  
  5.    AND tp.typname <> 'trigger'  
  6.    AND pr.pronamespace IN (   
  7.        SELECT oid   
  8.          FROM pg_namespace   
  9.         WHERE nspname NOT LIKE 'pg_%'  
  10.           AND nspname != 'information_schema'  
  11. );   
  12.     
  13. -- with INFORMATION_SCHEMA:   
  14.     
  15. SELECT routine_name   
  16.   FROM information_schema.routines   
  17.  WHERE specific_schema NOT IN  
  18.        ('pg_catalog''information_schema')   
  19.    AND type_udt_name != 'trigger';  
SELECT proname
  FROM pg_proc pr,
       pg_type tp
 WHERE tp.oid = pr.prorettype
   AND pr.proisagg = FALSE
   AND tp.typname <> 'trigger'
   AND pr.pronamespace IN (
       SELECT oid
         FROM pg_namespace
        WHERE nspname NOT LIKE 'pg_%'
          AND nspname != 'information_schema'
);
 
-- with INFORMATION_SCHEMA:
 
SELECT routine_name
  FROM information_schema.routines
 WHERE specific_schema NOT IN
       ('pg_catalog', 'information_schema')
   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.

CREATE OR REPLACE FUNCTION public.function_args(   
  1.   IN funcname character varying,   
  2.   IN schema character varying,   
  3.   OUT pos integer,   
  4.   OUT direction character,   
  5.   OUT argname character varying,   
  6.   OUT datatype character varying)   
  7. RETURNS SETOF RECORD AS $$DECLARE  
  8.   rettype character varying;   
  9.   argtypes oidvector;   
  10.   allargtypes oid[];   
  11.   argmodes "char"[];   
  12.   argnames text[];   
  13.   mini integer;   
  14.   maxi integer;   
  15. BEGIN  
  16.      
  17.   SELECT INTO rettype, argtypes, allargtypes, argmodes, argnames   
  18.          CASE  
  19.          WHEN pg_proc.proretset   
  20.          THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)   
  21.          ELSE pg_catalog.format_type(pg_proc.prorettype, NULLEND,   
  22.          pg_proc.proargtypes,   
  23.          pg_proc.proallargtypes,   
  24.          pg_proc.proargmodes,   
  25.          pg_proc.proargnames   
  26.     FROM pg_catalog.pg_proc   
  27.          JOIN pg_catalog.pg_namespace   
  28.          ON (pg_proc.pronamespace = pg_namespace.oid)   
  29.    WHERE pg_proc.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype   
  30.      AND (pg_proc.proargtypes[0] IS NULL  
  31.       OR pg_proc.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)   
  32.      AND NOT pg_proc.proisagg   
  33.      AND pg_proc.proname = funcname   
  34.      AND pg_namespace.nspname = schema  
  35.      AND pg_catalog.pg_function_is_visible(pg_proc.oid);   
  36.     
  37.      
  38.   IF NOT FOUND THEN  
  39.     RETURN;   
  40.   END IF;   
  41.     
  42.      
  43.   pos = 0;   
  44.   direction = 'o'::char;   
  45.   argname = 'RETURN VALUE';   
  46.   datatype = rettype;   
  47.   RETURN NEXT;   
  48.     
  49.      
  50.   IF allargtypes IS NULL THEN  
  51.     mini = array_lower(argtypes, 1); maxi = array_upper(argtypes, 1);   
  52.   ELSE  
  53.     mini = array_lower(allargtypes, 1); maxi = array_upper(allargtypes, 1);   
  54.   END IF;   
  55.   IF maxi < mini THEN RETURNEND IF;   
  56.     
  57.      
  58.   FOR i IN mini .. maxi LOOP   
  59.     pos = i - mini + 1;   
  60.     IF argnames IS NULL THEN  
  61.       argname = NULL;   
  62.     ELSE  
  63.       argname = argnames[pos];   
  64.     END IF;   
  65.     IF allargtypes IS NULL THEN  
  66.       direction = 'i'::char;   
  67.       datatype = pg_catalog.format_type(argtypes[i], NULL);   
  68.     ELSE  
  69.       direction = argmodes[i];   
  70.       datatype = pg_catalog.format_type(allargtypes[i], NULL);   
  71.     END IF;   
  72.     RETURN NEXT;   
  73.   END LOOP;   
  74.     
  75.   RETURN;   
  76. END;$$ LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER;   
  77. COMMENT ON FUNCTION public.function_args(character varyingcharacter  
  78. varying)   
  79. IS $$For a function name and schema, this procedure selects for each   
  80. argument the following data:   
  81. - position in the argument list (0 for the return value)   
  82. - direction 'i''o'or 'b'  
  83. name (NULL if not defined)   
  84. - data type$$;  
CREATE OR REPLACE FUNCTION public.function_args(
  IN funcname character varying,
  IN schema character varying,
  OUT pos integer,
  OUT direction character,
  OUT argname character varying,
  OUT datatype character varying)
RETURNS SETOF RECORD AS $$DECLARE
  rettype character varying;
  argtypes oidvector;
  allargtypes oid[];
  argmodes "char"[];
  argnames text[];
  mini integer;
  maxi integer;
BEGIN
  /* get object ID of function */
  SELECT INTO rettype, argtypes, allargtypes, argmodes, argnames
         CASE
         WHEN pg_proc.proretset
         THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)
         ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END,
         pg_proc.proargtypes,
         pg_proc.proallargtypes,
         pg_proc.proargmodes,
         pg_proc.proargnames
    FROM pg_catalog.pg_proc
         JOIN pg_catalog.pg_namespace
         ON (pg_proc.pronamespace = pg_namespace.oid)
   WHERE pg_proc.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
     AND (pg_proc.proargtypes[0] IS NULL
      OR pg_proc.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)
     AND NOT pg_proc.proisagg
     AND pg_proc.proname = funcname
     AND pg_namespace.nspname = schema
     AND pg_catalog.pg_function_is_visible(pg_proc.oid);
 
  /* bail out if not found */
  IF NOT FOUND THEN
    RETURN;
  END IF;
 
  /* return a row for the return value */
  pos = 0;
  direction = 'o'::char;
  argname = 'RETURN VALUE';
  datatype = rettype;
  RETURN NEXT;
 
  /* unfortunately allargtypes is NULL if there are no OUT parameters */
  IF allargtypes IS NULL THEN
    mini = array_lower(argtypes, 1); maxi = array_upper(argtypes, 1);
  ELSE
    mini = array_lower(allargtypes, 1); maxi = array_upper(allargtypes, 1);
  END IF;
  IF maxi < mini THEN RETURN; END IF;
 
  /* loop all the arguments */
  FOR i IN mini .. maxi LOOP
    pos = i - mini + 1;
    IF argnames IS NULL THEN
      argname = NULL;
    ELSE
      argname = argnames[pos];
    END IF;
    IF allargtypes IS NULL THEN
      direction = 'i'::char;
      datatype = pg_catalog.format_type(argtypes[i], NULL);
    ELSE
      direction = argmodes[i];
      datatype = pg_catalog.format_type(allargtypes[i], NULL);
    END IF;
    RETURN NEXT;
  END LOOP;
 
  RETURN;
END;$$ LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER;
COMMENT ON FUNCTION public.function_args(character varying, character
varying)
IS $$For a function name and schema, this procedure selects for each
argument the following data:
- position in the argument list (0 for the return value)
- direction 'i', 'o', or 'b'
- name (NULL if not defined)
- data type$$;
  1. SELECT p.proname AS procedure_name,   
  2.           p.pronargs AS num_args,   
  3.           t1.typname AS return_type,   
  4.           a.rolname AS procedure_owner,   
  5.           l.lanname AS language_type,   
  6.           p.proargtypes AS argument_types_oids,   
  7.           prosrc AS body   
  8.      FROM pg_proc p   
  9. LEFT JOIN pg_type t1 ON p.prorettype=t1.oid      
  10. LEFT JOIN pg_authid a ON p.proowner=a.oid    
  11. LEFT JOIN pg_language l ON p.prokeyword">WHERE proname = :PROCEDURE_NAME;