Postgresql 相关系统表查询

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了Postgresql 相关系统表查询，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
  1. --sampledatatotestPostgresqlINFORMATION_SCHEMA
  3. --TABLETEST
  4. CREATETABLETEST(
  5. TEST_NAMECHAR(30)NOTNULL,
  6. TEST_IDINTEGERDEFAULT'0' TEST_DATETIMESTAMPNULL
  7. );
  8. ALTERTABLETESTADDCONSTRAINTPK_TESTPRIMARYKEY(TEST_ID);
  9. --TABLETEST2withsomeCONSTRAINTsandanINDEX
  10. TABLETEST2(
  11. IDINTEGER FIELD1INTEGER,248); line-height:18px"> FIELD2CHAR(15),248); line-height:18px"> FIELD3VARCHAR(50),248); line-height:18px"> FIELD4 FIELD5 ID2TABLETEST2CONSTRAINTPK_TEST2KEY(ID2);
  12. CONSTRAINTTEST2_FIELD1ID_IDXUNIQUE(ID,FIELD1);
  13. CONSTRAINTTEST2_FIELD4_IDXUNIQUE(FIELD4);
  14. INDEXTEST2_FIELD5_IDXONTEST2(FIELD5);
  15. --TABLENUMBERS
  16. TABLENUMBERS(
  17. NUMBER ENCHAR(100) FR--TABLENEWTABLE
  18. TABLENEWTABLE(
  19. INTDEFAULT0 SOMENAMEVARCHAR(12),248); line-height:18px"> SOMEDATETABLENEWTABLECONSTRAINTPKINDEX_IDXKEY(ID);
  20. SEQUENCENEWTABLE_SEQINCREMENT1START1;
  21. --VIEWonTEST
  22. VIEW"testview"(
  23. TEST_NAME,248); line-height:18px"> TEST_ID,248); line-height:18px"> TEST_DATE
  24. )AS
  25. SELECT*
  26. FROMTEST
  27. WHERETEST_NAMELIKE't%';
  28. --VIEWonNUMBERS
  29. VIEW"numbersview"(
  30. NUMBER,248); line-height:18px"> TRANS_EN,248); line-height:18px"> TRANS_FR
  31. FROMNUMBERS
  32. WHERENUMBER>100;
  33. --TRIGGERonNEWTABLE
  34. FUNCTIONadd_stamp()RETURNSOPAQUEAS'
  35. BEGIN
  36. IF(NEW.somedateISNULLORNEW.somedate=0)THEN
  37. NEW.somedate:=CURRENT_TIMESTAMP;
  38. RETURNNEW;
  39. ENDIF;
  40. END;
  41. 'LANGUAGE'plpgsql';
  42. TRIGGERADDCURRENTDATE
  43. BEFOREINSERTORUPDATE
  44. ONnewtableFOREACHROW
  45. EXECUTEPROCEDUREadd_stamp();
  46. --TABLEsfortestingCONSTRAINTs
  47. TABLEtestconstraints(
  48. someidinteger somenamecharactervarying(10)CONSTRAINTtestconstraints_id_pkKEY(someid)
  49. TABLEtestconstraints2(
  50. ext_id modifieddate,248); line-height:18px"> uniquefield usractionCONSTRAINTtestconstraints_id_fkFOREIGNKEY(ext_id)
  51. REFERENCEStestconstraints(someid)MATCHSIMPLE
  52. ONUPDATECASCADEDELETECASCADE,153); background-color:inherit; font-weight:bold">CONSTRAINTunique_2_fields_idxUNIQUE(modified,usraction),153); background-color:inherit; font-weight:bold">CONSTRAINTuniquefld_idxUNIQUE(uniquefield)
  53. );
