Re: Selecting from table into an array var

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