How to call Postgres function returning SETOF record?
Posted
by Peter
on Stack Overflow
See other posts from Stack Overflow
or by Peter
Published on 2010-06-06T07:48:36Z
Indexed on
2010/06/06
7:52 UTC
Read the original article
Hit count: 352
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.
© Stack Overflow or respective owner