postgresql - 如何调用返回 SETOF 记录的 Postgres 函数？

Question

我写了下面的函数:

    -- Gets stats for all markets
CREATE OR REPLACE FUNCTION GetMarketStats (
)
RETURNS SETOF record
AS
$$
BEGIN
 SELECT 'R approved offer' AS Metric,
 SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketAPlus24,
 SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketAPlus36,
 SUM(CASE WHEN M.MarketName = 'A' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketA24,
 SUM(CASE WHEN M.MarketName = 'A' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketA36,
 SUM(CASE WHEN M.MarketName = 'B' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketB24,
 SUM(CASE WHEN M.MarketName = 'B' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketB36
FROM "Market" M
 INNER JOIN "Listing" L ON L.MarketID = M.MarketID
 INNER JOIN "ListingOffer" LO ON L.ListingID = LO.ListingID;
END
$$
LANGUAGE plpgsql;

当尝试这样调用它时......

select * from GetMarketStats() AS (
   Metric VARCHAR(50),
   MarketAPlus24 INT,  
   MarketAPlus36 INT,
   MarketA24 INT,
   MarketA36 INT,
   MarketB24 INT,
   MarketB36 INT);

我得到一个错误:

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 "getmarketstats" line 2 at SQL statement

我不明白这个输出。我也尝试过使用 perform，但我认为只有在函数不返回任何内容时才必须使用它。

Answer 1

你的函数没有意义，它不返回任何东西。它看起来像一个 VIEW，那你为什么不创建一个 View 呢？

编辑:您已将 OUT 参数或 RETURN TABLE() 与以下参数一起使用:

CREATE OR REPLACE FUNCTION my_func(OUT o_id INT, OUT o_bar TEXT) 
RETURNS SETOF RECORD AS
$$
BEGIN
    RETURN QUERY SELECT id, bar FROM foo;
END;
$$
LANGUAGE plpgsql;


SELECT  * FROM my_func();