我写了以下函数:
-- 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,但我认为只有当函数不返回任何内容时才需要使用它。