pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (7+ messages)  latest in thread

Message-ID: <424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com>
Permalink:  ../424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com/
Also on:    postgresql.org/message-id/424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com

 · 

reply

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