Thread: 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.What I want to see in my test script is a TEXT variable that will contain the value of p_result_string_o which was assigned in fbind. Also I want "using_values" to contain the value which was inserted by fbind.
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[])RETURNS record AS$BODY$BEGINp_bind_values_io := array_append(p_bind_values_io, p_parm_value_i);CASE p_psudo_datatype_iWHEN 'cvs num' THENp_result_string_o := '= ANY ((''{''||$'||array_length(p_bind_values_io,1)::text||'||''}'')::bigint[] ) ';WHEN 'bigint' THENp_result_string_o := '= TO_NUMBER($'||array_length(p_bind_values_io,1)::text||',''99999999999999999999'') ';ELSEp_result_string_o := 'datatype not implemented';END CASE;RETURN;END;$BODY$LANGUAGE plpgsql;
Here is my test script
DO $$DECLAREusing_values text[] ;p_result_string_o text;BEGINusing_values := array_append(using_values, 'the first entry'::text);p_result_string_o := fbind('1234'::text, 'bigint'::character varying, using_values);raise notice 'p_result_string_o:%', p_result_string_o;raise notice 'using_values:%', using_values;END$$;
Output from my test script:
NOTICE: p_result_string_o:("= TO_NUMBER($2,'99999999999999999999') ","{""the first entry"",1234}")
NOTICE: using_values:{"the first entry"}
The problems are:
- using_values does not show the update that was applied by fbind even though it is defined as INOUT.
- p_result_string_o contains a CSV representation of both the OUT and the INOUT parameters.
What do I have to do to make it so?
TIA
Mike
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.
On Tue, May 24, 2016 at 3:45 PM, David G. Johnston <david.g.johnston@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.
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.
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
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.
FWIW the line immediately preceding the one quoted previously was a good opportunity to "call by reference" if it was allowed:
> using_values := array_append(using_values, 'the first entry'::text);
array_append is a "pure function"[1] though for non-trivial arrays having mutable properties would be much more efficient.
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.
It helps explain where all these heretical thoughts come from ;)
David J.