SELECT INTO array[i] with PL/pgSQL

[Julia Jacobson]

Dear PostgreSQL community,

Please consider the following minimal example:

CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');

CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';

The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web
search, but a more verbose error message and some additional explanation
would help me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

Regards,
Julia

[Edoardo Panfili]

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';

this one seems work...

CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
b[i]:= value FROM example WHERE row_id=i;
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';


Edoardo

[Dmitriy Igrishin]

Hey,

Post by Edoardo Panfili

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';

this one seems work...
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
b[i]:= value FROM example WHERE row_id=i;
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
Edoardo

CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
DECLARE
a_ text[];
t_ text; -- just for example of usage
BEGIN
SELECT INTO a_ array_agg(dat) FROM t1;

-- usage example:
FOR t_ IN SELECT unnest(a_) LOOP
RAISE NOTICE '%', t_;
END LOOP;
END;
$function$

create table t1 (id serial, dat text);
insert into t1 (dat) select 'dima';
insert into t1 (dat) select 'alex';
insert into t1 (dat) select 'vasya';

dmitigr=> select f1();
NOTICE: dima
NOTICE: alex
NOTICE: vasya

[Merlin Moncure]

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
 a TEXT;
 b TEXT[];
 i INT;
BEGIN
 FOR i in 1..3 LOOP
   SELECT INTO a    value FROM example WHERE row_id=i;  -- This works
   b[i] := a;                                           -- perfectly!
--  SELECT INTO b[i] value FROM example WHERE row_id=i;  Doesn't work!
 END LOOP;
   RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web search,
but a more verbose error message and some additional explanation would help
me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

You got some good answers downthread but the key with arrays in
pl/pgsql is to avoid iterative processing whenever possible,
*especially* when building the array. The key is to convert the loop
to a query, and wrap the query with the array() syntax construction.
For example, your construction above could be written like this:

select array(select value from example where row_id in (1,2,3)) into b;

you can also use row types:
DECLARE
examples example[];
BEGIN
select array(select e from example e where row_id in (1,2,3)) into examples;

Using array(...) or array_agg() vs building with assignment or
array_cat() will be MUCH faster.

merlin

[Dmitriy Igrishin]

Post by Julia Jacobson

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an

array.

Post by Julia Jacobson
I have read the documentation carefully and have done extensive web

search,

Post by Julia Jacobson
but a more verbose error message and some additional explanation would

help

Post by Julia Jacobson
me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

You got some good answers downthread but the key with arrays in
pl/pgsql is to avoid iterative processing whenever possible,
*especially* when building the array. The key is to convert the loop
to a query, and wrap the query with the array() syntax construction.
select array(select value from example where row_id in (1,2,3)) into b;
DECLARE
examples example[];
BEGIN
select array(select e from example e where row_id in (1,2,3)) into examples;
Using array(...) or array_agg() vs building with assignment or
array_cat() will be MUCH faster.

array_agg() is more readable and clear :-P

Post by Julia Jacobson
merlin
--
http://www.postgresql.org/mailpref/pgsql-general

[Merlin Moncure]

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
 a TEXT;
 b TEXT[];
 i INT;
BEGIN
 FOR i in 1..3 LOOP
   SELECT INTO a    value FROM example WHERE row_id=i;  -- This works
   b[i] := a;                                           -- perfectly!
--  SELECT INTO b[i] value FROM example WHERE row_id=i;  Doesn't work!
 END LOOP;
   RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web search,
but a more verbose error message and some additional explanation would help
me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

You got some good answers downthread but the key with arrays in
pl/pgsql is to avoid iterative processing whenever possible,
*especially* when building the array.  The key is to convert the loop
to a query, and wrap the query with the array() syntax construction.
select array(select value from example where row_id in (1,2,3)) into b;
DECLARE
 examples example[];
BEGIN
 select array(select e from example e where row_id in (1,2,3)) into
examples;
Using array(...) or array_agg() vs building with assignment or
array_cat() will be MUCH faster.

array_agg() is more readable and clear :-P

That's debatable, but putting that aside it's still good to learn the
ins and outs of array() array_agg aggregates, and array() is syntax
that converts set returning one column subquery into an array. They
are NOT the same thing, and when nesting it's trivial to stack layers
with array() that is difficult/impossible with array_agg().

merlin

[Dmitriy Igrishin]

Post by Merlin Moncure

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This works
b[i] := a; -- perfectly!
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web search,
but a more verbose error message and some additional explanation would help
me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

You got some good answers downthread but the key with arrays in
pl/pgsql is to avoid iterative processing whenever possible,
*especially* when building the array. The key is to convert the loop
to a query, and wrap the query with the array() syntax construction.
select array(select value from example where row_id in (1,2,3)) into b;
DECLARE
examples example[];
BEGIN
select array(select e from example e where row_id in (1,2,3)) into examples;
Using array(...) or array_agg() vs building with assignment or
array_cat() will be MUCH faster.

array_agg() is more readable and clear :-P

That's debatable, but putting that aside it's still good to learn the
ins and outs of array() array_agg aggregates, and array() is syntax
that converts set returning one column subquery into an array. They
are NOT the same thing, and when nesting it's trivial to stack layers
with array() that is difficult/impossible with array_agg().
merlin

