Re: Selecting from table into an array var

[Postgres User <postgres.developer@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general