postgres- plsql function to get rows counts for specific columns with null data

Question

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?

SQL Error [42601]: ERROR: syntax error at or near "null"
  Where: PL/pgSQL function inline_code_block line 14 at EXECUTE

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.

Answer 1

There is a lot to improve, especially the usage of format(). In your current approach your query will still fail when a parameter isn't in standard PostgreSQL format or even prone to SQL injection. You didn't use placeholders and the correct types.

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 = l_schema -- Correct?
                      AND is_nullable = 'NO'
                      AND data_type NOT IN ('integer')
                      AND column_default IS NULL
                    ORDER BY table_name, column_name)
            LOOP
                -- use placeholders for Identifiers:
                l_sql := FORMAT('SELECT COUNT(1) FROM %I.%I WHERE %I IS NULL;'
                            , l_schema -- Correct?
                            , tab.table_name
                            , tab.column_name
                         );
                RAISE NOTICE '%', l_sql;
                EXECUTE l_sql INTO rse_row_count;
                l1_sql := FORMAT('INSERT INTO RSE_TABLE_COUNT (TABLE_NAME, Table_column_name,ROW_COUNT, DATE_LAST_UPDATED)
                        VALUES  (%L,%L,%L, %L);'
                            , tab.table_name
                            , tab.column_name
                            , rse_row_count
                            , NOW()
                          );
                RAISE NOTICE '%', l1_sql;
                EXECUTE l1_sql;
            END LOOP;
    END;
$$;