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
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 |