Re: array of composite type

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: array of composite type
Дата
Msg-id CAKFQuwbfD_y_TEjcAr_tzO=ayrR6yqZkr4gmBtMW7mN7z=HNPw@mail.gmail.com
обсуждение исходный текст
Ответ на array of composite type  (Michael Moore <michaeljmoore@gmail.com>)
Список pgsql-sql
On Thu, Dec 17, 2015 at 5:03 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
-- create types to simulate input parameter
​​
CREATE TYPE kv_pair  AS(ckey text, cvalue text);
CREATE TYPE kv_pair_tab  as  (kv kv_pair[]);

load some values:
​​
    arr.kv[0] := CAST( row('var1','value of var1') AS kv_pair);
    arr.kv[1] := CAST( row('var2','value of var2') AS kv_pair);
    arr.kv[2] := CAST( row('var3','value of var3') AS kv_pair);

how can I write a FOREACH to retrieve the values:

var1, var2 and var3. 

​​
DO $$declare
   arr kv_pair_tab;
   kp kv_pair; 
begin
  foreach kp in array arr 
  loop
    RAISE NOTICE '#loop %', kp.ckey;
  end loop;
end$$;
ERROR:  FOREACH expression must yield an array, not type kv_pair_tab

I understand what the error message is telling me but. It wants an array, but the composite type,kv_pair IS the array. 

I've looked at dozens of examples but they are always slightly different than what I need. 


Me thinks you've been staring at this too long​
 
​:)

"arr" is of type "kv_pair_tab" which is a composite type having a component (kv) that is an array.  You correctly assign to the "kv" property of "arr" when loading data but then fail to specify "kv" when attempting to retrieve that same data.

​thusly:
foreach kp in array arr.kv

​David J.​


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

Предыдущее
От: Michael Moore
Дата:
Сообщение: array of composite type
Следующее
От: Yevgeny
Дата:
Сообщение: Stucks in the middle