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