Question 395059 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    @Paul T. correctly shows first issue – missing space between column name and IS keyword.

    This way of using values for dynamic SQL is possibly dangerous (sql injection vulnerable). You should to use function format with correct placeholdres or function quote_ident. You use function format, but not correctly:

    l_sql := format('SELECT COUNT(1) FROM %I where %I is null', tab.table_name, tab.column_name);

    I see another problem – Postgres has not function sysdate. Use current_date instead.

    Login or Signup to reply.
  2. 0

    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;
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search