yadi shahryary yadi shahryary - 3 months ago 16
SQL Question

How to return results as table from Postgres function?

I have function script that I converted from SQL Server to Postgres, now when I'm running the function I get an error


ERROR: structure of query does not match function result type


My function gets 3 parameters (
siteid bigint, datefrom timestamp, dateto timestamp
) and should return a table which I included in code. I used "Return Query".

I'm executing my function like this:

getrtbactivesiteplaces(1475, '2016-02-01', '2016-08-01')


How I can get this result as a table from my function?

This is screenshot of my function

{
CREATE OR REPLACE FUNCTION "whis2011"."getrtbactivesiteplaces"(IN siteid int8, IN datefrom timestamp, IN dateto timestamp) RETURNS SETOF "varchar"
AS $BODY$

DECLARE
siteid BIGINT;
datefrom timestamp without time zone;
dateto timestamp without time zone;


BEGIN


-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

/* SQLWays Notice: SET TRANSACTION ISOLATION LEVEL READ COMMITTED must be called before procedure call */
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED


-- Insert statements for procedure here
RETURN QUERY SELECT pl."Id",
pl."RtbActiveSiteId",
pl."AdPlaceId",
pl."AdPosition",
pl."Ctr",
pl."State",
pl."BidPrice",
pl."MinBidFloor",
pl."MinBidFloorCurrency",
pl."AverageCpm",
pl."AverageClickCost",
coalesce(SUM(ss."BidsCount"),0) AS BidsCount,
coalesce(SUM(ss."ShowsCount"),0) AS ShowsCount,
coalesce(SUM(ss."RealShowsCount"),0) AS RealShowsCount,
coalesce(SUM(ss."ClicksCount"),0) AS ClicksCount,
coalesce(SUM(ss."ClickLayerClicksCount"),0) as ClickLayerClicksCount,
coalesce(SUM(ss."ShowsCost"),0::money) AS ShowsCost,
coalesce(SUM(ss."ClicksCost"),0::money) AS ClicksCost,
coalesce(SUM(ss."BidsCost"),0::money) AS BidsCost,
coalesce(SUM(ss."SliderMovesCount"),0) AS SliderMovesCount
FROM "whis2011"."RtbActiveSitePlaces" pl
LEFT OUTER JOIN "whis2011"."RtbActiveSitePlaceStatistics" ss ON ss."RtbActiveSitePlaceId" = pl."Id"
WHERE ss."Date" >= datefrom AND ss."Date" < dateto AND pl."RtbActiveSiteId" = siteid
GROUP BY pl."Id", pl."RtbActiveSiteId", pl."AdPlaceId", pl."AdPosition", pl."Ctr", pl."State", pl."BidPrice",
pl."MinBidFloor", pl."MinBidFloorCurrency", pl."AverageCpm", pl."AverageClickCost";
END;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;

}

Answer

You have two problems with your code and I have two recommendations for improvements.

First of all, instead of RETURNS SETOF varchar you should do RETURNS TABLE ... specifying all columns that the function returns. Secondly, you have three function parameters, whose names you redeclare in the function block, masking out the parameter values. This is why the function returns nothing because the parameter values are not used.

Thirdly, I put all the sums in a sub-query, which reads easier and is probably more efficient too. Lastly, since the function body is a single SQL statement, you should make this a SQL function, which is more efficient than a PL/pgSQL language function.

See below.

CREATE OR REPLACE FUNCTION "whis2011"."getrtbactivesiteplaces"
    (IN siteid int8, IN datefrom timestamp, IN dateto timestamp)
RETURNS SETOF "varchar" TABLE (id int, RtbActiveSiteId int, ...) -- add all fields
AS $BODY$
DECLARE
siteid BIGINT;
datefrom timestamp without time zone;
dateto   timestamp without time zone; -- don't redeclare parameters!!!
BEGIN -- not needed for a SQL function
    -- Insert statements for procedure here
    RETURN QUERY SELECT pl."Id", -- SQL function uses simple SELECT
        pl."RtbActiveSiteId",
        pl."AdPlaceId",
        pl."AdPosition",
        pl."Ctr",
        pl."State",
        pl."BidPrice",
        pl."MinBidFloor",
        pl."MinBidFloorCurrency",
        pl."AverageCpm",
        pl."AverageClickCost",
        ss.*
    FROM "whis2011"."RtbActiveSitePlaces" pl
    LEFT JOIN (
        SELECT "RtbActiveSitePlaceId" AS "Id"
               coalesce(SUM("BidsCount"),0) AS BidsCount,
               coalesce(SUM("ShowsCount"),0) AS ShowsCount,
               coalesce(SUM("RealShowsCount"),0) AS RealShowsCount,
               coalesce(SUM("ClicksCount"),0) AS ClicksCount,
               coalesce(SUM("ClickLayerClicksCount"),0) as ClickLayerClicksCount,
               coalesce(SUM("ShowsCost"),0::money) AS ShowsCost,
               coalesce(SUM("ClicksCost"),0::money) AS ClicksCost,
               coalesce(SUM("BidsCost"),0::money) AS BidsCost,
               coalesce(SUM("SliderMovesCount"),0) AS SliderMovesCount
         FROM "whis2011"."RtbActiveSitePlaceStatistics"
         WHERE "Date" >= datefrom AND "Date" < dateto
         GROUP BY "RtbActiveSitePlaceId") ss USING ("Id")
    WHERE pl."RtbActiveSiteId" = siteid;
END;  -- not needed for a SQL function
$BODY$ LANGUAGE sql STRICT STABLE; -- don't call on NULL input and use STABLE

You then call this function like so:

SELECT * FROM getrtbactivesiteplaces(1475, '2016-02-01', '2016-08-01');