Thread: SELECT INTO array[i] with PL/pgSQL

SELECT INTO array[i] with PL/pgSQL

From

Julia Jacobson

Date:

07 February 2011, 20:49:48

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

Re: SELECT INTO array[i] with PL/pgSQL

From

Edoardo Panfili

Date:

07 February 2011, 21:06:33

On 07/02/11 22.15, Julia Jacobson wrote:
> 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';

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

Re: SELECT INTO array[i] with PL/pgSQL

From

Dmitriy Igrishin

Date:

12 February 2011, 12:41:00

Hey,

2011/2/8 Edoardo Panfili <edoardo@aspix.it>

On 07/02/11 22.15, Julia Jacobson wrote:
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';

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

--
// Dmitriy.

Re: SELECT INTO array[i] with PL/pgSQL

From

Merlin Moncure

Date:

14 February 2011, 13:33:46

On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia.jacobson@arcor.de> wrote:
> 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?

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

Re: SELECT INTO array[i] with PL/pgSQL

From

Dmitriy Igrishin

Date:

14 February 2011, 13:38:02

2011/2/14 Merlin Moncure <mmoncure@gmail.com>

On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia.jacobson@arcor.de> wrote:
> 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?

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.

array_agg() is more readable and clear :-P

merlin

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

--
// Dmitriy.

Re: SELECT INTO array[i] with PL/pgSQL

From

Merlin Moncure

Date:

14 February 2011, 20:58:04

On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> 2011/2/14 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia.jacobson@arcor.de>
>> wrote:
>> > 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?
>>
>> 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.
>
> 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

Re: SELECT INTO array[i] with PL/pgSQL

From

Dmitriy Igrishin

Date:

15 February 2011, 12:51:21

2011/2/15 Merlin Moncure <mmoncure@gmail.com>

On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> 2011/2/14 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia.jacobson@arcor.de>
>> wrote:
>> > 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?
>>
>> 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.
>
> 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

--
// Dmitriy.

Re: SELECT INTO array[i] with PL/pgSQL

From

Merlin Moncure

Date:

15 February 2011, 21:24:55

On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>
>
> 2011/2/15 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr@gmail.com>
>> wrote:
>> > 2011/2/14 Merlin Moncure <mmoncure@gmail.com>
>> >>
>> >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson
>> >> <julia.jacobson@arcor.de>
>> >> wrote:
>> >> > 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?
>> >>
>> >> 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.
>> >
>> > 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

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

Re: SELECT INTO array[i] with PL/pgSQL

From

Dmitriy Igrishin

Date:

17 February 2011, 16:42:41

2011/2/16 Merlin Moncure <mmoncure@gmail.com>

On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>
>
> 2011/2/15 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr@gmail.com>
>> wrote:
>> > 2011/2/14 Merlin Moncure <mmoncure@gmail.com>
>> >>
>> >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson
>> >> <julia.jacobson@arcor.de>
>> >> wrote:
>> >> > 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?
>> >>
>> >> 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.
>> >
>> > 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

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

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

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

merlin

--
// Dmitriy.