Re: Selecting from table into an array var

On Sat, Dec 19, 2009 at 1:05 AM, Postgres User
<postgres.developer@gmail.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