Thread: storing record-array in varchar?

On Fri, Jan 13, 2006 at 12:11:40AM +0100, me@alternize.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

hi michael

thanks for your answer.

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

yes. as far as i know, the ms odbc layer does not understand array-fields,
at least not asp3 where the db in question is used ;-)

if the field never has to be outputed to asp your solution will work. i'll
try to implement it, thanks again.

- thomas