CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user14209525
user14209525

Reputation: 5

postgres - Passing variable to not like operator

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
$$;

Upvotes: 0

Views: 107

Answers (1)

user330315
user330315

Reputation:

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
$$;

Upvotes: 1

Related Questions