Re: INOUT text[],OUT text parameter handling problem

On Tue, May 24, 2016 at 6:19 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> This function needs to add a string to the array and return that array.
> Also it needs to return a string.
>
>> CREATE OR REPLACE FUNCTION fbind(
>>>
>>> IN p_parm_value_i text,
>>>
>>> IN p_psudo_datatype_i character varying,
>>>
>>> OUT p_result_string_o text,
>>>
>>> INOUT p_bind_values_io text[])
>>>
>>>
The behavior you are seeing, and is reasonable to expect, is that for an
INOUT parameter ​the input value is returned unchanged as the output
value. It is, for all purposes, immutable. Within the function you get a
copy of the supplied input but not a reference to it. You modify the copy.

You will need to define a separate OUT parameter name, for instance,
"modified_p_bind_values_io" to return the modified array.

​> "​​p_result_string_o := fbind('1234'::text, 'bigint'::character varying,
using_values);"

You are passing <using_values> "by value" here, not "by reference".
p_result_string_o is being assigned an anonymous composite value
consisting of the two output columns.

You will need to write the function and test driver to conform to this
behavior.

This could possibly be documented but it comes up infrequently and the
whole concept of "by reference" variable passing is a total foreign concept
in SQL so its introduction could add confusion for readers unfamiliar with
the concept.

A sentence in the "argmod" paragraph of CREATE FUNCTION specifying that
"INOUT" arguments echo their input would probably be OK. The background,
if desired, could be placed in the notes section but would likely clutter
things unnecessarily. But it also is not the first question of this form
that I've seen.

David J.