如何调用Postgres函数返回SETOF记录? [英] How to call Postgres function returning SETOF record?

问题描述

我编写了以下函数:

    -- 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;

当试图这样称呼它时...

And when trying to call it like this...

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，但是我认为只有在函数不返回任何内容的情况下，才必须使用它.

I don't understand this output. I've tried using perform too, but I thought one only had to use that if the function doesn't return anything.

推荐答案

您的函数没有任何意义，它不会返回任何内容.它看起来像一个VIEW，那么为什么不创建一个视图?

Your function doesn't maken sense, it doesn't return anything. It looks like a VIEW, so why don't you create a view?

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

You have use the OUT parameters or RETURN TABLE() with the parameters:

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();

这篇关于如何调用Postgres函数返回SETOF记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！