【PSQL】获取不同Schema下对应表信息的存储过程public.get_table_info

【PSQL】获取不同Schema下对应表信息的存储过程public.get_table_info

存储过程源码

CREATE OR REPLACE FUNCTION public.get_table_info(tableschema varchar(20),tablename varchar(200)) returns TABLE(TABLE_NAME varchar(100),TABLE_NAME_ZH varchar(200),COL_NAME varchar(100),REMARK varchar(200),COL_NUM INT,COL_TYPE varchar(100),COL_LEN INT,COL_PREC INT,COL_TIME_PREC INT,COL_NULLABLE varchar(5),COL_DEFAULT varchar(100))AS $$/*脚本用途： 获取表信息 脚本生成时间: 2021-07-31 脚本开发人: wel 参数说明: tableschema：是对应shema的名称，支持大、小写或者大小写混合, tablename: 表名称，支持大、小写或者大小写混合使用示例：select * FROM public.get_table_info('pg_catalog','pg_database'); *//***************************************************************************************************/begin RETURN QUERY SELECT CAST(T1.TABLE_NAME AS varchar(100)), CAST(T2.TABLE_NAME_ZH AS varchar(200)), CAST(T1.COL_NAME AS varchar(100)), CAST(T1.REMARK AS varchar(200)), CAST(T1.COL_NUM AS INTEGER), CAST(T1.COL_TYPE AS varchar(100)), CAST(T1.COL_LEN AS INTEGER), CAST(T1.COL_PREC AS INTEGER), CAST(T1.COL_TIME_PREC AS INTEGER), CAST(T1.COL_NULLABLE AS varchar(5)), CAST(T1.COL_DEFAULT AS varchar(100)) FROM (SELECT AA.TABLE_NAME, AA.COL_NUM, AA.COL_NAME, AA.COL_TYPE, AA.COL_LEN, AA.COL_PREC, AA.COL_TIME_PREC, AA.COL_NULLABLE, AA.COL_DEFAULT, BB.CLASSOID, BB.REMARK FROM (SELECT A.TABLE_NAME, A.ORDINAL_POSITION AS COL_NUM, A.COLUMN_NAME AS COL_NAME, A.DATA_TYPE AS COL_TYPE, A.CHARACTER_MAXIMUM_LENGTH AS COL_LEN, A.NUMERIC_PRECISION AS COL_PREC, A.DATETIME_PRECISION AS COL_TIME_PREC, A.IS_NULLABLE AS COL_NULLABLE, A.COLUMN_DEFAULT AS COL_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS A WHERE A.TABLE_SCHEMA = lower(tableschema) AND A.TABLE_NAME = lower(tablename)) AS AA LEFT JOIN (SELECT CLASSOID, OBJSUBID, DESCRIPTION AS REMARK FROM PG_DESCRIPTION WHERE OBJOID = (SELECT RELID FROM PG_STAT_ALL_TABLES WHERE SCHEMANAME = lower(tableschema) AND RELNAME = lower(tablename)) AS BB ON AA.COL_NUM = BB.OBJSUBID) T1 LEFT JOIN (SELECT T.CLASSOID, T.DESC AS TABLE_NAME_ZH FROM (SELECT CLASSOID, OBJSUBID, DESCRIPTION AS DESC FROM PG_DESCRIPTION WHERE OBJOID = (SELECT RELID FROM PG_STAT_ALL_TABLES WHERE SCHEMANAME = lower(tableschema) AND RELNAME = lower(tablename))) T WHERE OBJSUBID = 0) T2 ON T1.CLASSOID = T2.CLASSOID ORDER BY T1.COL_NUM ASC ;end;$$ LANGUAGE plpgsql;

运行效果展示：

select * FROM public.get_table_info('pg_catalog','pg_database');

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。