在 Postgres 函式中為 SELECT 語句聲明變數

我已經修改了我的函式，但是我在聲明變數時遇到了問題。我使用 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:  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

如何解決這個問題？

如果SELECTwithCASE表達式是您想要在輸出表中返回的內容，只需RETURN QUERY在它之前添加：

RETURN QUERY SELECT CASE ...

注意： 8.4 現在已經很老了。現在甚至 9.0 也不支持 - 考慮盡快升級到最近的主要版本。舊的通常不再獲得任何安全（和其他）修復。

我想我們RETURN QUERY在您之前關於 SO 的相關問題中討論了 for plpgsql 函式的使用：

• 使用 SELECT CASE 創建函式

同樣，使用簡單的 SQL 函式會更有效：

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;

多個單獨的查詢和分配更昂貴。

引用自：https://dba.stackexchange.com/questions/132268