Postgresql return SETOF row from Table

Question

I have this stored procedure in DBMS (Postgresql)

CREATE OR REPLACE FUNCTION getallentree()
  RETURNS SETOF entree AS
$BODY$
begin 
   select * from entree ; 
end;
$BODY$
  LANGUAGE plpgsql

After calling this procedure :

select *  from getAllEntree();

I receive this 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 getallentree() line 3 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 getallentree() line 3 at SQL statement

user330315 · Accepted Answer · 2015-01-15 07:35:56Z

2

For such a simple query, it's better to use a SQL function. They can be inlined and better optimized by the query optimizer than a PL/pgSQL function:

CREATE OR REPLACE FUNCTION getallentree()
  RETURNS SETOF entree AS
$BODY$
   select * from entree ; 
$BODY$
  LANGUAGE sql;

If you do need PL/pgSQL for some other processing that you didn't show us, then Mureinik's answer about using a return query is the way to go.

answered Jan 15, 2015 at 7:35

user330315

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

4 Comments

abdou amer Over a year ago

You mean that, do this from java jdbc, is better than calling a stored procedure ?

user330315 Over a year ago

I mean: for just a plain query without any procedural logic a SQL function is better (faster) than a PL/pgSQL function. Calling/using a SQL function is no different in JDBC than calling/using a PL/pgSQL function (although I don't see the reason for a function at all for such a simple statement, but you are probably not showing us everything).

abdou amer Over a year ago

My reason for using a stored procedure, is to minimize the rounds trip beteween the client and the postgresql server.But, really i need to know the reasons from experts. I want try this, to if it is better than using normal PreparedStatement or Statement in JDBC.

user330315 Over a year ago

The decision between a SQL and a PL/pgSQL function has nothing to do with using a PreparedStatement or a Statemet in JDBC. A SQL function is more efficient than a PL/pgSQL function regardless on how you call it (especially if you start joining against it). But for such a simple query I would never use a function in the first place. And this function (as shown) will not reduce the round trips to the database. The PL/pgSQL function will actually be more expensive to call. Calling the SQL function will not make any difference

Mureinik · Accepted Answer · 2015-01-15 07:26:41Z

1

You just need to add return query to your select statement:

CREATE OR REPLACE FUNCTION getallentree()
  RETURNS SETOF entree AS
$BODY$
begin 
   return query
   select * from entree ; 
end;
$BODY$
  LANGUAGE plpgsql

answered Jan 15, 2015 at 7:26

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Collectives™ on Stack Overflow

Postgresql return SETOF row from Table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related