Postgresql function not returning a table on select

Question

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.

Stuck in an Ant Farm · Accepted Answer · 2014-09-12 19:38:20Z

1

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).

answered Sep 12, 2014 at 19:38

Stuck in an Ant Farm

663 bronze badges

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

Collectives™ on Stack Overflow

Postgresql function not returning a table on select

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related