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@jakobs.com> 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@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 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