pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to return multiple rows by stored procedure in postgresql
7+ messages / 5 participants
[nested] [flat]
* How to return multiple rows by stored procedure in postgresql
@ 2020-10-06 05:55 Naveen Kumar <naveenmcp@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Naveen Kumar @ 2020-10-06 05:55 UTC (permalink / raw)
To: pgsql-admin
can someone please help me on How to return multiple rows by stored
procedure in postgresql? if possible with an example?
Thank you
Naveen
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to return multiple rows by stored procedure in postgresql
@ 2020-10-06 06:01 androxkentaki <androxkentaki@gmail.com>
parent: Naveen Kumar <naveenmcp@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: androxkentaki @ 2020-10-06 06:01 UTC (permalink / raw)
To: Naveen Kumar <naveenmcp@gmail.com>; +Cc: pgsql-admin
1 minute research in google
https://www.google.com/search?client=firefox-b-d&q=postgresql+select+form+procedure
https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-va...
wt., 6 paź 2020 o 07:55 Naveen Kumar <naveenmcp@gmail.com> napisał(a):
> can someone please help me on How to return multiple rows by stored
> procedure in postgresql? if possible with an example?
>
> Thank you
> Naveen
>
>
--
Z wyrazami szacunku
Andrzej Gerasimuk
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to return multiple rows by stored procedure in postgresql
@ 2020-10-06 06:15 Naveen Kumar <naveenmcp@gmail.com>
parent: androxkentaki <androxkentaki@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Naveen Kumar @ 2020-10-06 06:15 UTC (permalink / raw)
To: androxkentaki <androxkentaki@gmail.com>; +Cc: pgsql-admin
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
On Tue, Oct 6, 2020 at 11:32 AM androxkentaki <androxkentaki@gmail.com>
wrote:
> 1 minute research in google
>
> https://www.google.com/search?client=firefox-b-d&q=postgresql+select+form+procedure
>
>
> https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-va...
>
> wt., 6 paź 2020 o 07:55 Naveen Kumar <naveenmcp@gmail.com> napisał(a):
>
>> can someone please help me on How to return multiple rows by stored
>> procedure in postgresql? if possible with an example?
>>
>> Thank you
>> Naveen
>>
>>
>
> --
> Z wyrazami szacunku
> Andrzej Gerasimuk
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to return multiple rows by stored procedure in postgresql
@ 2020-10-06 06:40 Paul Förster <paul.foerster@gmail.com>
parent: Naveen Kumar <naveenmcp@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Paul Förster @ 2020-10-06 06:40 UTC (permalink / raw)
To: Naveen Kumar <naveenmcp@gmail.com>; +Cc: androxkentaki <androxkentaki@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to return multiple rows by stored procedure in postgresql
@ 2020-10-06 08:00 Holger Jakobs <holger@jakobs.com>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Holger Jakobs @ 2020-10-06 08:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to return multiple rows by stored procedure in postgresql
@ 2020-10-06 08:24 Naveen Kumar <naveenmcp@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 7+ messages in thread
From: Naveen Kumar @ 2020-10-06 08:24 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to return multiple rows by stored procedure in postgresql
@ 2020-10-08 12:31 lennam@incisivetechgroup.com
parent: Naveen Kumar <naveenmcp@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: lennam@incisivetechgroup.com @ 2020-10-08 12:31 UTC (permalink / raw)
To: Naveen Kumar <naveenmcp@gmail.com>; +Cc: pgsql-admin
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.h...
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-10-08 12:31 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-06 05:55 How to return multiple rows by stored procedure in postgresql Naveen Kumar <naveenmcp@gmail.com>
2020-10-06 06:01 ` androxkentaki <androxkentaki@gmail.com>
2020-10-06 06:15 ` Naveen Kumar <naveenmcp@gmail.com>
2020-10-06 06:40 ` Paul Förster <paul.foerster@gmail.com>
2020-10-06 08:00 ` Holger Jakobs <holger@jakobs.com>
2020-10-06 08:24 ` Naveen Kumar <naveenmcp@gmail.com>
2020-10-08 12:31 ` lennam@incisivetechgroup.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox