Create function with SELECT CASE

Question

I want create a function with 2 parameters. This is my SELECT statement:

SELECT CASE 
   WHEN duration <=  10000000 THEN '00-01 sec'
           WHEN duration <=  40000000 THEN '01-04 sec' 
           WHEN duration <= 100000000 THEN '04-10 sec' 
           WHEN duration <= 300000000 THEN '10-30 sec' 
           WHEN duration <= 600000000 THEN '30-60 sec' 
           ELSE 'more than 60 sec' END AS "Kategorien", 
   COUNT(*) AS Requestcounter
FROM tablename
WHERE starttime BETWEEN '2016-03-01 00:00:00' AND '2016-03-08 14:00:00'
GROUP BY
  (CASE WHEN duration <=  10000000 THEN '00-01 sec'
   WHEN duration <=  40000000 THEN '01-04 sec' 
   ELSE 'more than 60 sec' END); 

Result:

 Kategorien | requestcounter
------------+----------------
 00-01 sec  |           2073
 01-04 sec  |              2
(2 rows)

I want this result from my function. But I get the error:

query has no destination for result data

This is my function:

CREATE OR REPLACE FUNCTION requestcounter(mindate timestamptz,maxdate timestamptz) 
RETURNS SETOF integer AS $$ 

BEGIN
SELECT CASE WHEN duration <=  10000000 THEN '00-01 sec'
            WHEN duration <=  40000000 THEN '01-04 sec'  
            ELSE 'more than 60 sec' END AS "Kategorien", 
COUNT(*) AS Requestcounter from tablename where starttime BETWEEN mindate and maxdate
GROUP BY
  (CASE WHEN duration <=  10000000 THEN '00-01 sec' 
        WHEN duration <=  40000000 THEN '01-04 sec'  
        ELSE 'more than 60 sec' END);
Return; 
END;
$$ LANGUAGE plpgsql;

I know that RETURNS SETOF integer is not right but I have no idea how to get this to work?

Answer 1

@Travis already provided that you need RETURN QUERY to actually return the results of a query from a plpgsql function.

But you don't need plpgsql for a simple SELECT. Use a plain SQL function instead. You need to define the return type either way, best use RETURNS TABLE like @Travis also provided, but the data types actually returned have to match precisely:

CREATE OR REPLACE FUNCTION requestcounter(_mindate timestamptz, _maxdate timestamptz) 
  RETURNS TABLE (kategorien text, requestcounter int) AS
$func$ 
SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
            WHEN duration <= 40000000 THEN '01-04 sec'
            -- more cases ...
            ELSE 'more than 60 sec' END  -- AS kategorien -- not visible outside function
     , count(*)::int                     -- AS requestcounter
FROM   tablename
WHERE  starttime BETWEEN _mindate AND _maxdate
GROUP  BY 1                              -- simpler with positional reference
ORDER  BY 1                              -- see below   
$func$  LANGUAGE sql;                    -- never quote the language name

• count() returns bigint. If you define the returned column requestcounter as int, you have to cast: count(*)::int (Or return bigint to begin with.)

• In an SQL function you can use a plain SELECT to return rows.

• Avoid naming conflicts between parameters and column name. A common convention is to prefix parameters like I did.

• A positional reference in GROUP BY is a very convenient syntax shortcut for long expressions in the SELECT list. This also makes sure that GROUP BY items match SELECT items (which is not the case in your first query).

• You probably want to add ORDER BY 1 or you get an arbitrary order. Your demonstrated text values happen to sort in the right order.

Call:

SELECT * FROM requestcounter('2015-01-01 00:00+01', '2015-03-01 00:00+01')