postgresql:在 pl pgsql 中使用时间戳变量 [英] postgresql: use timestamp variable within pl pgsql
问题描述
我是使用 pl pgsql 的新手.我想连接两个变量,但我总是遇到相同的错误:time_ 变量未知
Im new using pl pgsql. I want to concatenate two variables but im gotting always same error: time_ variable is not known
假设 date_ 是日期类型,而 time_ 是时间类型.错误来自这一行:
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
我的代码在下面
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')
推荐答案
动态 SQL 应通过 format()
生成,参数不应作为字符串文字传递,而应通过占位符和 使用
.
Dynamic SQL should be generated through format()
and parameters should not be passed as string literals, but through placeholders and using
.
你的代码真的很难阅读,不完整,并且有一些实质性的语法错误源于例如缺少 CASE 的 END
和括号未正确配对.所以下面的代码可能仍然包含一些错误,因为我显然没有办法测试它.
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.
但是由于您的主要 SELECT 似乎根本没有使用动态 SQL,所有 quote_literal()
和字符串连接都是不必要的,直接使用变量即可.
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.
由于 max_date 应该是 date
值,您可以将字符串 'XXXXX'
分配给它,但是如果您直接使用 max_date,则可以摆脱据我所知.
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;
这篇关于postgresql:在 pl pgsql 中使用时间戳变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!