plpgsql - postgresql: use timestamp variable within pl pgsql

Question

Im new using pl pgsql. I want to concatenate two variables but im gotting always same error: time_ variable is not known

Let's say that date_ is of type date and time_ is of type time. The error came from this row:

sum(extract(epoch from (least(s.end, gs.date_+time_) - greatest(s.beg, gs.date_))) / 60) as Timing

My code is below

delcare 
  time_ time;
Begin
   execute $$SELECT CURRENT_TIMESTAMP::time FROM $$||result_table INTO time_;
   execute $$SELECT MAX(date_) FROM $$||result_table INTO max_date;
   IF max_date is not NULL THEN
       execute $$DELETE FROM $$||result_table||$$ WHERE date_ >= $$||quote_literal(max_date);
   ELSE
       max_date := 'XXXXXXX';
   end if;
   execute $$
   INSERT INTO $$result_table$$
   (Id, gs.date_, TIME, timing)
   SELECT * from (
      select
      Id, gs.date_,
      (case
      When TRIM(set) ~ '^OPT[0-9]{3}/MINUTE/$'
      Then 'minute'
      When TRIM(set) ~ '^OPT[0-9]{3}/SECOND/$'
      Then 'second' as TIME, 
       
       sum(extract(epoch from (least(s.end, gs.date_+time_) -
                   greatest(s.beg, gs.date_)
                  )
      ) / 60) as Timing
  from source s cross join lateral
  generate_series(date_trunc('day', s.beg), date_trunc('day',
       least(s.end,
             CASE WHEN $$||quote_literal(max_date)||$$ = 'XXXXXXX'
             THEN (current_date)
             ELSE $$||quote_literal(max_date)||$$
             END)
   ), interval '1 day') gs(date_)
 where ( (beg, end) overlaps ($$||quote_literal(max_date)||$$'00:00:00', $$||quote_literal(max_date)||$$'23:59:59'))
 group by id, gs.date_, TIME
 ) as X
 where ($$||quote_literal(max_date)||$$ = X.date_  and $$||quote_literal(max_date)||$$ != 'XXXXXXX')
 OR  ($$||quote_literal(max_date)||$$ ='XXXXXXX')

Answer 1

Dynamic SQL should be generated through format() and parameters should not be passed as string literals, but through placeholders and using.

Your code is really hard to read, incomplete and there are some substantial syntax errors which stem from e.g. a missing END for the CASE and parentheses not properly paired. So the following code might still contain some errors as I apparently have no way of testing it.

But as your main SELECT does not seem to use dynamic SQL at all, all the quote_literal() and string concatenation is unnecessary, just use the variables directly.

As max_date is supposed to be a date value you can assign the string 'XXXXX' to it, but if you use the max_date directly, you can get rid of that check as far as I can tell.

declare 
  time_ time;
  max_date date;
  result_table text := 'contract_frequency';
  table_schema text := 'public';
Begin
   time_ := localtime; 
   execute format('SELECT MAX(date_) FROM %I.%I', table_schema, result_table) into INTO max_date;
   
   IF max_date is not NULL THEN
     execute format('DELETE FROM %I.%I WHERE date_ >= $1', table_schema, result_table) using max_date;
   ELSE
     -- you replace XXXX with current_date in the CASE expression 
     -- later on, so using current_date here seems the right thing to do
     max_date := current_date;
   end if;

   SELECT * 
   from (
      select
      Id, gs.date_,
      case 
        When TRIM(set) ~ '^OPT[0-9]{3}/MINUTE/$' Then 'minute'
        When TRIM(set) ~ '^OPT[0-9]{3}/SECOND/$' Then 'second' as TIME, 
      end
      sum(extract(epoch from (least(s.end, gs.date_+time_) - greatest(s.beg, gs.date_) ) ) / 60) as Timing
    from source s 
    cross join lateral 
      generate_series(date_trunc('day', s.beg), date_trunc('day', least(s.end, max_date)), interval '1 day') gs(date_)
    where (beg, end) overlaps (max_date::timestamp, max_date + time '23:59:59') 
    group by id, gs.date_, TIME
  ) as X
  where (max_date = X.date_ and max_date <> current_date)
    OR  (max_date = current_date)

end;