INOUT text[],OUT text parameter handling problem

From: Michael Moore <michaeljmoore(at)gmail(dot)com>
To: postgres list <pgsql-sql(at)postgresql(dot)org>
Subject: INOUT text[],OUT text parameter handling problem
Date: 2016-05-24 22:19:12
Message-ID: CACpWLjOioHs+UWJNb4VHii_aEEvjn_2Aun6p3v6BsFVSOTuOdw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2016-05-24 22:45:00 Re: INOUT text[],OUT text parameter handling problem
Previous Message Maks Materkov 2016-05-21 16:52:06 SQL query funnel analysis