Re: storing record-array in varchar?

On Fri, Jan 13, 2006 at 12:11:40AM +0100, me(at)alternize(dot)com wrote:
> how can something like this be achieved:
>
> UPDATE mytable SET m_list = (SELECT DISTINCT s_id FROM sometable
> WHERE s_id > 6000) WHERE m_id = 10;

In 7.4 and later, if m_list were an array then you could do this:

UPDATE mytable SET m_list = ARRAY(SELECT ...) WHERE ...

> the field m_list would then hold something like '6001, 6002, 6003, 7000',
> which ideally i could later use for something like SELECT * FROM sometable
> JOIN mytable WHERE s_id IN m_list AND m_id = 10;

Again, if m_list were an array then you could do something like this:

SELECT *
FROM sometable AS s
JOIN mytable AS m ON s.s_id = ANY(m.m_list)
WHERE m_id = 10;

> field m_list would idealy be a varchar if possible...

Why? It could be done but an array seems more suitable for what
you're describing.

Is there a reason you want

m_id | m_list
------+-----------------------
10 | {6001,6002,6003,7000}

instead of the more conventional

m_id | s_id
------+------
10 | 6001
10 | 6002
10 | 6003
10 | 7000

?

--
Michael Fuhr