Обсуждение: INOUT text[],OUT text parameter handling problem

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[])

On Tue, May 24, 2016 at 6:19 PM, Michael Moore <michaeljmoore@gmail.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.

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.