【数据库】PostgreSQL/PgSql-根据模式名和字段名查询有该字段的所有表信息【通过表元数据信息和函数实现】

时间：2022-11-04 07:57:11

一、利用表数据信息查询表和字段信息

（一）从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');

 

作者：​​哥们要飞​​



标签：

相关文章