### [PostgreSQL函數:用以查詢某表的詳細 包含表字段的注釋信息](http://lovejuan1314.iteye.com/blog/1167680)** **博客分類：**?- [數據庫(Oracle/Postgresql/Hypertable等)](http://lovejuan1314.iteye.com/category/32183) ? 感謝PostgreSQL論壇阿弟? Sql代碼??[](# "收藏這段代碼") 1. CREATE?OR?REPLACE?FUNCTION?pgsql_type(a_type?varchar)?RETURNS?varchar?AS?? 1. $BODY$?? 1. DECLARE?? 1. ?????v_type?varchar;?? 1. BEGIN?? 1. ?????IF?a_type='int8'?THEN?? 1. ??????????v_type:='bigint';?? 1. ?????ELSIF?a_type='int4'?THEN?? 1. ??????????v_type:='integer';?? 1. ?????ELSIF?a_type='int2'?THEN?? 1. ??????????v_type:='smallint';?? 1. ?????ELSIF?a_type='bpchar'?THEN?? 1. ??????????v_type:='char';?? 1. ?????ELSE?? 1. ??????????v_type:=a_type;?? 1. ?????END?IF;?? 1. ?????RETURN?v_type;?? 1. END;?? 1. $BODY$?? 1. LANGUAGE?PLPGSQL;?? 1. ?? 1. CREATE?TYPE?"public"."tablestruct"?AS?(?? 1. ??"fields_key_name"?varchar(100),?? 1. ??"fields_name"?VARCHAR(200),?? 1. ??"fields_type"?VARCHAR(20),?? 1. ??"fields_length"?BIGINT,?? 1. ??"fields_not_null"?VARCHAR(10),?? 1. ??"fields_default"?VARCHAR(500),?? 1. ??"fields_comment"?VARCHAR(1000)?? 1. );?? 1. ?? 1. CREATE?OR?REPLACE?FUNCTION?"public"."table_msg"?(a_schema_name?varchar,?a_table_name?varchar)?RETURNS?SETOF?"public"."tablestruct"?AS?? 1. $body$?? 1. DECLARE?? 1. ?????v_ret?tablestruct;?? 1. ?????v_oid?oid;?? 1. ?????v_sql?varchar;?? 1. ?????v_rec?RECORD;?? 1. ?????v_key?varchar;?? 1. BEGIN?? 1. ?????SELECT?? 1. ???????????pg_class.oid??INTO?v_oid?? 1. ?????FROM?? 1. ???????????pg_class?? 1. ???????????INNER?JOIN?pg_namespace?ON?(pg_class.relnamespace?=?pg_namespace.oid?AND?lower(pg_namespace.nspname)?=?a_schema_name)?? 1. ?????WHERE?? 1. ???????????pg_class.relname=a_table_name;?? 1. ?????IF?NOT?FOUND?THEN?? 1. ?????????RETURN;?? 1. ?????END?IF;?? 1. ?? 1. ?????v_sql='?? 1. ?????SELECT?? 1. ???????????pg_attribute.attname?AS?fields_name,?? 1. ???????????pg_attribute.attnum?AS?fields_index,?? 1. ???????????pgsql_type(pg_type.typname::varchar)?AS?fields_type,?? 1. ???????????pg_attribute.atttypmod-4?as?fields_length,?? 1. ???????????CASE?WHEN?pg_attribute.attnotnull??THEN?''not?null''?? 1. ???????????ELSE?''''?? 1. ???????????END?AS?fields_not_null,?? 1. ???????????pg_attrdef.adsrc?AS?fields_default,?? 1. ???????????pg_description.description?AS?fields_comment?? 1. ?????FROM?? 1. ???????????pg_attribute?? 1. ???????????INNER?JOIN?pg_class??ON?pg_attribute.attrelid?=?pg_class.oid?? 1. ???????????INNER?JOIN?pg_type???ON?pg_attribute.atttypid?=?pg_type.oid?? 1. ???????????LEFT?OUTER?JOIN?pg_attrdef?ON?pg_attrdef.adrelid?=?pg_class.oid?AND?pg_attrdef.adnum?=?pg_attribute.attnum?? 1. ???????????LEFT?OUTER?JOIN?pg_description?ON?pg_description.objoid?=?pg_class.oid?AND?pg_description.objsubid?=?pg_attribute.attnum?? 1. ?????WHERE?? 1. ???????????pg_attribute.attnum?>?0?? 1. ???????????AND?attisdropped?<>?''t''?? 1. ???????????AND?pg_class.oid?=?'?||?v_oid?||?'?? 1. ?????ORDER?BY?pg_attribute.attnum'?;?? 1. ?? 1. ?????FOR?v_rec?IN?EXECUTE?v_sql?LOOP?? 1. ?????????v_ret.fields_name=v_rec.fields_name;?? 1. ?????????v_ret.fields_type=v_rec.fields_type;?? 1. ?????????IF?v_rec.fields_length?>?0?THEN?? 1. ????????????v_ret.fields_length:=v_rec.fields_length;?? 1. ?????????ELSE?? 1. ????????????v_ret.fields_length:=NULL;?? 1. ?????????END?IF;?? 1. ?????????v_ret.fields_not_null=v_rec.fields_not_null;?? 1. ?????????v_ret.fields_default=v_rec.fields_default;?? 1. ?????????v_ret.fields_comment=v_rec.fields_comment;?? 1. ?????????SELECT?constraint_name?INTO?v_key?FROM?information_schema.key_column_usage?WHERE?table_schema=a_schema_name?AND?table_name=a_table_name?AND?column_name=v_rec.fields_name;?? 1. ?????????IF?FOUND?THEN?? 1. ????????????v_ret.fields_key_name=v_key;?? 1. ?????????ELSE?? 1. ????????????v_ret.fields_key_name='';?? 1. ?????????END?IF;?? 1. ?????????RETURN?NEXT?v_ret;?? 1. ?????END?LOOP;?? 1. ?????RETURN?;?? 1. END;?? 1. $body$?? 1. LANGUAGE?'plpgsql'?VOLATILE?CALLED?ON?NULL?INPUT?SECURITY?INVOKER;?? 1. ?? 1. COMMENT?ON?FUNCTION?"public"."table_msg"(a_schema_name?varchar,?a_table_name?varchar)?? 1. IS?'獲得表信息';?? 1. ?? 1. ---重載一個函數?? 1. CREATE?OR?REPLACE?FUNCTION?"public"."table_msg"?(a_table_name?varchar)?RETURNS?SETOF?"public"."tablestruct"?AS?? 1. $body$?? 1. DECLARE?? 1. ????v_ret?tablestruct;?? 1. BEGIN?? 1. ????FOR?v_ret?IN?SELECT?*?FROM?table_msg('public',a_table_name)?LOOP?? 1. ????????RETURN?NEXT?v_ret;?? 1. ????END?LOOP;?? 1. ????RETURN;?? 1. END;?? 1. $body$?? 1. LANGUAGE?'plpgsql'?VOLATILE?CALLED?ON?NULL?INPUT?SECURITY?INVOKER;?? 1. ?? 1. COMMENT?ON?FUNCTION?"public"."table_msg"(a_table_name?varchar)?? 1. IS?'獲得表信息';?? Sql代碼??[](# "收藏這段代碼") 1. 例子?? 1. ?? 1. CREATE?TABLE?"public"."login_log"?(?? 1. ??"auto_bh"?BIGSERIAL,??? 1. ??"yhm"?VARCHAR(20)?NOT?NULL,??? 1. ??"dlsj"?TIMESTAMP?WITHOUT?TIME?ZONE?DEFAULT?date_trunc('second'::text,?('now'::text)::timestamp(6)?with?time?zone)?NOT?NULL,??? 1. ??"dlip"?VARCHAR(30)?NOT?NULL,??? 1. ??"dcsj"?TIMESTAMP?WITHOUT?TIME?ZONE?DEFAULT?date_trunc('second'::text,?('now'::text)::timestamp(6)?with?time?zone)?NOT?NULL,??? 1. ??"zc"?CHAR(4)?NOT?NULL,??? 1. ??"zxsj"?VARCHAR(20)?DEFAULT?'00:00:00'::character?varying,??? 1. ??CONSTRAINT?"login_log_pkey"?PRIMARY?KEY("auto_bh",?"dlsj")?? 1. )?WITH?OIDS;?? 1. ?? 1. COMMENT?ON?TABLE?"public"."login_log"?? 1. IS?'用戶登錄日記檔';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."auto_bh"?? 1. IS?'系統編號';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."yhm"?? 1. IS?'用戶名';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."dlsj"?? 1. IS?'登錄時間';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."dlip"?? 1. IS?'登錄IP';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."dcsj"?? 1. IS?'登出時間';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."zc"?? 1. IS?'進出';?? 1. ?? 1. COMMENT?ON?COLUMN?"public"."login_log"."zxsj"?? 1. IS?'在線時間';?? 1. ?? 1. select?*?from?table_msg('public','login_log');?? 1. 或?? 1. select?*?from?table_msg('login_log');?? 1. ?? 1. 結果?? 1. fields_key_name?|?fields_name?|?fields_type?|?fields_length?|?fields_not_null?|?????????????????????????????fields_default?????????????????????????????|?fields_comment?? 1. -----------------+-------------+-------------+---------------+-----------------+------------------------------------------------------------------------+----------------?? 1. ?login_log_pkey??|?auto_bh?????|?bigint??????|???????????????|?not?null????????|?nextval('public.login_log_auto_bh_seq'::text)??????????????????????????|?系統編號?? 1. ?????????????????|?yhm?????????|?varchar?????|????????????20?|?not?null????????|????????????????????????????????????????????????????????????????????????|?用戶名?? 1. ?????????????????|?dlsj????????|?timestamp???|???????????????|?not?null????????|?date_trunc('second'::text,?('now'::text)::timestamp(6)?with?time?zone)?|?登錄時間?? 1. ?????????????????|?dlip????????|?varchar?????|????????????30?|?not?null????????|????????????????????????????????????????????????????????????????????????|?登錄IP?? 1. ?????????????????|?dcsj????????|?timestamp???|???????????????|?not?null????????|?date_trunc('second'::text,?('now'::text)::timestamp(6)?with?time?zone)?|?登出時間?? 1. ?????????????????|?zc??????????|?char????????|?????????????4?|?not?null????????|????????????????????????????????????????????????????????????????????????|?進出?? 1. ?????????????????|?zxsj????????|?varchar?????|????????????20?|?????????????????|?'00:00:00'::character?varying??????????????????????????????????????????|?在線時間?? 1. (7?rows)?? 分享到：?[](# "分享到新浪微博")?[](# "分享到騰訊微博")