Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPhuC-0008UD-3X for pgsql-admin@arkaria.postgresql.org; Tue, 06 Oct 2020 08:01:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kPhuB-0004Og-2k for pgsql-admin@arkaria.postgresql.org; Tue, 06 Oct 2020 08:01:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPhuA-0004OZ-S4 for pgsql-admin@lists.postgresql.org; Tue, 06 Oct 2020 08:01:02 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kPhu7-0004V5-VD for pgsql-admin@lists.postgresql.org; Tue, 06 Oct 2020 08:01:01 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 97ADA380620; Tue, 6 Oct 2020 10:00:56 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id wo_5rnMk1QGN; Tue, 6 Oct 2020 10:00:56 +0200 (CEST) Received: from [192.168.178.197] (ip-37-201-154-65.hsi13.unitymediagroup.de [37.201.154.65]) by rs.plausibolo.de (Postfix) with ESMTPSA id 5C42838061E; Tue, 6 Oct 2020 10:00:56 +0200 (CEST) Subject: Re: How to return multiple rows by stored procedure in postgresql To: pgsql-admin@lists.postgresql.org References: <39B3366A-5C16-4477-9C5C-03D3689179CB@gmail.com> From: Holger Jakobs Message-ID: <424aa4ae-2543-2f34-74b5-3a2518542412@jakobs.com> Date: Tue, 6 Oct 2020 10:00:55 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.12.1 MIME-Version: 1.0 In-Reply-To: <39B3366A-5C16-4477-9C5C-03D3689179CB@gmail.com> Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit Content-Language: de-DE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 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