如何调用返回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，但我认为只有在函数没有返回值时才需要使用它。

- Peter

Answer 1

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

编辑： 你可以使用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();

- Frank Heikens

---

我来自SQL Server的世界。我期望类似的行为，即返回任何SELECT语句。我将尝试切换到视图。谢谢。 - Peter

假设我确实想传递一个参数，那么我该如何返回我的SQL查询结果呢？ - Peter

例如，RETURNS TABLE（int，text）将是另一种选择。 - Frank Heikens

解决实际问题的关键是使用"RETURN QUERY"，使以下SELECT的结果集成为函数的结果。OUT参数/RETURNS TABLE只是使调用更加容易。 - araqnid