Add field in RETURNS TABLE pgplsql function

Question

How to add a field in the RETURNS TABLE of a plpgpsql function, the value I want to add is not present in the SELECT performed inside the function :

I have this function that samples the sum of cpu and ram used by jobs every 'step' seconds between start_date and end_date (epoch timestamp):

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;

The outputed results is something like :

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

How to add the value of the variable 'counter' in the table to have something like :

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

Kaushik Nayak · Accepted Answer · 2019-02-28 10:13:03Z

1

You actually don't need EXECUTE for your case as there's no dynamic parameter like tablename, column name etc. It's only the value passed to where clause that changes. The counter can be part of select.

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;

I would recommend you to avoid loops, a single query using generate_series would be good enough.The function can then be a simple SQL function unless you have anything else to do than return just a query result.

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

edited Feb 28, 2019 at 10:13

answered Feb 28, 2019 at 10:02

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

The second function can even be language sql not need for PL/pgSQL there

Tony Morris Over a year ago

Thanks, I understand better now, the only downside is that if the "select from jobs" does not return any jobs i won't have the entry for the current counter

Tony Morris Over a year ago

With the solution using LOOP I have a line when no jobs is selected (thus cpu/ram is 0 ). The solution with generate_series seems cleaner though.

user330315 · Accepted Answer · 2019-02-28 10:01:16Z

1

There is no need for dynamic SQL here. And if you leave that out, you can simply add the variable to the select:

CREATE OR REPLACE FUNCTION resources(start_date INTEGER, end_date INTEGER, step INTEGER) 
  RETURNS TABLE (
      cpu bigint
    , ram bigint 
    , counter bigint)  
AS $$

DECLARE
   counter INTEGER := 0;
BEGIN
  counter := start_date;
  LOOP 
    EXIT WHEN counter > end_date;

    RETURN QUERY 
      SELECT sum(j_ram) as ram, sum(j_cpu) as cpu, counter 
      from jobs 
      where j_start_time <= counter 
        and j_end_time >= counter;

    counter := counter + step; 
  END LOOP; 

END;
$$ LANGUAGE plpgsql;

answered Feb 28, 2019 at 10:01

user330315

Collectives™ on Stack Overflow

Add field in RETURNS TABLE pgplsql function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related