yadi shahryary yadi shahryary - 2 months ago 9
SQL Question

How I can return the result of query in Postgres?

I have function in PostgreSQL, when I want to return the results I got error :

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.


Here is summary my function ( it's too long to copy/paste all of code here)

CREATE OR REPLACE FUNCTION "schema"."test"(IN Id int8,)

RETURNS TABLE("Id" uuid, "SiteId" uuid, "Domain" varchar,
"JumpCost" numeric, "CPMCost" numeric, "PaymentModel" int8, "ComputedEpm" numeric,
"ClickedCampaignId" int8, "MainRubricId" int8, "ShowSlider" bool,
"ShowGoods" bool, "ShowTeaser" bool, "BidLimitCoeff" numeric,
"RtbActiveSiteStatByBannerId" int8, "ShowType" int8, "MinAge" int8) AS

$BODY$

DECLARE
rtbCampaignEpmCoeff NUMERIC(18,6);
maxRubricShows int;
showsRubricAddedPerClick int;
siteLossLimitPerRubric NUMERIC(18,6);
rtbMinRubricVisitsToShow int;
showAdsToUsersWithoutInterests boolean;

BEGIN
......

DO
$do$
......
$do$;

PERFORM "Id", "SiteId", "Domain", "JumpCost", "CPMCost", "PaymentModel",
5 as ComputedEpm, "ClickedCampaignId", "MainRubricId", "ShowSlider",
"ShowGoods", "ShowTeaser", "BidLimitCoeff", "RtbActiveSiteStatByBannerId",
"ShowType", "MinAge"

FROM mytable

ORDER BY "FromClick" DESC, "Similarity" DESC, "rn", random()
LIMIT 1;

END;

$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;


As you see I used "PERFORM" to return results, but failed with error I explained.

Also, I used "Return query .. " and got same error.

"mytable" is temp table that I created in function.

How I can return this result?

Answer

maybe something like this?

CREATE OR REPLACE FUNCTION my_function(user_id integer)
  RETURNS TABLE(id integer, firstname character varying
                          , lastname  character varying) AS
$$
DECLARE
    ids character varying;
BEGIN
    ids := '';

    --Some code which build the ids string, not interesting for this issue

    RETURN QUERY EXECUTE 'SELECT users.id, users.firstname, users.lastname
    FROM public.users WHERE ids IN (' || ids || ')';
END;
$$ LANGUAGE plpgsql;