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

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(at)jakobs(dot)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(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
>
>
>
>