0

i have modifed my function, but i have problems with declare variables. I used postgres 8.4. Have someone an idea?

Function:

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

BEGIN 

_minid := (SELECT id from tablename  where starttime >= $1 ORDER BY tablename2 ASC LIMIT 1); 
_maxid := (SELECT id from tablename  where starttime < $2 ORDER BY tablename2 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 11 at SQL statement

Regrads

3
  • You need to use select id into _minid from ..: postgresql.org/docs/current/static/… Commented Mar 14, 2016 at 15:03
  • I think the _minid := assignment works only in later versions. See comment before Commented Mar 14, 2016 at 15:13
  • Same 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 11 at SQL statement Commented Mar 14, 2016 at 15:36

1 Answer 1

1

Now it workes:

   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; 

    Return Query 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; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.