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