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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: INOUT text[],OUT text parameter handling problem
Дата
Msg-id CAKFQuwZqJBq3Lhi81fJoA6Yv_r2C4bQWq-iSk4X_OqYyVB71Tg@mail.gmail.com
обсуждение исходный текст
Ответ на INOUT text[],OUT text parameter handling problem  (Michael Moore <michaeljmoore@gmail.com>)
Ответы Re: INOUT text[],OUT text parameter handling problem  (Michael Moore <michaeljmoore@gmail.com>)
Список pgsql-sql
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.

В списке pgsql-sql по дате отправления:

Предыдущее
От: Michael Moore
Дата:
Сообщение: INOUT text[],OUT text parameter handling problem
Следующее
От: Michael Moore
Дата:
Сообщение: Re: INOUT text[],OUT text parameter handling problem