1

I have modified my function, but I have problems with declaring variables. I use Postgres 8.4.

CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) 
  RETURNS TABLE (kategorien text, requestcounter int) AS
$func$  
DECLARE
_minid bigint;
_maxid bigint;

BEGIN 

SELECT id  INTO _minid from tablename
where starttime >= $1 ORDER BY starttime ASC LIMIT 1; 
SELECT id  INTO _maxid from tablename
where starttime < $2 ORDER BY starttime DESC LIMIT 1; 

SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
            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  
     , count(*)::int                     
FROM   tablename
WHERE  id >= _minid and id <= _maxid
GROUP  BY 1                              
ORDER  BY 1; 

END; 
$func$ LANGUAGE plpgsql;

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement

How to fix this?

2

If the SELECT with the CASE expression is something you want to return in the output table, just add RETURN QUERY before it:

RETURN QUERY SELECT CASE ...

Note: 8.4 is very old now. Even 9.0 is out of support by now - consider upgrading to a recent major version soon. The old ones usually don't get any security (and other) fixes anymore.

  • @Version: i know - thats the plan. but we need 8.4 because of an application. – liquid Mar 15 '16 at 11:33
  • 1
    @liquid in my experience, moving from 8.4 to anything (up until 9.4, IIRC) is quite seamless. – dezso Mar 15 '16 at 11:37
0

I think we discussed the use of RETURN QUERY for plpgsql functions at your previous related question on SO:

Again, it would be more efficient to use a simple SQL function:

CREATE OR REPLACE FUNCTION requestcounterid(_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'
               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
        , count(*)::int  
   FROM   tbl
   WHERE  id >= (SELECT id FROM tbl
                 WHERE starttime >= $1 ORDER BY starttime ASC LIMIT 1)
   AND    id <= (SELECT id FROM tbl
                 WHERE starttime <  $2 ORDER BY starttime DESC LIMIT 1)
   GROUP  BY 1
   ORDER  BY 1;
$func$ LANGUAGE sql;

Multiple separate queries and assignments are more expensive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.