Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[SQL] INOUT text[],OUT text parameter handling problem

1 view
Skip to first unread message

Michael Moore

unread,
May 24, 2016, 6:20:29 PM5/24/16
to
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[])
  RETURNS record AS
$BODY$
BEGIN
p_bind_values_io := array_append(p_bind_values_io, p_parm_value_i);
CASE p_psudo_datatype_i
   WHEN 'cvs num' THEN
      p_result_string_o := '= ANY ((''{''||$'||array_length(p_bind_values_io,1)::text||'||''}'')::bigint[] ) ';
   WHEN 'bigint' THEN
      p_result_string_o := '= TO_NUMBER($'||array_length(p_bind_values_io,1)::text||',''99999999999999999999'') ';
   ELSE
      p_result_string_o := 'datatype not implemented';
END CASE;

RETURN;
END;$BODY$
  LANGUAGE plpgsql;
 Here is my test script
DO $$
DECLARE 
  using_values text[] ;
  p_result_string_o text;
BEGIN
   using_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:
  1. using_values does not show the update that was applied by fbind even though it is defined as INOUT.
  2. p_result_string_o contains a CSV representation of both the OUT and the INOUT parameters. 
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.


What do I have to do to make it so?
TIA
Mike

David G. Johnston

unread,
May 24, 2016, 6:46:15 PM5/24/16
to
On Tue, May 24, 2016 at 6:19 PM, Michael Moore <michae...@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.

Michael Moore

unread,
May 24, 2016, 7:19:44 PM5/24/16
to
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

David G. Johnston

unread,
May 24, 2016, 7:44:23 PM5/24/16
to
On Tue, May 24, 2016 at 7:19 PM, Michael Moore <michae...@gmail.com> wrote:
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.

0 new messages