From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: How to return multiple rows by stored procedure in postgresql
Date: Tue, 6 Oct 2020 10:00:55 +0200
Message-ID: <424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com> (raw)
In-Reply-To: <39B3366A-5C16-4477-9C5C-03D3689179CB@gmail.com>
References: <CAN+uLsG8ws_o8HtnZtvezte_74QQsT5qCs=1+fa+rY7ubv8HZw@mail.gmail.com>
<CAL+gpC8FRukDicarW282TVd3_guZKW-Zbq8uw0xCObPovw+pcA@mail.gmail.com>
<CAN+uLsEvZH1JdhDUfJaevz8AUyVgdX75ufnc0TgUZY9wquKaOg@mail.gmail.com>
<39B3366A-5C16-4477-9C5C-03D3689179CB@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com
Subject: Re: How to return multiple rows by stored procedure in postgresql
In-Reply-To: <424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com>
References: <CAN+uLsG8ws_o8HtnZtvezte_74QQsT5qCs=1+fa+rY7ubv8HZw@mail.gmail.com> <CAL+gpC8FRukDicarW282TVd3_guZKW-Zbq8uw0xCObPovw+pcA@mail.gmail.com> <CAN+uLsEvZH1JdhDUfJaevz8AUyVgdX75ufnc0TgUZY9wquKaOg@mail.gmail.com> <39B3366A-5C16-4477-9C5C-03D3689179CB@gmail.com> <424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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