CODEWITHSUNDEEP
sqlpostgresql
Abhishek
Abhishek

Reputation: 3068

Postgresql function not returning a table on select

I have the following postgresql function in which i am trying to return 2 parameters named campusid and campusname.

CREATE OR REPLACE FUNCTION getall(IN a character varying, IN b character varying)
  RETURNS TABLE(id character varying, name character varying) AS
$BODY$
BEGIN
if $1 = 'PK' then
     SELECT * from table1;

end if;
END
$BODY$
LANGUAGE plpgsql;

But i am getting the following 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 "getallcampuses" line 27 at SQL statement

********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "getallcampuses" line 27 at SQL statement

What do i need to change in the function to make it return me a table of values? I have also checked the perform query but i need to return a result.

Upvotes: 0

Views: 274

Answers (1)

Stuck in an Ant Farm
Stuck in an Ant Farm

Reputation: 66

You must have a destination for the selects, and the function must return a value. Just a SELECT statement does neither. The only use of such a statement, generally, is to test permissions, or make a trigger run, for which the results are not used. You will need to use one of the family of RETURN statements, to get values from the function.

RETURN QUERY( SELECT * from "SIS_campus" );

That will add the results of that query to the function's returning results, and should do what you're after, since you only can return 0 or 1 results. You may need to add a simple RETURN at the very end of the function, as well (despite the docs, I've not quite grokked when that is or isn't needed, myself).

Upvotes: 1

Related Questions