Re: Selecting from table into an array var

On Fri, Dec 18, 2009 at 9:53 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Fri, Dec 18, 2009 at 11:35 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2009/12/19 Postgres User <postgres(dot)developer(at)gmail(dot)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;