Postgresql 相关系统表查询

概述-- 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  --sampledatatotestPostgresqlinformatION_SCHEMA --tableTEST CREATEtableTEST( TEST_nameCHAR(30)NOTNulL, TEST_IDINTEGERDEFAulT'0' TEST_DATETIMESTAMPNulL ); ALTERtableTESTADDCONSTRAINTPK_TESTPRIMARYKEY(TEST_ID); --tableTEST2withsomeCONSTRAINTsandanINDEX tableTEST2( IDINTEGER FIELD1INTEGER,248); line-height:18px"> FIELD2CHAR(15),248); line-height:18px"> FIELD3VARCHAR(50),248); line-height:18px"> FIELD4 FIELD5 ID2tableTEST2CONSTRAINTPK_TEST2KEY(ID2); CONSTRAINTTEST2_FIELD1ID_IDXUNIQUE(ID,FIELD1); CONSTRAINTTEST2_FIELD4_IDXUNIQUE(FIELD4); INDEXTEST2_FIELD5_IDXONTEST2(FIELD5); --tableNUMBERS tableNUMBERS( NUMBER ENCHAR(100) FR--tableNEWtable tableNEWtable( INTDEFAulT0 SOMEnameVARCHAR(12),248); line-height:18px"> SOMEDATEtableNEWtableCONSTRAINTPKINDEX_IDXKEY(ID); SEQUENCENEWtable_SEQINCREMENT1START1; --VIEWonTEST VIEW"testvIEw"( TEST_name,248); line-height:18px"> TEST_ID,248); line-height:18px"> TEST_DATE )AS SELECT* FROMTEST WHERETEST_nameliKE't%'; --VIEWonNUMBERS VIEW"numbersvIEw"( NUMBER,248); line-height:18px"> TRANS_EN,248); line-height:18px"> TRANS_FR FROMNUMBERS WHERENUMBER>100; --TRIGGERonNEWtable FUNCTIONadd_stamp()RETURNSOPAQUEAS' BEGIN IF(NEW.somedateISNulLORNEW.somedate=0)THEN NEW.somedate:=CURRENT_TIMESTAMP; RETURNNEW; ENDIF; END; 'LANGUAGE'plpgsql'; TRIGGERADDCURRENTDATE BEFOREINSERTORUPDATE ONnewtableFOREACHROW EXECUTEPROCEDUREadd_stamp(); --tablesfortestingCONSTRAINTs tabletestconstraints( someIDinteger somenamecharactervarying(10)CONSTRAINTtestconstraints_ID_pkKEY(someID) tabletestconstraints2( ext_ID modifIEddate,248); line-height:18px"> uniquefIEld usractionCONSTRAINTtestconstraints_ID_fkFOREIGNKEY(ext_ID) REFERENCEStestconstraints(someID)MATCHSIMPLE ONUPDATECASCADEDELETECASCADE,153); background-color:inherit; Font-weight:bold">CONSTRAINTunique_2_fIElds_IDxUNIQUE(modifIEd,usraction),153); background-color:inherit; Font-weight:bold">CONSTRAINTuniquefld_IDxUNIQUE(uniquefIEld) ); [sql] view plain copy --tableTEST CREATEtableTEST( TEST_nameCHAR(30)NOTNulL, TEST_IDINTEGERDEFAulT'0'TEST_DATETIMESTAMPNulL ); ALTERtableTESTADDCONSTRAINTPK_TESTPRIMARYKEY(TEST_ID); --tableTEST2withsomeCONSTRAINTsandanINDEX tableTEST2( 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); CONSTRAINTTEST2_FIELD1ID_IDXUNIQUE(ID,FIELD1); CONSTRAINTTEST2_FIELD4_IDXUNIQUE(FIELD4); INDEXTEST2_FIELD5_IDXONTEST2(FIELD5); --tableNUMBERS tableNUMBERS( NUMBER ENCHAR(100) FRNulL ); --tableNEWtable tableNEWtable( INTDEFAulT0 SOMEnameVARCHAR(12),108); List-style:decimal-leading-zero outsIDe; color:inherit; line-height:18px"> SOMEDATEtableNEWtableCONSTRAINTPKINDEX_IDXKEY(ID); SEQUENCENEWtable_SEQINCREMENT1START1; --VIEWonTEST VIEW"testvIEw"( TEST_ID,248); line-height:18px"> TEST_DATE )AS SELECT* FROMTEST WHERETEST_nameliKE't%'; --VIEWonNUMBERS VIEW"numbersvIEw"( TRANS_EN,248); line-height:18px"> TRANS_FR FROMNUMBERS WHERENUMBER>100; --TRIGGERonNEWtable FUNCTIONadd_stamp()RETURNSOPAQUEAS' BEGIN IF(NEW.somedateISNulLORNEW.somedate=0)THEN NEW.somedate:=CURRENT_TIMESTAMP; RETURNNEW; ENDIF; END; 'LANGUAGE'plpgsql'; TRIGGERADDCURRENTDATE BEFOREINSERTORUPDATE ONnewtableFOREACHROW EXECUTEPROCEDUREadd_stamp(); --tablesfortestingCONSTRAINTs tabletestconstraints( someIDinteger somenamecharactervarying(10)CONSTRAINTtestconstraints_ID_pkKEY(someID) tabletestconstraints2( ext_ID modifIEddate,248); line-height:18px"> uniquefIEld usractionCONSTRAINTtestconstraints_ID_fkFOREIGNKEY(ext_ID) REFERENCEStestconstraints(someID)MATCHSIMPLE ONUPDATECASCADEDELETECASCADE,153); background-color:inherit; Font-weight:bold">CONSTRAINTunique_2_fIElds_IDxUNIQUE(modifIEd,153); background-color:inherit; Font-weight:bold">CONSTRAINTuniquefld_IDxUNIQUE(uniquefIEld) );
列出所有数据库中的表名
SELECTrelname FROMpg_class WHERErelname!~'^(pg_|sql_)' ANDrelkind='r'; <!-- SELECTc.relnameAS"name" FROMpg_classc,pg_useru WHEREc.relowner=u.usesysID ANDc.relkind='r' ANDNOTEXISTS( SELECT1 FROMpg_vIEws WHEREvIEwname=c.relname ) ANDc.relname!~'^(pg_|sql_)' UNION FROMpg_classc WHEREc.relkind='r' FROMpg_user WHEREusesysID=c.relowner ANDc.relname!~'^pg_'; --> --usinginformatION_SCHEMA: SELECTtable_name FROMinformation_schema.tables WHEREtable_type='BASEtable' ANDtable_schemaIN ('pg_catalog','information_schema'); copy SELECTrelname FROMpg_class WHERErelname!~'^(pg_|sql_)' ANDrelkind='r'; <!-- SELECTc.relnameAS"name" FROMpg_classc,pg_useru WHEREc.relowner=u.usesysID ANDc.relkind='r' ANDNOTEXISTS( SELECT1 FROMpg_vIEws WHEREvIEwname=c.relname ) ANDc.relname!~'^(pg_|sql_)' UNION AS"name" FROMpg_classc WHEREc.relkind='r' NOTEXISTS( SELECT1 FROMpg_user WHEREusesysID=c.relowner ) ANDc.relname!~'^pg_'; --> --usinginformatION_SCHEMA: SELECTtable_name FROMinformation_schema.tables WHEREtable_type='BASEtable' ANDtable_schemaIN ('pg_catalog','information_schema');
列出所有视图
sql代码 --withpostgresql7.2: SELECTvIEwname WHEREvIEwname!~'^pg_'; --withpostgresql7.4andlater: WHEREschemaname'information_schema') ANDvIEwname!~'^pg_'; WHEREtable_type='VIEW' ANDtable_name!~'^pg_'; --or FROMinformation_schema.vIEws WHEREtable_schemaIN('pg_catalog',248); line-height:18px"> #showonlytheVIEWsreferencingagiventable NATURALJOINpg_tables WHEREtablename='test'; --> copy --withpostgresql7.2: SELECTvIEwname WHEREvIEwname!~'^pg_'; --withpostgresql7.4andlater: WHEREschemaname'information_schema') ANDvIEwname!~'^pg_'; --usinginformatION_SCHEMA: SELECTtable_name FROMinformation_schema.tables WHEREtable_type='VIEW' IN ('pg_catalog','information_schema') ANDtable_name!~'^pg_'; --or FROMinformation_schema.vIEws WHEREtable_schemaIN('pg_catalog',128); background-color:inherit">ANDtable_name!~'^pg_'; <!-- #showonlytheVIEWsreferencingagiventable NATURALJOINpg_tables WHEREtablename='test'; -->
列出所有用户
SELECTusename FROMpg_user; copy SELECTusename FROMpg_user;
列出某表中得所有字段
SELECTa.attname WHEREc.relname='test2' ANDa.attnum>0 ANDa.attrelID=c.oID ANDa.atttypID=t.oID --withinformatION_SCHEMA: SELECTcolumn_name FROMinformation_schema.columns WHEREtable_name='test2'; copy SELECTa.attname WHEREc.relname='test2' ANDa.attnum>0 ANDa.attrelID=c.oID ANDa.atttypID=t.oID --withinformatION_SCHEMA: SELECTcolumn_name FROMinformation_schema.columns 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 pg_attributea,248); line-height:18px"> pg_typet ORDERBYa.attnum; 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 WHEREtable_name='test2' 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 pg_attributea,248); line-height:18px"> pg_typet ORDERBYa.attnum; --withinformatION_SCHEMA: 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 FROMinformation_schema.columns WHEREtable_name='test2' 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( SELECTindexrelID FROMpg_index,pg_class WHEREpg_class.relname='test2' ANDpg_class.oID=pg_index.indrelID ANDindisunique!='t' ANDindisprimary!='t' );
列出表的索引信息
SELECTrelname,indkey FROMpg_class,pg_index WHEREpg_class.oID=pg_index.indexrelID ANDpg_class.oID SELECTt.relname,a.attname,a.attnum FROMpg_indexc leftJOINpg_classt ONc.indrelID=t.oID JOINpg_attributea ONa.attrelID=t.oID ANDa.attnum=ANY(indkey) WHEREt.relname='test2' ANDa.attnum=6;--thisistheindexkey copy SELECTrelname,indkey FROMpg_class,pg_index WHEREpg_class.oID=pg_index.indexrelID ANDpg_class.oIDIN( SELECTindexrelID WHEREpg_class.relname='test2' ANDpg_class.oID=pg_index.indrelID ANDindisunique!='t' ANDindisprimary!='t' SELECTt.relname,a.attnum FROMpg_indexc leftJOINpg_classt ONc.indrelID=t.oID JOINpg_attributea ONa.attrelID=t.oID ANDa.attnum=ANY(indkey) WHEREt.relname='test2' 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 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,108); List-style:decimal-leading-zero outsIDe; color:inherit; 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 THEN'NOACTION' THEN'RESTRICT' THEN'CASCADE' THEN'SETNulL' THEN'SETDEFAulT' ASon_delete,147); background-color:inherit">CASEconfmatchtype THEN'UnspecIFIED' THEN'FulL' THEN'PARTIAL' 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 FROMpg_constraintc JOINpg_classtONc.conrelID=t.oID JOINpg_classt2ONc.confrelID=t2.oID WHEREt.relname='testconstraints2' ANDc.conname='testconstraints_ID_fk'; 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 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';
列出所有序列
WHERErelkind='S' ANDrelnamespaceSELECToID FROMpg_namespace WHEREnspnameliKE'pg_%' ANDnspname!='information_schema' copy WHERErelkind='S' ANDrelnamespaceSELECToID FROMpg_namespace WHEREnspnameliKE'pg_%' ANDnspname!='information_schema' );

