Postgres sql ERROR: query has no destination for result datal

Question

CREATE OR REPLACE FUNCTION master.test2(tehsil text, district text, state text, flag text)
  RETURNS setof master.population AS
$BODY$
BEGIN


  IF flag='A' THEN
    select Count(*) from master.population where income in('1','2')and statecode=state;
  ELSIF flag='B' THEN
 select Count(*) from master.population where income in('1','2') and statecode=state and districtcode=district ;
   ELSIF flag='C' THEN
  select Count(*) from master.population where income in('1','2') and statecode=state and districtcode=district and tehsilcode=tehsil;


ELSE
     select Count(*) from master.population where income in('1','2'); 
END IF;

END;
$BODY$
  LANGUAGE plpgsql

Throwing 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 master.test2(text,text,text,text) line 6 at SQL statement

You should also add what you have tried to fix your problem. — rekire
– rekire, Commented May 22, 2013 at 9:01
What's unclear about the error message? The result of the select count(*) needs to be stored in a variable. If you want to return a result, you need to use return query. Please check the manual, it has several examples for this. — user330315
– user330315, Commented May 22, 2013 at 9:23

Regexident · Accepted Answer · 2013-05-22 09:25:29Z

5

plpgSQL function can't just run a query; you have to put the results somewhere.
See the documentation for details.

It appears that you want to return the count(*) from the function so your return type should look something like

CREATE OR REPLACE FUNCTION master.test2(tehsil text, district text, state text, flag text)    RETURNS INT AS
$BODY$
  DECLARE 
    CNT INT;
  BEGIN

  IF flag='A' THEN
    select Count(*) from master.population where income in('1','2')and statecode=state INTO CNT;
  ELSIF flag='B' THEN
 select Count(*) from master.population where income in('1','2') and statecode=state and districtcode=district INTO CNT ;
   ELSIF flag='C' THEN
  select Count(*) from master.population where income in('1','2') and statecode=state and districtcode=district and tehsilcode=tehsil INTO CNT;

ELSE
     select Count(*) from master.population where income in('1','2') INTO CNT; 
END IF;
RETURN CNT;

END;
$BODY$
LANGUAGE plpgsql;

edited May 22, 2013 at 9:25

Regexident

29.6k10 gold badges95 silver badges100 bronze badges

answered May 22, 2013 at 9:20

Muhammad Usama

3,2971 gold badge19 silver badges16 bronze badges

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

3 Comments

user330315 Over a year ago

Postgres functions can return a result set, they just need to use return query. But in this case this is not needed as only a single value should be returned.

Anusha Over a year ago

so if I want to call this function, it should be like this??? select * from master.test2('0', '0', '04', 'C') but when I am calling it like this it's giving a 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 secc_master.test3(text,text,text,text) line 17 at SQL statement ********** Error ********** ERROR: query has no destination for result data SQL state: 42601

Zeeshanef Over a year ago

@Muhammad Usama: usage of into in the last for assignment query result is wonderful for me. actually I was searching to save auto generated id into variable and then return it.But always error when calling function. but by following way(using Into in last) did work for me. 'Insert into usertbl(id, username) values (default, user_name) returning id into lastid;' lastid was declared as bigint. Thanks and +1 for this.

Soul Clinic · Accepted Answer · 2014-06-07 13:43:26Z

0

Use:

RETURN QUERY select Count(*) ...

answered Jun 7, 2014 at 13:43

Soul Clinic

1,29913 silver badges20 bronze badges

Collectives™ on Stack Overflow

Postgres sql ERROR: query has no destination for result datal

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related