CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user5893028
user5893028

Reputation:

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?

Upvotes: 0

Views: 72

Answers (1)

RaZoDiuM
RaZoDiuM

Reputation: 223

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;

Upvotes: 2

Related Questions