Date: Mon, 19 Dec 2005 11:06:12 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: how to convert relational column to array?
Message-Id: <20051219110612.26de2268.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm trying to convert a column from a traditional relational form to an array:

create table old_tab(name text, id int, permits text);

newschm3=# select * from old_tab order by name;
   name   |  id   |  permits   
----------+-------+------------
 baker    |   581 | operator
 lawless  |   509 | operator
 lawless  |   509 | originator
 lcalvet  |   622 | originator
 loomis   |   514 | operator
 loomis   |   514 | originator
 pig      |   614 | operator
 pig      |   614 | originator
 pig      |   614 | supervisor

create table new_tab(name text, id int, permits text[]);

-- I insert one row per name:
insert into new_tab select distinct name,id,cast('{}' as text[]) from old_tab;

Now I want to fold all the 'permits' values into the new permits arrays.
I can do:

update new_tab set permits=new_tab.permits||ot.permits from old_tab ot where ot.name=new_tab.name and ot.permits!=all(new_tab.permits);

but this only gets one permits value per name.  Repeating this many times would
eventually get all of them, but it seems there must be a more reliable way?
[I don't care about the *order* of permits values in the array, since order did not exist in old_tab]

Just to be clear, I want to end up with:

newschm3=# select * from new_tab order by name;
  name   | id  |                 permits                  
---------+-----+------------------------------------------
 baker   | 581 | {operator}
 lawless | 509 | {operator,originator}
 lcalvet | 622 | {originator}
 loomis  | 514 | {operator,originator}
 pig     | 614 | {operator,originator,supervisor}

-- George Young
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)