【数据库】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');