foreach x [list table_1 view_1] {
    spi_exec -array V "SELECT relname, pg_class.relkind as relkind FROM pg_class, pg_namespace \
      WHERE pg_class.relnamespace=pg_namespace.oid \
        AND pg_class.relkind IN ('v', 'r') \
        AND pg_namespace.nspname='user_$1' \
        AND relname = '$unit\_$x'" {
      if [info exists V(relname)] {
        if {$V(relkind) eq "v"} {
          spi_exec "DROP VIEW  user_$1.$V(relname) CASCADE"
        }
        if {$V(relkind) eq "r"} {
          spi_exec "DROP TABLE  user_$1.$V(relname) CASCADE"
        }
      }
    }
  }

CREATE OR REPLACE FUNCTION reports.build_sales_checks(int4)
  RETURNS bool AS
$BODY$
 
  set unit report_sales_checks
  set session_table "user_$1.session"
  set db_prefix "user_$1.$unit\_"
 
  spi_exec "DELETE from $session_table where unit='$unit' and var IN ('db_log', 'db_sale_filter', 'db_user_filter', 'db_date_filter')"
  # drop views
  foreach x [list checks expand_checks] {
    spi_exec -array V "SELECT relname, pg_class.relkind as relkind FROM pg_class, pg_namespace \
      WHERE pg_class.relnamespace=pg_namespace.oid \
        AND pg_class.relkind IN ('v', 'r') \
        AND pg_namespace.nspname='user_$1' \
        AND relname = '$unit\_$x'" {
      if [info exists V(relname)] {
        if {$V(relkind) eq "v"} {
          spi_exec "DROP VIEW  user_$1.$V(relname) CASCADE"
        }
        if {$V(relkind) eq "r"} {
          spi_exec "DROP TABLE  user_$1.$V(relname) CASCADE"
        }
      }
    }
  }
 
  set sql_get {SELECT value from $session_table where unit='$unit' and var='$var'}
 
  set vars [list macroregion region dealer subdealer category cluster trademark]
  set sale_filter true
  foreach var $vars {
    if {![ spi_exec [subst $sql_get]]} {
      set log [quote "Ошибка: Не найден элемент $var в переменных сессии модуля $unit"]
      spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_log', '$log')"
      return false
    } 
    if {$value ne ""} {
      append sale_filter " and $var IN ([joinquote $value])"
    }
  }
  spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_sale_filter', '[quote $sale_filter]')"
 
  set var user
  set user_filter true
  if {![ spi_exec [subst $sql_get]]} {
      set log [quote "Ошибка: Не найден элемент $var в переменных сессии модуля $unit"]
      spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_log', '$log')"
      return false
  }
  if {$value ne ""} {
    set user_filter "name IN ([joinquote $value])"
  }
  spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_user_filter', '[quote $user_filter]')"
 
  set vars [list date_from date_to]
  set sql_get {SELECT value from $session_table where unit='$unit' and var='$var'}
  foreach var $vars {
    if {![ spi_exec [subst $sql_get]]} {
      set log [quote "Ошибка: Не найден элемент $var в переменных сессии модуля $unit"]
      spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_log', '$log')"
      return false
    } 
    if {$value ne ""} {
      set $var [quote $value]
    } else {
      set log [quote "Ошибка: Пустое значение элемента $var в переменных сессии модуля $unit"]
      spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_log', '$log')"
      return false
    }
  }
 
  # если дошли до этого места, фильтры по т/т и пользователю успешно созданы, можно начинать генерацию таблиц отчета
 
  spi_exec "create view ${db_prefix}checks as 
    select distinct check_date, object_id, user_id
    from cache.checks d 
    where d.check_date between '$date_from'::date and '$date_to'::date 
    and d.object_id IN (select id from merch.objects_sales where $sale_filter) 
    and d.user_id   IN (select id from auth.users       where $user_filter)"
 
  spi_exec "CREATE view ${db_prefix}expand_checks AS
SELECT s.id as object_id, macroregion, region, case when s.code='' then 'Удалена' else s.code end as code,
    town, address, dealer, subdealer, category, cluster,
    trademark, person, phone, u.id as user_id, u.name as user,
    check_date
FROM ${db_prefix}checks c
left join merch.objects_sales s on c.object_id=s.id
left join auth.users u on c.user_id  =u.id"
 
  # таблицы отчета построены, возвращаем управление
  spi_exec "INSERT INTO $session_table (unit, var, value) values ('$unit', 'db_log', '')"
  return true
 
$BODY$
  LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;