Please note, that OP wants array aggregate of column of table rather
than array aggregate of composite type. So, in case of OP array_agg()
is much cleaner and its not debatable:

select into examples array_agg(value) from example;
VS
select array(select e from example e where row_id in (1,2,3)) into examples

[Merlin Moncure]

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Dmitriy Igrishin

On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
 a TEXT;
 b TEXT[];
 i INT;
BEGIN
 FOR i in 1..3 LOOP
   SELECT INTO a    value FROM example WHERE row_id=i;  -- This works
   b[i] := a;                                           -- perfectly!
--  SELECT INTO b[i] value FROM example WHERE row_id=i;  Doesn't work!
 END LOOP;
   RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web search,
but a more verbose error message and some additional explanation would
help
me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

You got some good answers downthread but the key with arrays in
pl/pgsql is to avoid iterative processing whenever possible,
*especially* when building the array.  The key is to convert the loop
to a query, and wrap the query with the array() syntax construction.
select array(select value from example where row_id in (1,2,3)) into b;
DECLARE
 examples example[];
BEGIN
 select array(select e from example e where row_id in (1,2,3)) into
examples;
Using array(...) or array_agg() vs building with assignment or
array_cat() will be MUCH faster.

array_agg() is more readable and clear :-P

That's debatable, but putting that aside it's still good to learn the
ins and outs of array() array_agg aggregates, and array() is syntax
that converts set returning one column subquery into an array.  They
are NOT the same thing, and when nesting it's trivial to stack layers
with array() that is difficult/impossible with array_agg().
merlin

Please note, that OP wants array aggregate of column of table rather
than array aggregate of composite type. So, in case of OP array_agg()
select into examples array_agg(value) from example;
VS
select array(select e from example e where row_id in (1,2,3)) into examples

er, you are not comparing apples to apples:
select into examples array_agg(value) from example;
VS
select into examples array(select value from example);

Yeah, array_agg is kinda sorta easier, but the point I was making is
that array() can be employed against a much broader array of problems,
not just when using composite types.

for example,
select f.*, array(select value from bar where foo_id = f.foo_id) as
values from foo;
vs
select f.*, array_agg(value) as values from foo group by foo.a, foo.b etc

Are completely different queries, and have non-trivial plan interactions.

merlin

[Dmitriy Igrishin]

Post by Julia Jacobson

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Dmitriy Igrishin

On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson

Post by Julia Jacobson
Dear PostgreSQL community,
CREATE TABLE example (row_id SERIAL, value TEXT);
INSERT INTO example(value) VALUES ('val1');
INSERT INTO example(value) VALUES ('val2');
INSERT INTO example(value) VALUES ('val3');
CREATE OR REPLACE FUNCTION foo()
RETURNS TEXT
AS
$$
DECLARE
a TEXT;
b TEXT[];
i INT;
BEGIN
FOR i in 1..3 LOOP
SELECT INTO a value FROM example WHERE row_id=i; -- This

works

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Dmitriy Igrishin

Post by Julia Jacobson
b[i] := a; --

perfectly!

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Dmitriy Igrishin

Post by Julia Jacobson
-- SELECT INTO b[i] value FROM example WHERE row_id=i; Doesn't work!
END LOOP;
RETURN b[2];
END;
$$
LANGUAGE 'plpgsql';
The error message indicates a problem with selecting values into an array.
I have read the documentation carefully and have done extensive web search,
but a more verbose error message and some additional explanation would
help
me to understand the problem.
Is there a way to select values directly into an array without the
assignment from an additional variable?

You got some good answers downthread but the key with arrays in
pl/pgsql is to avoid iterative processing whenever possible,
*especially* when building the array. The key is to convert the loop
to a query, and wrap the query with the array() syntax construction.
select array(select value from example where row_id in (1,2,3)) into

b;

Post by Dmitriy Igrishin

Post by Merlin Moncure

Post by Dmitriy Igrishin

DECLARE
examples example[];
BEGIN
select array(select e from example e where row_id in (1,2,3)) into examples;
Using array(...) or array_agg() vs building with assignment or
array_cat() will be MUCH faster.

array_agg() is more readable and clear :-P

That's debatable, but putting that aside it's still good to learn the
ins and outs of array() array_agg aggregates, and array() is syntax
that converts set returning one column subquery into an array. They
are NOT the same thing, and when nesting it's trivial to stack layers
with array() that is difficult/impossible with array_agg().
merlin

Please note, that OP wants array aggregate of column of table rather
than array aggregate of composite type. So, in case of OP array_agg()
select into examples array_agg(value) from example;
VS
select array(select e from example e where row_id in (1,2,3)) into

examples
select into examples array_agg(value) from example;
VS
select into examples array(select value from example);
Yeah, array_agg is kinda sorta easier, but the point I was making is
that array() can be employed against a much broader array of problems,
not just when using composite types.
for example,
select f.*, array(select value from bar where foo_id = f.foo_id) as
values from foo;
vs
select f.*, array_agg(value) as values from foo group by foo.a, foo.b etc

Huh? I don't clearly understand where here "comparison of apples to apples"
?

Post by Julia Jacobson
Are completely different queries, and have non-trivial plan interactions.
merlin