Обсуждение: How to return multiple rows by stored procedure in postgresql

Hi Naveen,

> On 06. Oct, 2020, at 08:15, Naveen Kumar <naveenmcp@gmail.com> wrote:
>
> Thank you for the quick response.
>
> All the examples are talking about functions, not about procedures in PostgreSQL. what i am looking for is how to
returnmultiple rows by calling STORED PROCEDURE in postgreSQL 12 version. 
>
> Thank you
> Naveen

a procedure does by definition not return anything. If you want to return something, use a function.

As for returning multiple rows, use create function ... returns table (columns...)

https://www.postgresql.org/docs/current/sql-createfunction.html
https://www.postgresql.org/docs/current/sql-createprocedure.html

Cheers,
Paul

Hi Paul, hi Naveen,

Actually, stored procedures in PostgreSQL can return something if they
have INOUT parameters, but it is restricted to a single row, composed
from the parameters.

Try this:

CREATE OR REPLACE PROCEDURE whoami (
  INOUT sessionname TEXT DEFAULT '',
  INOUT currentname TEXT DEFAULT '',
  INOUT now TIMESTAMPTZ DEFAULT '2020-01-01 00:00:00') AS $$
BEGIN
  SELECT session_user, current_user, current_timestamp
  INTO sessionname, currentname, now;
END
$$ LANGUAGE plpgsql;

CALL whoami();

If you call the procedure from a Java program, make sure you use
executeQuery() and not executeUpdate(), because the row gets returned as
a result set with 1 row.

If you want to return multiple rows (aka create a table-valued
function), you have to use a function.

Regards,

Holger


Am 06.10.20 um 08:40 schrieb Paul Förster:
> Hi Naveen,
>
>> On 06. Oct, 2020, at 08:15, Naveen Kumar <naveenmcp@gmail.com> wrote:
>>
>> Thank you for the quick response.
>>
>> All the examples are talking about functions, not about procedures in PostgreSQL. what i am looking for is how to
returnmultiple rows by calling STORED PROCEDURE in postgreSQL 12 version.
 
>>
>> Thank you
>> Naveen
> a procedure does by definition not return anything. If you want to return something, use a function.
>
> As for returning multiple rows, use create function ... returns table (columns...)
>
> https://www.postgresql.org/docs/current/sql-createfunction.html
> https://www.postgresql.org/docs/current/sql-createprocedure.html
>
> Cheers,
> Paul
>
-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

not understand your  requirements,

  but here is PostgreSQL developer reference guide , go through it ,  
let me know , if any questions.


   
https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/11/toc.html

Quoting Naveen Kumar <naveenmcp@gmail.com>:

> can someone please help me on How to return multiple rows by stored
> procedure in postgresql? if possible with an example?
>
> Thank you
> Naveen