Thread: Selecting from table into an array var
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?
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 Regards Pavel Stehule > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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
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;
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
On Sat, Dec 19, 2009 at 6:56 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > 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 Thanks, your syntax does compile and run. This is where it gets interesting. With your syntax (and variations of it), I'm able to successfully compile and execute. However, as soon as I add a dozen rows to the table, the query failes to complete. It runs until timing out. This prolem does not occur when I remove the SELECT INTO ARRAY statement and simply run the recursive query. Has anyone else seen this behavior?
On Sat, Dec 19, 2009 at 1:30 PM, Postgres User <postgres.developer@gmail.com> wrote: > > Thanks, your syntax does compile and run. > > This is where it gets interesting. With your syntax (and variations > of it), I'm able to successfully compile and execute. However, as > soon as I add a dozen rows to the table, the query failes to complete. > It runs until timing out. > This prolem does not occur when I remove the SELECT INTO ARRAY > statement and simply run the recursive query. > > Has anyone else seen this behavior? are you sure you don't have a circular dependency? maybe you have a variable/column name clash? merlin
Great call, someone did hose the data. Oddly enough the circular reference caused no problem when running the stand alone recursive SQL (with clause). On Sat, Dec 19, 2009 at 10:41 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Sat, Dec 19, 2009 at 1:30 PM, Postgres User > <postgres.developer@gmail.com> wrote: >> >> Thanks, your syntax does compile and run. >> >> This is where it gets interesting. With your syntax (and variations >> of it), I'm able to successfully compile and execute. However, as >> soon as I add a dozen rows to the table, the query failes to complete. >> It runs until timing out. >> This prolem does not occur when I remove the SELECT INTO ARRAY >> statement and simply run the recursive query. >> >> Has anyone else seen this behavior? > > are you sure you don't have a circular dependency? maybe you have a > variable/column name clash? > > merlin >
On Sat, Dec 19, 2009 at 7:08 PM, Postgres User <postgres.developer@gmail.com> wrote: > Great call, someone did hose the data. Oddly enough the circular > reference caused no problem when running the stand alone recursive SQL > (with clause). > no problem. I would advise guarding against circular dependencies either with a trigger on the relationship table, a recursion limit in the query itself, or both. merlin