Re: Selecting from table into an array var

On Sat, Dec 19, 2009 at 6:56 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Sat, Dec 19, 2009 at 1:05 AM, Postgres User
> <postgres(dot)developer(at)gmail(dot)com> wrote:
>>
>> BEGIN
>> SELECT array_agg(category_id) INTO cat_list FROM (
>>         WITH RECURSIVE subcategory AS
>>                (
>>                SELECT * FROM category
>>                WHERE category_id = p_category_id
>>
>>                UNION ALL
>>
>>                        SELECT c.*
>>                        FROM category AS c
>>                        INNER JOIN subcategory AS sc ON (c.category_id = sc.parent_id)
>>                )
>>                SELECT category_id FROM subcategory
>>                ORDER BY Coalesce(parent_id, 0) DESC
>>  ) c;
>> END;
>
> works for me (i didn't put any data in though).  the above is probably
> better written using array() notation as I mentioned above:
>
> SELECT array
> (
>  WITH RECURSIVE subcategory AS
>  (
>    SELECT * FROM category
>      WHERE category_id = p_category_id
>    UNION ALL
>      SELECT c.*
>        FROM category AS c
>        INNER JOIN subcategory AS sc ON (c.category_id = sc.parent_id)
>  )
>  SELECT category_id FROM subcategory
>  ORDER BY Coalesce(parent_id, 0) DESC
> ) INTO cat_list;
>
> Also if you want more than just the ID stacked in the array the above
> can be reworked in to an array of the 'category' type.
>
> merlin

Thanks, your syntax does compile and run.

This is where it gets interesting. With your syntax (and variations
of it), I'm able to successfully compile and execute. However, as
soon as I add a dozen rows to the table, the query failes to complete.
It runs until timing out.
This prolem does not occur when I remove the SELECT INTO ARRAY
statement and simply run the recursive query.

Has anyone else seen this behavior?