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屋！