Re: array of composite type

On Thu, Dec 17, 2015 at 5:03 PM, Michael Moore <michaeljmoore(at)gmail(dot)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.​