Subject: Re: Support for OUT parameters in procedures
To: Robert Haas <robertmhaas@gmail.com>
References: <2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com>
 <CA+TgmobV5QdvDq7LJbRYULi9U7dUQnYz8MzhVmgMrDUmEUV4VQ@mail.gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <7c2aa288-74fd-fe0a-58bb-d77055315047@2ndquadrant.com>
Date: Fri, 28 Aug 2020 08:04:04 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: 
 <CA+TgmobV5QdvDq7LJbRYULi9U7dUQnYz8MzhVmgMrDUmEUV4VQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 2020-08-27 15:56, Robert Haas wrote:
> On Thu, Aug 27, 2020 at 4:34 AM Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> For a top-level direct call, you can pass whatever you want, since all
>> OUT parameters are presented as initially NULL to the procedure code.
>> So you could just pass NULL, as in CALL test_proc(5, NULL).
> 
> Is that actually how other systems work? I would think that people
> would expect to pass, say, a package variable, and expect that it will
> get updated.

The handling of results of SQL statements executed at the top level 
(a.k.a. direct SQL) is implementation-specific and varies widely in 
practice.  More interesting in practice, in terms of functionality and 
also compatibility, are nested calls in PL/pgSQL as well as integration 
in JDBC.

We already support INOUT parameters in procedures, so the method of 
returning the value of output parameters after the CALL already exists. 
This patch doesn't touch that at all, really.  If we had or would add 
other places to put those results, such as package variables, then they 
could be added independently of this patch.

Of course, feedback from those more knowledgeable in other systems than 
me would be welcome.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services