[sql] view plain copy
  2. --TABLETEST
  3. CREATETABLETEST(
  4. TEST_NAMECHAR(30)NOTNULL,
  5. TEST_IDINTEGERDEFAULT'0' TEST_DATETIMESTAMPNULL
  6. );
  7. ALTERTABLETESTADDCONSTRAINTPK_TESTPRIMARYKEY(TEST_ID);
  8. --TABLETEST2withsomeCONSTRAINTsandanINDEX
  9. TABLETEST2(
  10. IDINTEGER FIELD1INTEGER,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> FIELD2CHAR(15),248); line-height:18px"> FIELD3VARCHAR(50),108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> FIELD4 FIELD5 ID2TABLETEST2CONSTRAINTPK_TEST2KEY(ID2);
  11. CONSTRAINTTEST2_FIELD1ID_IDXUNIQUE(ID,FIELD1);
  12. CONSTRAINTTEST2_FIELD4_IDXUNIQUE(FIELD4);
  13. INDEXTEST2_FIELD5_IDXONTEST2(FIELD5);
  15. --TABLENUMBERS
  16. TABLENUMBERS(
  17. NUMBER ENCHAR(100) FRNULL
  18. );
  19. --TABLENEWTABLE
  20. TABLENEWTABLE(
  21. INTDEFAULT0 SOMENAMEVARCHAR(12),108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> SOMEDATETABLENEWTABLECONSTRAINTPKINDEX_IDXKEY(ID);
  22. SEQUENCENEWTABLE_SEQINCREMENT1START1;
  23. --VIEWonTEST
  24. VIEW"testview"(
  25. TEST_ID,248); line-height:18px"> TEST_DATE
  26. )AS
  27. SELECT*
  28. FROMTEST
  29. WHERETEST_NAMELIKE't%';
  30. --VIEWonNUMBERS
  31. VIEW"numbersview"(
  32. TRANS_EN,248); line-height:18px"> TRANS_FR
  33. FROMNUMBERS
  34. WHERENUMBER>100;
  35. --TRIGGERonNEWTABLE
  36. FUNCTIONadd_stamp()RETURNSOPAQUEAS'
  37. BEGIN
  38. IF(NEW.somedateISNULLORNEW.somedate=0)THEN
  39. NEW.somedate:=CURRENT_TIMESTAMP;
  40. RETURNNEW;
  41. ENDIF;
  42. END;
  43. 'LANGUAGE'plpgsql';
  44. TRIGGERADDCURRENTDATE
  45. BEFOREINSERTORUPDATE
  46. ONnewtableFOREACHROW
  47. EXECUTEPROCEDUREadd_stamp();
  48. --TABLEsfortestingCONSTRAINTs
  49. TABLEtestconstraints(
  50. someidinteger somenamecharactervarying(10)CONSTRAINTtestconstraints_id_pkKEY(someid)
  51. TABLEtestconstraints2(
  52. ext_id modifieddate,248); line-height:18px"> uniquefield usractionCONSTRAINTtestconstraints_id_fkFOREIGNKEY(ext_id)
  53. REFERENCEStestconstraints(someid)MATCHSIMPLE
  54. ONUPDATECASCADEDELETECASCADE,153); background-color:inherit; font-weight:bold">CONSTRAINTunique_2_fields_idxUNIQUE(modified,153); background-color:inherit; font-weight:bold">CONSTRAINTuniquefld_idxUNIQUE(uniquefield)
  55. );

列出所有数据库中的表名
SELECTrelname
    FROMpg_class
  1. WHERErelname!~'^(pg_|sql_)'
  2. ANDrelkind='r';
  3. <!--
  4. SELECTc.relnameAS"Name"
  5. FROMpg_classc,pg_useru
  6. WHEREc.relowner=u.usesysid
  7. ANDc.relkind='r'
  8. ANDNOTEXISTS(
  9. SELECT1
  10. FROMpg_views
  11. WHEREviewname=c.relname
  12. )
  13. ANDc.relname!~'^(pg_|sql_)'
  14. UNION
  15. FROMpg_classc
  16. WHEREc.relkind='r'
  17. FROMpg_user
  18. WHEREusesysid=c.relowner
  19. ANDc.relname!~'^pg_';
  20. -->
  21. --usingINFORMATION_SCHEMA:
  22. SELECTtable_name
  23. FROMinformation_schema.tables
  24. WHEREtable_type='BASETABLE'
  25. ANDtable_schemaIN
  26. ('pg_catalog','information_schema');