列出所有触发器

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 FROMpg_triggertrg,pg_classtbl WHEREtrg.tgrelID=tbl.oID ANDtbl.relname!~'^pg_'; --or SELECTtgnameAStrigger_name FROMpg_trigger WHEREtgname!~'^pg_'; SELECTdisTINCTtrigger_name FROMinformation_schema.triggers WHEREtrigger_schema'information_schema'); ANDtbl.relname='newtable'; WHEREevent_object_table='newtable' ANDtrigger_schema列出所有触发器的信息
SELECTtrg.tgname AStrigger_name,248); line-height:18px"> tbl.relname p.pronameASfunction_name,147); background-color:inherit">CASEtrg.tgtype&cast(2asint2) WHEN0THEN'AFTER' ELSE'BEFORE' AStrigger_type,147); background-color:inherit">cast(28WHEN16THEN'UPDATE' WHEN8THEN'DELETE' WHEN4THEN'INSERT' WHEN20THEN'INSERT,UPDATE' WHEN28 WHEN24THEN'UPDATE,153); background-color:inherit; Font-weight:bold">WHEN12AStrigger_event,147); background-color:inherit">cast(1THEN'STATEMENT' ELSE'ROW' ASaction_orIEntation pg_classtbl,248); line-height:18px"> pg_procp ANDtrg.tgfoID=p.oID copy AStrigger_name,248); line-height:18px"> tbl.relname p.pronameASfunction_name,147); background-color:inherit">CASEtrg.tgtype&cast(2asint2) WHEN0THEN'AFTER' ELSE'BEFORE' AStrigger_type,147); background-color:inherit">cast(28WHEN16THEN'UPDATE' WHEN8THEN'DELETE' WHEN4THEN'INSERT' WHEN20THEN'INSERT,UPDATE' WHEN28 WHEN24THEN'UPDATE,DELETE' WHEN12AStrigger_event,147); background-color:inherit">cast(1asint2) THEN'STATEMENT' ELSE'ROW' ASaction_orIEntation pg_procp WHEREtrg.tgrelID=tbl.oID ANDtrg.tgfoID=p.oID 列出所有函数
SELECTproname FROMpg_procpr,248); line-height:18px"> pg_typetp WHEREtp.oID=pr.prorettype ANDpr.proisagg=FALSE ANDtp.typname<>'trigger' ANDpr.pronamespaceSELECTroutine_name FROMinformation_schema.routines WHEREspecific_schemaANDtype_udt_name!='trigger'; copy SELECTproname FROMpg_procpr,108); List-style:decimal-leading-zero outsIDe; color:inherit; line-height:18px"> pg_typetp WHEREtp.oID=pr.prorettype ANDpr.proisagg=FALSE ANDtp.typname<>'trigger' ANDpr.pronamespaceSELECToID FROMpg_namespace liKE'pg_%' ANDnspname!='information_schema' SELECTroutine_name FROMinformation_schema.routines 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) RETURNSSetoFRECORDAS$$DECLARE rettypevarying; argtypesoIDvector; allargtypesoID[]; argmodes"char"[]; argnamestext[]; miniinteger; maxi /*getobjectIDoffunction*/ INTOrettype,argtypes,allargtypes,argmodes,argnames CASE WHENpg_proc.proretset THEN'setof'||pg_catalog.format_type(pg_proc.prorettype,NulL) 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 FROMpg_catalog.pg_proc JOINpg_catalog.pg_namespace ON(pg_proc.pronamespace=pg_namespace.oID) WHEREpg_proc.prorettype<>'pg_catalog.cstring'::pg_catalog.regtype AND(pg_proc.proargtypes[0]ORpg_proc.proargtypes[0]<>'pg_catalog.cstring'::pg_catalog.regtype) NOTpg_proc.proisagg ANDpg_proc.proname=funcname ANDpg_namespace.nspname=schema ANDpg_catalog.pg_function_is_visible(pg_proc.oID); /*bailoutifnotfound*/ IFNOTFOUNDRETURN; /*returnarowforthereturnvalue*/ pos=0; direction='o'::char; argname='RETURNVALUE'; datatype=rettype; RETURNNEXT; /*unfortunatelyallargtypesisNulLiftherearenoOUTparameters*/ IFallargtypesNulL mini=array_lower(argtypes,1);maxi=array_upper(argtypes,1); ELSE mini=array_lower(allargtypes,1);maxi=array_upper(allargtypes,248); line-height:18px"> IFmaxi<miniTHENRETURN; /*loopallthearguments*/ FORiINmini..maxiLOOP pos=i-mini+1; IFargnames argname=NulL; argname=argnames[pos]; direction='i':: datatype=pg_catalog.format_type(argtypes[i],128); background-color:inherit">NulL); direction=argmodes[i]; datatype=pg_catalog.format_type(allargtypes[i],153); background-color:inherit; Font-weight:bold">ENDLOOP; END;$$LANGUAGEplpgsqlStableSTRICTSecurityINVOKER; COMMENTFUNCTIONpublic.function_args(character IS$$Forafunctionnameandschema,thisprocedureselectsforeach argumentthefollowingdata: -positionintheargumentList(0returnvalue) -direction'i','o',128); background-color:inherit">or'b' -name(NulLifnotdefined) -datatype$$; copy CREATEORREPLACEFUNCTIONpublic.function_args( INfuncnamevarying,128); background-color:inherit">INschemaOUTposinteger,153); background-color:inherit; Font-weight:bold">OUTdirectioncharacter,153); background-color:inherit; Font-weight:bold">OUTargnameOUTdatatypevarying) RETURNSSetoFRECORDAS$$DECLARE rettypevarying; argtypesoIDvector; allargtypesoID[]; argmodes"char"[]; argnamestext[]; miniinteger; maxiinteger; /*getobjectIDoffunction*/ INTOrettype,argnames CASE WHENpg_proc.proretset THEN'setof'||pg_catalog.format_type(pg_proc.prorettype,128); background-color:inherit">NulL) 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 FROMpg_catalog.pg_proc JOINpg_catalog.pg_namespace ON(pg_proc.pronamespace=pg_namespace.oID) WHEREpg_proc.prorettype<>'pg_catalog.cstring'::pg_catalog.regtype AND(pg_proc.proargtypes[0]ORpg_proc.proargtypes[0]<>'pg_catalog.cstring'::pg_catalog.regtype) NOTpg_proc.proisagg ANDpg_proc.proname=funcname ANDpg_namespace.nspname=schema ANDpg_catalog.pg_function_is_visible(pg_proc.oID); /*bailoutifnotfound*/ IFNOTFOUNDRETURN; ENDIF; /*returnarowforthereturnvalue*/ pos=0; direction='o'::char; argname='RETURNVALUE'; datatype=rettype; RETURNNEXT; /*unfortunatelyallargtypesisNulLiftherearenoOUTparameters*/ IFallargtypesNulL IFmaxi<miniTHENRETURN; /*loopallthearguments*/ FORiINmini..maxiLOOP pos=i-mini+1; IFargnames argname=NulL; argname=argnames[pos]; IFallargtypesTHEN direction='i'::NulL); direction=argmodes[i]; datatype=pg_catalog.format_type(allargtypes[i],128); background-color:inherit">NulL); NEXT; ENDLOOP; END;$$LANGUAGEplpgsqlStableSTRICTSecurityINVOKER; COMMENTpublic.function_args(character IS$$Forafunctionnameandschema,153); background-color:inherit; Font-weight:bold">procedureselectsforeach argumentthefollowingdata: -positionintheargumentList(0returnvalue) -direction'i','o',128); background-color:inherit">or'b' -name(NulLifnotdefined) -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 FROMpg_procp JOINpg_typet1ONp.prorettype=t1.oID JOINpg_authIDaONp.proowner=a.oID JOINpg_languagelONp.prolang=l.oID WHEREproname=:PROCEDURE_name; 总结

以上是内存溢出为你收集整理的Postgresql 相关系统表查询全部内容，希望文章能够帮你解决Postgresql 相关系统表查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1178095.html