On Fri, Dec 18, 2009 at 9:53 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Dec 18, 2009 at 11:35 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2009/12/19 Postgres User <postgres.developer@gmail.com>:
>>> Hi,
>>>
>>> I'm trying to write a very simple function statement to select a
>>> single integer field from a table and save it into an int array. For
>>> some reason I can't seem to find the correct syntax:
>>>
>>> CREATE TABLE sample (
>>> id integer
>>> );
>>>
>>> and then within a function:
>>>
>>> my_array int[];
>>> my_array = SELECT ARRAY(id) FROM sample;
>>>
>>>
>>> This syntax and variations of it don't work. Can anyone show me the
>>> correct approach?
>>>
>>
>> Hello
>>
>> please try SELECT ARRAY(SELECT id FROM sample) or SELECT array_agg(id)
>> FROM sample - if you have 8.4
>
> yup:
>
> array() vs array_agg() vs array[]...which to use?
>
> *) use array[] when building list of scalar values
> *) use array_agg when aggregating (you need to group by something)
> *) use array() everywhere else
>
> merlin
>
Thanks for the replies. I had already tried array_agg (on 8.4) and
ultimately found that the errors were caused by a recursive query.
When I replace the recursive query with a basic SELECT statement, the
code below works.
Apparently, you cannot combine an aggregate function such as
arrayagg() with a recursive SQL statement. This may be a PG bug.
For example, this fails:
DECLARE
cat_list integer[];
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;
with this table def
CREATE TABLE "category" (
"category_id" SERIAL,
"parent_id" INTEGER,
"category_name" VARCHAR(50)
) WITHOUT OIDS;
