Re: How to return multiple rows by stored procedure in postgresql

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(at)gmail(dot)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 return multiple 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