copy
    SELECTrelname
  1. FROMpg_class
  2. WHERErelname!~'^(pg_|sql_)'
  3. ANDrelkind='r';
  4. <!--
  5. SELECTc.relnameAS"Name"
  6. FROMpg_classc,pg_useru
  7. WHEREc.relowner=u.usesysid
  8. ANDc.relkind='r'
  9. ANDNOTEXISTS(
  10. SELECT1
  11. FROMpg_views
  12. WHEREviewname=c.relname
  13. )
  14. ANDc.relname!~'^(pg_|sql_)'
  15. UNION
  16. AS"Name"
  17. FROMpg_classc
  18. WHEREc.relkind='r'
  19. NOTEXISTS(
  20. SELECT1
  21. FROMpg_user
  22. WHEREusesysid=c.relowner
  23. )
  24. ANDc.relname!~'^pg_';
  25. -->
  26. --usingINFORMATION_SCHEMA:
  27. SELECTtable_name
  28. FROMinformation_schema.tables
  29. WHEREtable_type='BASETABLE'
  30. ANDtable_schemaIN
  31. ('pg_catalog','information_schema');

列出所有视图
sql代码
--withpostgresql7.2:
    SELECTviewname
  1. WHEREviewname!~'^pg_';
  2. --withpostgresql7.4andlater:
  3. WHEREschemaname'information_schema')
  4. ANDviewname!~'^pg_';
  5. WHEREtable_type='VIEW'
  6. ANDtable_name!~'^pg_';
  7. --or
  8. FROMinformation_schema.views
  9. WHEREtable_schemaIN('pg_catalog',248); line-height:18px"> #showonlytheVIEWsreferencingagiventable
  10. NATURALJOINpg_tables
  11. WHEREtablename='test';
  12. -->
copy
    --withpostgresql7.2:
  1. SELECTviewname
  2. WHEREviewname!~'^pg_';
  3. --withpostgresql7.4andlater:
  4. WHEREschemaname'information_schema')
  5. ANDviewname!~'^pg_';
  6. --usingINFORMATION_SCHEMA:
  7. SELECTtable_name
  8. FROMinformation_schema.tables
  9. WHEREtable_type='VIEW'
  10. IN
  11. ('pg_catalog','information_schema')
  12. ANDtable_name!~'^pg_';
  13. --or
  14. FROMinformation_schema.views
  15. WHEREtable_schemaIN('pg_catalog',128); background-color:inherit">ANDtable_name!~'^pg_';
  16. <!--
  17. #showonlytheVIEWsreferencingagiventable
  18. NATURALJOINpg_tables
  19. WHEREtablename='test';
  20. -->

列出所有用户
SELECTusename
    FROMpg_user;
copy
    SELECTusename
  1. FROMpg_user;

列出某表中得所有字段
SELECTa.attname
    sql" style="margin:0px 0px 1px 45px!important; padding:0px; border:none; list-style-position:initial; background-color:rgb(255,pg_attributea,pg_typet
  1. WHEREc.relname='test2'
  2. ANDa.attnum>0
  3. ANDa.attrelid=c.oid
  4. ANDa.atttypid=t.oid
  5. --withINFORMATION_SCHEMA:
  6. SELECTcolumn_name
  7. FROMinformation_schema.columns
  8. WHEREtable_name='test2';
copy
    SELECTa.attname
  1. WHEREc.relname='test2'
  2. ANDa.attnum>0
  3. ANDa.attrelid=c.oid
  4. ANDa.atttypid=t.oid
  5. --withINFORMATION_SCHEMA:
  6. SELECTcolumn_name
  7. FROMinformation_schema.columns
  8. WHEREtable_name='test2';

列出某表字段的信息
SELECTa.attnum ASordinal_position,
    a.attnameAScolumn_name,248); line-height:18px"> t.typnameASdata_type,248); line-height:18px"> a.attlenAScharacter_maximum_length,248); line-height:18px"> a.atttypmodASmodifier,248); line-height:18px"> a.attnotnullASnotnull,248); line-height:18px"> a.atthasdefAShasdefault
  1. pg_attributea,248); line-height:18px"> pg_typet
  2. ORDERBYa.attnum;
  3. SELECTordinal_position,248); line-height:18px"> column_name,248); line-height:18px"> data_type,248); line-height:18px"> column_default,248); line-height:18px"> is_nullable,248); line-height:18px"> character_maximum_length,248); line-height:18px"> numeric_precision
  4. WHEREtable_name='test2'
  5. BYordinal_position;
