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

On Tue, May 24, 2016 at 3:45 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>
No 'call by reference', that's weird. So, why would anybody ever want an
INOUT parameter? Rhetorical question, not expecting an answer. So, since I
am going to have to get a copy back from the CALLED function, then I will
need to replace the original text[] with the one I got back. It would
probably be more efficient to just do the array_append function in the
caller and pass the array length to fbind.
By the way, not that you should care, but Oracle has an INOUT parameter
that does a COPY or you can specify "INOUT NOCOPY" which is of course your
call by reference.
Thanks for your help, I think I know what I need to do.
Regards,
Mike