postgresql - 在 RETURNS TABLE pgplsql 函数中添加字段

Question

如何在 plpgpsql 函数的 RETURNS TABLE 中添加字段，我要添加的值在函数内部执行的 SELECT 中不存在:

我有这个函数，它在开始日期和结束日期(纪元时间戳)之间每隔“步”秒对作业使用的 cpu 和 ram 的总和进行采样:

CREATE OR REPLACE FUNCTION resources(start_date INTEGER, end_date INTEGER, step INTEGER) 
RETURNS TABLE (
    cpu bigint
  , ram bigint)  AS $$
DECLARE
   counter INTEGER := 0 ;
BEGIN
  counter := start_date;
  LOOP 
    EXIT WHEN counter > end_date ;
RETURN QUERY EXECUTE
    'SELECT sum(j_ram) as ram, sum(j_cpu) as cpu from jobs where j_start_time <= ' || counter || ' and j_end_time >= ' || counter;
    counter := counter + step ; 
  END LOOP ; 

END;
$$ LANGUAGE plpgsql;    

输出的结果是这样的:

 cpu   |  ram 
-------+------
     0 |      
     6 | 12000
     6 | 11000
     1 | 1000

如何在表中添加变量 'counter' 的值使其具有类似的内容:

 cpu   |  ram  | counter 
-------+-------+--------
     0 |       |     100        
     6 | 12000 |     110      
     6 | 11000 |     120       
     1 |  1000 |     130      

Answer 1

您的情况实际上不需要 EXECUTE，因为没有动态参数，如表名、列名等。它只是传递给 where 子句的值发生变化。计数器可以是选择的一部分。

CREATE OR REPLACE FUNCTION resources(start_date INTEGER, end_date INTEGER, 
step INTEGER) 
RETURNS TABLE (
    cpu bigint
  , ram bigint
  , counter int)  AS $$
DECLARE
   counter INTEGER := 0 ;
BEGIN
  counter := start_date;
  LOOP 
    EXIT WHEN counter > end_date ;
RETURN QUERY 
    SELECT sum(j_ram)::bigint as ram, sum(j_cpu)::bigint as cpu,
           counter from jobs 
        where j_start_time <= counter
    and  j_end_time >= counter;
    counter := counter + step ; 
  END LOOP ; 
END;
$$ LANGUAGE plpgsql;    

我建议您避免循环，使用 generate_series 的单个查询就足够了。该函数可以是一个简单的 SQL 函数，除非您有任何其他需要做而不是只返回一个查询结果。

CREATE OR REPLACE FUNCTION resources2(start_date INTEGER, end_date INTEGER, 
step INTEGER) 
RETURNS TABLE (
    cpu bigint
  , ram bigint
  , counter int)  AS $$
   SELECT sum(j_ram) :: BIGINT AS ram, 
         sum(j_cpu) :: BIGINT AS cpu, 
       g.counter 
    FROM   jobs j 
       JOIN (SELECT generate_series(start_date, end_date, step) AS counter) g 
         ON j_start_time <= g.counter 
            AND j_end_time >= g.counter 
   GROUP  BY g.counter 
    ORDER  BY g.counter; 
$$ LANGUAGE sql;   

Demo