array of composite type

From: Michael Moore <michaeljmoore(at)gmail(dot)com>
To: postgres list <pgsql-sql(at)postgresql(dot)org>
Subject: array of composite type
Date: 2015-12-18 00:03:09
Message-ID: CACpWLjMxgCDwV1ZDM1Y-FBbHOxhaz_=TUVpgqXrnojddKvSOvA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Thanks,
Mike

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2015-12-18 00:13:02 Re: array of composite type
Previous Message Michael Moore 2015-12-17 18:25:10 Re: cant get what I want from array_to_json (simple)