select tablename from pg_tables where schemaname='ap' and tablename SIMILAR TO 'dwd_[a-z,_]+_[0-9]+'
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的区别
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');