Thank you all. I got the solution for the procedures returning the resultset. Thank you all for your help.
Thank you
Naveen
On Tue, Oct 6, 2020 at 1:31 PM Holger Jakobs <holger@xxxxxxxxxx> wrote:
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@xxxxxxxxx> 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
