【数据库】PostgreSQL/PgSql-根据模式名和字段名查询有该字段的所有表信息【通过表元数据信息和函数实现】
2022/8/24 2:22:55
本文主要是介绍【数据库】PostgreSQL/PgSql-根据模式名和字段名查询有该字段的所有表信息【通过表元数据信息和函数实现】,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、利用表数据信息查询表和字段信息
(一)从pg_tables中查询表信息
select tablename from pg_tables where schemaname='ap' and tablename SIMILAR TO 'dwd_[a-z,_]+_[0-9]+'
(二)从pg_class和pg_attribute根据指定的表名查询字段信息
SELECT C.relname, A.attname AS NAME, A.attnotnull AS NOTNULL, format_type ( A.atttypid, A.atttypmod ) AS TYPE, col_description ( A.attrelid, A.attnum ) AS COMMENT FROM pg_class AS C, pg_attribute AS A WHERE C.relname = 'table_name' AND A.attrelid = C.oid AND A.attnum > 0
二、函数创建步骤
(一)创建函数
CREATE OR REPLACE FUNCTION ods.find_table_by_column_and_schema() RETURNS "pg_catalog"."void" AS $BODY$ DECLARE loop_index integer; BEGIN loop_index=1; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
(二)函数调用
select ods.find_table_by_column_and_schema(10);
(三) 输入参数
CREATE OR REPLACE FUNCTION ods.find_table_by_column_and_schema(in_param_schema integer) RETURNS "pg_catalog"."void" AS $BODY$ DECLARE loop_index integer; BEGIN loop_index=1; loop_index=loop_index+in_param_schema; RAISE notice '表名为:%',loop_index; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
(四)删除已创建的函数
DROP FUNCTION find_table_by_column_and_schema(integer)
(五)输出及打印参数
CREATE OR REPLACE FUNCTION ods.find_table_by_column_and_schema( in_param_schema integer, OUT out_table_list integer) RETURNS integer AS $BODY$ DECLARE loop_index integer; BEGIN loop_index=1; loop_index=loop_index+in_param_schema; RAISE notice '表名为:%',loop_index; out_table_list=loop_index; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
(六)测试数组
CREATE OR REPLACE FUNCTION ods.find_table_by_column_and_schema( in_param_schema integer, OUT out_table_list character varying[]) RETURNS character varying[] AS $BODY$ DECLARE loop_index integer; BEGIN loop_index=1; loop_index=loop_index+in_param_schema; RAISE notice '表名为:%',loop_index; out_table_list[0]=loop_index; out_table_list[1]=loop_index+1; out_table_list[2]=concat(out_table_list[1],cast(1 as character varying)); END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
问题:character varying[]和character、varchar的区别
(七)测试与SQL交互
CREATE OR REPLACE FUNCTION ods.find_table_by_column_and_schema( in_param_schema integer, OUT out_table_list character varying[]) RETURNS character varying[] AS $BODY$ DECLARE loop_index integer; BEGIN loop_index=1; loop_index=loop_index+in_param_schema; RAISE notice '表名为:%',loop_index; out_table_list[0]=loop_index; out_table_list[1]=loop_index+1; out_table_list[2]=( select count(*) from ap.fact_ito ); END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100
三、最终结果
(一)函数内容
CREATE OR REPLACE FUNCTION ods.find_table_by_column_and_schema( in_param_schema varchar, in_param_column varchar, OUT out_table_list character varying[]) RETURNS character varying[] LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE loop_index integer; row_record VARCHAR(200); arr_length integer; BEGIN loop_index = 1; FOR row_record IN( select tablename from pg_tables where schemaname=in_param_schema ) LOOP IF (SELECT count(*) FROM (SELECT C.relname, A.attname AS column_name, A.attnotnull AS NOTNULL, format_type ( A.atttypid, A.atttypmod ) AS TYPE, col_description ( A.attrelid, A.attnum ) AS COMMENT FROM pg_class AS C, pg_attribute AS A WHERE C.relname = row_record AND A.attrelid = C.oid AND A.attnum > 0 ) REF where column_name=in_param_column)>0 THEN out_table_list[loop_index]=row_record; loop_index=loop_index+1; END IF; END LOOP; END; $BODY$;
(二)调用方式
select ods.find_table_by_column_and_schema('ods','fbillno');
这篇关于【数据库】PostgreSQL/PgSql-根据模式名和字段名查询有该字段的所有表信息【通过表元数据信息和函数实现】的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格,让你的数据库重新焕然一新!
- 2024-01-04在PostgreSQL中创建角色:判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL:修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数