【数据库】PostgreSQL/PgSql-根据模式名和字段名查询有该字段的所有表信息【通过表元数据信息和函数实现】...
哥们要飞
于 2022-08-23 14:51:00 发布
304
收藏
文章标签： 数据库 postgresql
版权
一、利用表数据信息查询表和字段信息
(一)从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');
————————————————
版权声明：本文为CSDN博主「哥们要飞」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/USTSD/article/details/126487656