Declare variable for SELECT statement in Postgres function

Question

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?

Answer 1

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.

• liquid: @Version: i know - thats the plan. but we need 8.4 because of an application.
• András Váczi: @liquid in my experience, moving from 8.4 to anything (up until 9.4, IIRC) is quite seamless.

Answer 2

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

• Create function with SELECT CASE

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.