INOUT text[],OUT text parameter handling problem

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

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

Предыдущее
От: Maks Materkov
Дата:
Сообщение: SQL query funnel analysis
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: INOUT text[],OUT text parameter handling problem