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')
See Question&Answers more detail:os