copy
    SELECTa.attnumASordinal_position,248); line-height:18px"> a.attnameAScolumn_name,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> t.typnameASdata_type,248); line-height:18px"> a.attlenAScharacter_maximum_length,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> a.atttypmodASmodifier,248); line-height:18px"> a.attnotnullASnotnull,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> a.atthasdefAShasdefault
  1. pg_attributea,248); line-height:18px"> pg_typet
  2. ORDERBYa.attnum;
  3. --withINFORMATION_SCHEMA:
  4. SELECTordinal_position,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> data_type,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> is_nullable,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> numeric_precision
  5. FROMinformation_schema.columns
  6. WHEREtable_name='test2'
  7. BYordinal_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
WHEREoidIN(
  • SELECTindexrelid
  • FROMpg_index,pg_class
  • WHEREpg_class.relname='test2'
  • ANDpg_class.oid=pg_index.indrelid
  • ANDindisunique!='t'
  • ANDindisprimary!='t'
  • copy
      WHEREoidIN(
    1. SELECTindexrelid
    2. FROMpg_index,pg_class
    3. WHEREpg_class.relname='test2'
    4. ANDpg_class.oid=pg_index.indrelid
    5. ANDindisunique!='t'
    6. ANDindisprimary!='t'
    7. );

    列出表的索引信息
    SELECTrelname,indkey
      FROMpg_class,pg_index
    1. WHEREpg_class.oid=pg_index.indexrelid
    2. ANDpg_class.oid
    3. SELECTt.relname,a.attname,a.attnum
    4. FROMpg_indexc
    5. LEFTJOINpg_classt
    6. ONc.indrelid=t.oid
    7. JOINpg_attributea
    8. ONa.attrelid=t.oid
    9. ANDa.attnum=ANY(indkey)
    10. WHEREt.relname='test2'
    11. ANDa.attnum=6;--thisistheindexkey
    copy
      SELECTrelname,indkey
    1. FROMpg_class,pg_index
    2. WHEREpg_class.oid=pg_index.indexrelid
    3. ANDpg_class.oidIN(
    4. SELECTindexrelid
    5. WHEREpg_class.relname='test2'
    6. ANDpg_class.oid=pg_index.indrelid
    7. ANDindisunique!='t'
    8. ANDindisprimary!='t'
    9. SELECTt.relname,a.attnum
    10. FROMpg_indexc
    11. LEFTJOINpg_classt
    12. ONc.indrelid=t.oid
    13. JOINpg_attributea
    14. ONa.attrelid=t.oid
    15. ANDa.attnum=ANY(indkey)
    16. WHEREt.relname='test2'
    17. ANDa.attnum=6;--thisistheindexkey

    列出表的约束
    SELECTc.conname ASconstraint_name,147); background-color:inherit">CASEc.contype
  • WHEN'c'THEN'CHECK'
  • WHEN'f'THEN'FOREIGNKEY'
  • WHEN'p'THEN'PRIMARYKEY'
  • WHEN'u'THEN'UNIQUE'
  • ENDAS"constraint_type",147); background-color:inherit">CASEWHENc.condeferrable='f'THEN0ELSE1ASis_deferrable,153); background-color:inherit; font-weight:bold">WHENc.condeferred='f'ASis_deferred,248); line-height:18px"> t.relnameAStable_name,248); line-height:18px"> array_to_string(c.conkey,'')ASconstraint_key,147); background-color:inherit">CASEconfupdtype
  • WHEN'a'THEN'NOACTION'
  • WHEN'r'THEN'RESTRICT'
  • THEN'CASCADE'
  • WHEN'n'THEN'SETNULL'
  • WHEN'd'THEN'SETDEFAULT'
  • ASon_update,147); background-color:inherit">CASEconfdeltype
  • ASon_delete,147); background-color:inherit">CASEconfmatchtype
  • THEN'UNSPECIFIED'
  • THEN'FULL'
  • THEN'PARTIAL'
  • ASmatch_type,248); line-height:18px"> t2.relnameASreferences_table,248); line-height:18px"> array_to_string(c.confkey,153); background-color:inherit; font-weight:bold">ASfk_constraint_key
  • FROMpg_constraintc
  • JOINpg_classtONc.conrelid=t.oid
  • JOINpg_classt2ONc.confrelid=t2.oid
  • WHEREt.relname='testconstraints2'
  • ANDc.conname='testconstraints_id_fk';
  • SELECTtc.constraint_name,248); line-height:18px"> tc.constraint_type,248); line-height:18px"> tc.table_name,248); line-height:18px"> kcu.column_name,248); line-height:18px"> tc.is_deferrable,248); line-height:18px"> tc.initially_deferred,248); line-height:18px"> rc.match_option rc.update_rule rc.delete_rule ccu.table_name ccu.column_nameASreferences_field
  • FROMinformation_schema.table_constraintstc
  • JOINinformation_schema.key_column_usagekcu
  • ONtc.constraint_catalog=kcu.constraint_catalog
  • ANDtc.constraint_schema=kcu.constraint_schema
  • ANDtc.constraint_name=kcu.constraint_name
  • JOINinformation_schema.referential_constraintsrc
  • ONtc.constraint_catalog=rc.constraint_catalog
  • ANDtc.constraint_schema=rc.constraint_schema
  • ANDtc.constraint_name=rc.constraint_name
  • JOINinformation_schema.constraint_column_usageccu
  • ONrc.unique_constraint_catalog=ccu.constraint_catalog
  • ANDrc.unique_constraint_schema=ccu.constraint_schema
  • ANDrc.unique_constraint_name=ccu.constraint_name
  • WHEREtc.table_name='testconstraints2'
  • ANDtc.constraint_name='testconstraints_id_fk';
    • copy
      SELECTc.connameASconstraint_name,248); line-height:18px"> CASEc.contype
    1. WHEN'c'THEN'CHECK'
    2. WHEN'f'THEN'FOREIGNKEY'
    3. WHEN'p'THEN'PRIMARYKEY'
    4. WHEN'u'THEN'UNIQUE'
    5. ENDAS"constraint_type",147); background-color:inherit">CASEWHENc.condeferrable='f'THEN0ELSE1ASis_deferrable,153); background-color:inherit; font-weight:bold">WHENc.condeferred='f'ASis_deferred,248); line-height:18px"> t.relnameAStable_name,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> array_to_string(c.conkey,'')ASconstraint_key,147); background-color:inherit">CASEconfupdtype
    6. WHEN'a'THEN'NOACTION'
    7. WHEN'r'THEN'RESTRICT'
    8. THEN'CASCADE'
    9. WHEN'n'THEN'SETNULL'
    10. WHEN'd'THEN'SETDEFAULT'
    11. ASon_update,147); background-color:inherit">CASEconfdeltype
    12. THEN'NOACTION'
    13. THEN'RESTRICT'
    14. THEN'CASCADE'
    15. THEN'SETNULL'
    16. THEN'SETDEFAULT'
    17. ASon_delete,147); background-color:inherit">CASEconfmatchtype
    18. THEN'UNSPECIFIED'
    19. THEN'FULL'
    20. THEN'PARTIAL'
    21. ASmatch_type,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> t2.relnameASreferences_table,153); background-color:inherit; font-weight:bold">ASfk_constraint_key
    22. FROMpg_constraintc
    23. JOINpg_classtONc.conrelid=t.oid
    24. JOINpg_classt2ONc.confrelid=t2.oid
    25. WHEREt.relname='testconstraints2'
    26. ANDc.conname='testconstraints_id_fk';
    27. SELECTtc.constraint_name,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> tc.table_name,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> tc.is_deferrable,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> rc.match_option rc.update_rule rc.delete_rule ccu.table_name ccu.column_nameASreferences_field
    28. FROMinformation_schema.table_constraintstc
    29. JOINinformation_schema.key_column_usagekcu
    30. ONtc.constraint_catalog=kcu.constraint_catalog
    31. ANDtc.constraint_schema=kcu.constraint_schema
    32. ANDtc.constraint_name=kcu.constraint_name
    33. JOINinformation_schema.referential_constraintsrc
    34. ONtc.constraint_catalog=rc.constraint_catalog
    35. ANDtc.constraint_schema=rc.constraint_schema
    36. ANDtc.constraint_name=rc.constraint_name
    37. JOINinformation_schema.constraint_column_usageccu
    38. ONrc.unique_constraint_catalog=ccu.constraint_catalog
    39. ANDrc.unique_constraint_schema=ccu.constraint_schema
    40. ANDrc.unique_constraint_name=ccu.constraint_name
    41. WHEREtc.table_name='testconstraints2'
    42. ANDtc.constraint_name='testconstraints_id_fk';

    列出所有序列
    WHERErelkind='S'
  • ANDrelnamespaceSELECToid
  • FROMpg_namespace
  • WHEREnspnameLIKE'pg_%'
  • ANDnspname!='information_schema'
  • copy
      WHERErelkind='S'
    1. ANDrelnamespaceSELECToid
    2. FROMpg_namespace
    3. WHEREnspnameLIKE'pg_%'
    4. ANDnspname!='information_schema'
    5. );



    列出所有触发器

    SELECTtrg.tgnameAStrigger_name

    FROMpg_triggertrg,pg_classtbl
  • WHEREtrg.tgrelid=tbl.oid
  • ANDtbl.relname!~'^pg_';
  • SELECTtgnameAStrigger_name
  • FROMpg_trigger
  • WHEREtgname!~'^pg_';
  • SELECTDISTINCTtrigger_name
  • FROMinformation_schema.triggers
  • WHEREtrigger_schema'information_schema');
  • SELECTtrg.tgnameANDtbl.relname='newtable';
  • WHEREevent_object_table='newtable'
  • ANDtrigger_schemacopy
      SELECTtrg.tgnameAStrigger_name
    1. FROMpg_triggertrg,pg_classtbl
    2. WHEREtrg.tgrelid=tbl.oid
    3. ANDtbl.relname!~'^pg_';
    4. --or
    5. SELECTtgnameAStrigger_name
    6. FROMpg_trigger
    7. WHEREtgname!~'^pg_';
    8. SELECTDISTINCTtrigger_name
    9. FROMinformation_schema.triggers
    10. WHEREtrigger_schema'information_schema');
    11. ANDtbl.relname='newtable';
    12. WHEREevent_object_table='newtable'
    13. ANDtrigger_schema列出所有触发器的信息
      SELECTtrg.tgname AStrigger_name,248); line-height:18px"> tbl.relname p.pronameASfunction_name,147); background-color:inherit">CASEtrg.tgtype&cast(2asint2)
    14. WHEN0THEN'AFTER'
    15. ELSE'BEFORE'
    16. AStrigger_type,147); background-color:inherit">cast(28WHEN16THEN'UPDATE'
    17. WHEN8THEN'DELETE'
    18. WHEN4THEN'INSERT'
    19. WHEN20THEN'INSERT,UPDATE'
    20. WHEN28
    21. WHEN24THEN'UPDATE,153); background-color:inherit; font-weight:bold">WHEN12AStrigger_event,147); background-color:inherit">cast(1THEN'STATEMENT'
    22. ELSE'ROW'
    23. ASaction_orientation
    24. pg_classtbl,248); line-height:18px"> pg_procp
    25. ANDtrg.tgfoid=p.oid
    26. copy
        AStrigger_name,248); line-height:18px"> tbl.relname p.pronameASfunction_name,147); background-color:inherit">CASEtrg.tgtype&cast(2asint2)
      1. WHEN0THEN'AFTER'
      2. ELSE'BEFORE'
      3. AStrigger_type,147); background-color:inherit">cast(28WHEN16THEN'UPDATE'
      4. WHEN8THEN'DELETE'
      5. WHEN4THEN'INSERT'
      6. WHEN20THEN'INSERT,UPDATE'
      7. WHEN28
      8. WHEN24THEN'UPDATE,DELETE'
      9. WHEN12AStrigger_event,147); background-color:inherit">cast(1asint2)
      10. THEN'STATEMENT'
      11. ELSE'ROW'
      12. ASaction_orientation
      13. pg_procp
      14. WHEREtrg.tgrelid=tbl.oid
      15. ANDtrg.tgfoid=p.oid
      16. 列出所有函数
        SELECTproname
          FROMpg_procpr,248); line-height:18px"> pg_typetp
        1. WHEREtp.oid=pr.prorettype
        2. ANDpr.proisagg=FALSE
        3. ANDtp.typname<>'trigger'
        4. ANDpr.pronamespaceSELECTroutine_name
        5. FROMinformation_schema.routines
        6. WHEREspecific_schemaANDtype_udt_name!='trigger';
        copy
          SELECTproname
        1. FROMpg_procpr,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> pg_typetp
        2. WHEREtp.oid=pr.prorettype
        3. ANDpr.proisagg=FALSE
        4. ANDtp.typname<>'trigger'
        5. ANDpr.pronamespaceSELECToid
        6. FROMpg_namespace
        7. LIKE'pg_%'
        8. ANDnspname!='information_schema'
        9. SELECTroutine_name
        10. FROMinformation_schema.routines
        11. WHEREspecific_schemaANDtype_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(
          INfuncnamevarying,128); background-color:inherit">INschemaOUTposinteger,153); background-color:inherit; font-weight:bold">OUTdirectioncharacter,153); background-color:inherit; font-weight:bold">OUTargnameOUTdatatypevarying)
        1. RETURNSSETOFRECORDAS$$DECLARE
        2. rettypevarying;
        3. argtypesoidvector;
        4. allargtypesoid[];
        5. argmodes"char"[];
        6. argnamestext[];
        7. miniinteger;
        8. maxi /*getobjectIDoffunction*/
        9. INTOrettype,argtypes,allargtypes,argmodes,argnames
        10. CASE
        11. WHENpg_proc.proretset
        12. THEN'setof'||pg_catalog.format_type(pg_proc.prorettype,NULL)
        13. ELSEpg_catalog.format_type(pg_proc.prorettype,128); background-color:inherit">NULL)END,248); line-height:18px"> pg_proc.proargtypes,248); line-height:18px"> pg_proc.proallargtypes,248); line-height:18px"> pg_proc.proargmodes,248); line-height:18px"> pg_proc.proargnames
        14. FROMpg_catalog.pg_proc
        15. JOINpg_catalog.pg_namespace
        16. ON(pg_proc.pronamespace=pg_namespace.oid)
        17. WHEREpg_proc.prorettype<>'pg_catalog.cstring'::pg_catalog.regtype
        18. AND(pg_proc.proargtypes[0]ORpg_proc.proargtypes[0]<>'pg_catalog.cstring'::pg_catalog.regtype)
        19. NOTpg_proc.proisagg
        20. ANDpg_proc.proname=funcname
        21. ANDpg_namespace.nspname=schema
        22. ANDpg_catalog.pg_function_is_visible(pg_proc.oid);
        23. /*bailoutifnotfound*/
        24. IFNOTFOUNDRETURN;
        25. /*returnarowforthereturnvalue*/
        26. pos=0;
        27. direction='o'::char;
        28. argname='RETURNVALUE';
        29. datatype=rettype;
        30. RETURNNEXT;
        31. /*unfortunatelyallargtypesisNULLiftherearenoOUTparameters*/
        32. IFallargtypesNULL mini=array_lower(argtypes,1);maxi=array_upper(argtypes,1);
        33. ELSE
        34. mini=array_lower(allargtypes,1);maxi=array_upper(allargtypes,248); line-height:18px"> IFmaxi<miniTHENRETURN; /*loopallthearguments*/
        35. FORiINmini..maxiLOOP
        36. pos=i-mini+1;
        37. IFargnames argname=NULL;
        38. argname=argnames[pos];
        39. direction='i':: datatype=pg_catalog.format_type(argtypes[i],128); background-color:inherit">NULL);
        40. direction=argmodes[i];
        41. datatype=pg_catalog.format_type(allargtypes[i],153); background-color:inherit; font-weight:bold">ENDLOOP;
        42. END;$$LANGUAGEplpgsqlSTABLESTRICTSECURITYINVOKER;
        43. COMMENTFUNCTIONpublic.function_args(character
        44. IS$$Forafunctionnameandschema,thisprocedureselectsforeach
        45. argumentthefollowingdata:
        46. -positionintheargumentlist(0returnvalue)
        47. -direction'i','o',128); background-color:inherit">or'b'
        48. -name(NULLifnotdefined)
        49. -datatype$$;
        copy
          CREATEORREPLACEFUNCTIONpublic.function_args(
        1. INfuncnamevarying,128); background-color:inherit">INschemaOUTposinteger,153); background-color:inherit; font-weight:bold">OUTdirectioncharacter,153); background-color:inherit; font-weight:bold">OUTargnameOUTdatatypevarying)
        2. RETURNSSETOFRECORDAS$$DECLARE
        3. rettypevarying;
        4. argtypesoidvector;
        5. allargtypesoid[];
        6. argmodes"char"[];
        7. argnamestext[];
        8. miniinteger;
        9. maxiinteger;
        10. /*getobjectIDoffunction*/
        11. INTOrettype,argnames
        12. CASE
        13. WHENpg_proc.proretset
        14. THEN'setof'||pg_catalog.format_type(pg_proc.prorettype,128); background-color:inherit">NULL)
        15. ELSEpg_catalog.format_type(pg_proc.prorettype,128); background-color:inherit">NULL)END,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> pg_proc.proargtypes,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px"> pg_proc.proargmodes,248); line-height:18px"> pg_proc.proargnames
        16. FROMpg_catalog.pg_proc
        17. JOINpg_catalog.pg_namespace
        18. ON(pg_proc.pronamespace=pg_namespace.oid)
        19. WHEREpg_proc.prorettype<>'pg_catalog.cstring'::pg_catalog.regtype
        20. AND(pg_proc.proargtypes[0]ORpg_proc.proargtypes[0]<>'pg_catalog.cstring'::pg_catalog.regtype)
        21. NOTpg_proc.proisagg
        22. ANDpg_proc.proname=funcname
        23. ANDpg_namespace.nspname=schema
        24. ANDpg_catalog.pg_function_is_visible(pg_proc.oid);
        25. /*bailoutifnotfound*/
        26. IFNOTFOUNDRETURN;
        27. ENDIF;
        28. /*returnarowforthereturnvalue*/
        29. pos=0;
        30. direction='o'::char;
        31. argname='RETURNVALUE';
        32. datatype=rettype;
        33. RETURNNEXT;
        34. /*unfortunatelyallargtypesisNULLiftherearenoOUTparameters*/
        35. IFallargtypesNULLELSE
        36. IFmaxi<miniTHENRETURN; /*loopallthearguments*/
        37. FORiINmini..maxiLOOP
        38. pos=i-mini+1;
        39. IFargnames argname=NULL;
        40. argname=argnames[pos];
        41. IFallargtypesTHEN
        42. direction='i'::NULL);
        43. direction=argmodes[i];
        44. datatype=pg_catalog.format_type(allargtypes[i],128); background-color:inherit">NULL);
        45. NEXT;
        46. ENDLOOP;
        47. END;$$LANGUAGEplpgsqlSTABLESTRICTSECURITYINVOKER;
        48. COMMENTpublic.function_args(character
        49. IS$$Forafunctionnameandschema,153); background-color:inherit; font-weight:bold">procedureselectsforeach
        50. argumentthefollowingdata:
        51. -positionintheargumentlist(0returnvalue)
        52. -direction'i','o',128); background-color:inherit">or'b'
        53. -name(NULLifnotdefined)
        54. -datatype$$;
        SELECTp.pronameASprocedure_name,248); line-height:18px"> p.pronargsASnum_args,248); line-height:18px"> t1.typnameASreturn_type,248); line-height:18px"> a.rolnameASprocedure_owner,248); line-height:18px"> l.lannameASlanguage_type,248); line-height:18px"> p.proargtypesASargument_types_oids,248); line-height:18px"> prosrcASbody
      17. FROMpg_procp
      18. JOINpg_typet1ONp.prorettype=t1.oid
      19. JOINpg_authidaONp.proowner=a.oid
      20. JOINpg_languagelONp.prolang=l.oid
      21. WHEREproname=:PROCEDURE_NAME;
      原文链接：https://www.f2er.com/postgresql/196197.html

      猜你在找的Postgre SQL相关文章

      PosegreSQL基础回顾(第 4 章 SQL语法)
      来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
      PosegreSQL基础回顾(第 8 章 数据类型)
      来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
      PosegreSQL基础回顾(第 5 章 数据定义)
      来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
      PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
      来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
      PosegreSQL基础回顾(第 13 章 并发控制)
      来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
      PostgreSQL安装和使用
      青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
      PostgreSQL整体架构
       
      为什么选择PostgreSQL而不是MySQL
      David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
      为什么选择PostgreSQL而不是MySQL
      David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
      专访唐成：从小工到专家，PostgreSQL的修炼之道
      唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
      MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
      undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问