69 lines
3.5 KiB
PL/PgSQL
Executable File
69 lines
3.5 KiB
PL/PgSQL
Executable File
--drop function if exists fn_info_column_dq();
|
|
|
|
CREATE OR REPLACE function fn_info_column_dq(schemaname text)
|
|
RETURNS void AS $$
|
|
declare
|
|
statements text;
|
|
begin
|
|
/************************************************************
|
|
Function Name:fn_info_column_dq
|
|
Function Desc: This function populates data into given schema. This function creates temporary table which stores SQL queires
|
|
and later through for loop each temporary sql statement is executed.
|
|
Function concept: uses concat method to create a insert statement, 2 different statements are provided (choose any).
|
|
Creation Date: 06-June-2021
|
|
Updation Date:
|
|
Author: compegence team
|
|
Function Call: select fn_info_column_dq('transactional');
|
|
Data Access: select * from info_column_dq;
|
|
***************************************************************/
|
|
-- Accessing information from information_schema and computing the required data
|
|
-- Storing the data in config db table
|
|
-- Execute this if the table does not exits or when the db is new
|
|
execute 'SET search_path to '''||schemaname||'''';
|
|
|
|
DROP table if exists info_column_dq;
|
|
create table info_column_dq (
|
|
table_schema text null,
|
|
table_name text NULL,
|
|
column_name text NULL,
|
|
column_data_type text NULL,
|
|
column_unique_count int null,
|
|
column_min_value text null,
|
|
column_max_value text null,
|
|
column_null_count int null,
|
|
create_timestamp timestamp not NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
-- Temporary table to store queries : create
|
|
--drop table if exists queries;
|
|
create temp table queries (sqlquery text null);
|
|
-- Temporary table to store queries : insert data
|
|
insert into queries(sqlquery)
|
|
select
|
|
-- without using time-stamp::Date:
|
|
concat('insert into info_column_dq(table_schema,table_name,column_name,column_data_type,column_unique_count,column_min_value,column_max_value,column_null_count) select ''',c.table_schema,''',''',c.table_name,''',''', c.column_name,''',''', c.data_type,''',','count(distinct ',c.column_name,'),','min(',c.column_name,'),','max(',c.column_name,'),','SUM(CASE WHEN ',c.column_name ,' IS NULL THEN 1 ELSE 0 END) from ',c.table_schema,'.',c.table_name,';')
|
|
-- with using time-stamp::Date:
|
|
--concat('insert into info_column_dq(table_schema,table_name,column_name,column_data_type,column_unique_count,column_min_value,column_max_value,column_null_count) select ''',c.table_schema,''',''',c.table_name,''',''', c.column_name,''',''', c.data_type,''',','count(distinct ',c.column_name,'),','min(',c.column_name,'),','max(',c.column_name,'),','SUM(CASE WHEN ',c.column_name ,' IS NULL THEN 1 ELSE 0 END) from ',c.table_schema,'.',c.table_name,' where create_timestamp :: date >= now() ::Date;')
|
|
from information_schema."columns" c
|
|
where c.table_schema in (''||schemaname||'')
|
|
and c.table_name != 'info_column_dq'
|
|
and not c.data_type in ('bytea','boolean');
|
|
--and c.table_name !='test_instance_images'
|
|
--and not c.column_name in ('create_timestamp','update_timestamp');
|
|
-- executing the stored queries from temporary queries table: Loop the column data and execute each statement
|
|
for statements in select sqlquery from queries
|
|
loop
|
|
execute(statements);
|
|
end loop;
|
|
end;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
|
|
|
|
|
|
/*
|
|
select fn_info_column_dq('transactional');
|
|
select * from info_column_dq;
|
|
--gives error
|
|
--insert into fw_core.info_column_dq(table_schema,table_name,column_name,column_data_type,column_unique_count,column_min_value,column_max_value) select 'transactional','test_instance_images','image','bytea',count(distinct image),min(image),max(image) from transactional.test_instance_images;
|
|
*/ |