postgres - Passing variable to not like operator

Question

We have tables name with Date fields, Like table_name_yyyy_mm_dd. Wanted to analyze tables older than two days using procedure and come up with below proc. But Looks like select command with not like expression is not picking up the tables older than two days, instead its picking all tables. what am i doing wrong. Please suggest.

DO $$
DECLARE
  tab RECORD;
  schemaNa VARCHAR := 'test';
  _now_date varchar := TO_CHAR((NOW()-2) :: DATE, 'yyyy_mm_dd') ;
BEGIN
  for tab in (select relname AS table_name from  pg_stat_user_tables where schemaname='test' and relname not like '%_now_date%' order by 1)
  LOOP
    RAISE NOTICE 'ANALYZE %1.%2', schemaNa, tab.table_name;
    RAISE NOTICE '%',_now_date;
    EXECUTE 'ANALYZE '||schemaNa||'.'||tab.table_name;
  end loop;
end
$$;

user330315 · Accepted Answer · 2020-09-02 15:25:42Z

Don't enclose the variable name in single quotes. It's also better to use format() when generating dynamic SQL

DO $$
DECLARE
  tab RECORD;
  l_schema VARCHAR := 'test';
  l_sql text;
  _now_date varchar := TO_CHAR(current_date - 2, 'yyyy_mm_dd');
BEGIN
  for tab in (select relname AS table_name 
              from  pg_stat_user_tables 
              where schemaname = l_schema
              and relname not like '%'||_now_date||'%'
              order by 1)
  LOOP
    l_sql := format('ANALYZE %I.%I', l_schema, tab.table_name); 
    RAISE NOTICE '%', l_sql;
    RAISE NOTICE '%',_now_date;
    EXECUTE l_sql;
  end loop;
end
$$;

Collectives™ on Stack Overflow

postgres - Passing variable to not like operator

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related