How to call Postgres function returning SETOF record?
- by Peter
I have written the following function:
-- 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);
I get an error:
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
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.
