i need to get rows counts for specific columns with null and insert the same rows count output into a table with result set of the query. For this requirement, i tried with below pl/sql code block in postgres but its not working as i am getting following error. Can someone suggest what i am doing wrong here?
<code>SQL Error [42601]: ERROR: syntax error at or near "null"
Where: PL/pgSQL function inline_code_block line 14 at EXECUTE
</code>
<code>SQL Error [42601]: ERROR: syntax error at or near "null"
Where: PL/pgSQL function inline_code_block line 14 at EXECUTE
</code>
SQL Error [42601]: ERROR: syntax error at or near "null"
Where: PL/pgSQL function inline_code_block line 14 at EXECUTE
<code>DO $$
DECLARE
tab RECORD;
l_schema VARCHAR := 'test';
l_sql text;
l1_sql text;
RSE_ROW_COUNT int;
BEGIN
for tab in (select table_name,column_name from INFORMATION_SCHEMA.columns where table_schema='public' and is_nullable='NO' and data_type not in ('integer')
and column_default is null order by table_name,column_name)
LOOP
l_sql := format('SELECT COUNT(1) FROM ' || tab.table_name || 'where ' || tab.column_name || 'is null');
RAISE NOTICE '%', l_sql;
EXECUTE l_sql INTO RSE_ROW_COUNT;
l1_sql := 'INSERT INTO RSE_TABLE_COUNT (TABLE_NAME, Table_column_name,ROW_COUNT, DATE_LAST_UPDATED)
VALUES ( '|| tab.table_name ||','|| tab.column_name ||','|| rse_row_count ||','|| 'SYSDATE)';
RAISE NOTICE '%', l1_sql;
EXECUTE l1_sql;
end loop;
end
$$;
</code>
<code>DO $$
DECLARE
tab RECORD;
l_schema VARCHAR := 'test';
l_sql text;
l1_sql text;
RSE_ROW_COUNT int;
BEGIN
for tab in (select table_name,column_name from INFORMATION_SCHEMA.columns where table_schema='public' and is_nullable='NO' and data_type not in ('integer')
and column_default is null order by table_name,column_name)
LOOP
l_sql := format('SELECT COUNT(1) FROM ' || tab.table_name || 'where ' || tab.column_name || 'is null');
RAISE NOTICE '%', l_sql;
EXECUTE l_sql INTO RSE_ROW_COUNT;
l1_sql := 'INSERT INTO RSE_TABLE_COUNT (TABLE_NAME, Table_column_name,ROW_COUNT, DATE_LAST_UPDATED)
VALUES ( '|| tab.table_name ||','|| tab.column_name ||','|| rse_row_count ||','|| 'SYSDATE)';
RAISE NOTICE '%', l1_sql;
EXECUTE l1_sql;
end loop;
end
$$;
</code>
DO $$
DECLARE
tab RECORD;
l_schema VARCHAR := 'test';
l_sql text;
l1_sql text;
RSE_ROW_COUNT int;
BEGIN
for tab in (select table_name,column_name from INFORMATION_SCHEMA.columns where table_schema='public' and is_nullable='NO' and data_type not in ('integer')
and column_default is null order by table_name,column_name)
LOOP
l_sql := format('SELECT COUNT(1) FROM ' || tab.table_name || 'where ' || tab.column_name || 'is null');
RAISE NOTICE '%', l_sql;
EXECUTE l_sql INTO RSE_ROW_COUNT;
l1_sql := 'INSERT INTO RSE_TABLE_COUNT (TABLE_NAME, Table_column_name,ROW_COUNT, DATE_LAST_UPDATED)
VALUES ( '|| tab.table_name ||','|| tab.column_name ||','|| rse_row_count ||','|| 'SYSDATE)';
RAISE NOTICE '%', l1_sql;
EXECUTE l1_sql;
end loop;
end
$$;
Tried multiple options but unable to fix it.