### [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;??