How I can return the result of query in Postgres?

Question

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?

You want return query. See the manual for details, it's all explained there: postgresql.org/docs/current/static/… Unrelated, but: you should really avoid those dreaded quoted identifiers, they are much more trouble then they are worth it. — user330315
– user330315, Commented Oct 6, 2016 at 17:33
Why do you use DO anonymous block of code when you are already inside a function? — Kamil Gosciminski
– Kamil Gosciminski, Commented Oct 6, 2016 at 17:35
One of your problems is that you use the same names for the output parameters and the columns of the table you want to return. That leads to name collisions and surprising errors that are hard to debug. Avoid it by naming the output parameters differently, like p_id and p_siteid. And by the way, avoid upper case and mixed case names wherever possible. — Laurenz Albe
– Laurenz Albe, Commented Oct 6, 2016 at 18:52

RaZoDiuM · Accepted Answer · 2016-10-06 17:34:48Z

2

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;

answered Oct 6, 2016 at 17:34

RaZoDiuM

2232 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5893028 Over a year ago

I mentioned that I tried with RETURN QUERY EXECUTE!

Laurenz Albe Over a year ago

Yes, but you didn't show the code. RETURN QUERY works fine in that case, maybe you made a mistake somewhere.

user5893028 Over a year ago

Mistake was about schema, I didn't specified columns name with schema prefix. thanks.

Collectives™ on Stack Overflow

How I can return the result of query in Postgres?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related