Обсуждение: select result into string's array

Hi,

I need to store the result of select into an array of string:
   /create or replace function search_engine.test/   /(/   /)/   /    returns integer as $$/   /declare/   /    m_array
text[];/  /begin/   /    for m_array in select * from my_table loop/   /        raise notice 'valor 1: %',m_array;/   /
  end loop;/   /    return 1;/   /end; $$ LANGUAGE plpgsql;/
 


This launch this errors:
   /ERROR: array value must start with "{" or dimension information   SQL state: 22P02   Context: PL/pgSQL function
"test"line 4 at FOR over SELECT rows/
 


Is it possible do this?? May I choose another way?

Thanks in advance

Alberto,

In response to Alberto Asuero Arroyo :
> Hi,
> 
> I need to store the result of select into an array of string:

test=*# select * from foo; t
------foobarbatz
(3 rows)

test=*# select array_agg(t) from foo;  array_agg
----------------{foo,bar,batz}
(1 row)

Helps that?


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

Hello.

You should use an array constructor:

DECLARE
    m_array text[];
    [..]
BEGIN
FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP
[..]
END LOOP;

Regards,
Igrishin Dmitriy.

Dmitriy Igrishin wrote:
> Hello.
>
> You should use an array constructor:
>
> DECLARE
>     m_array text[];
>     [..]
> BEGIN
> FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP
> [..]
> END LOOP;
>
> Regards,
> Igrishin Dmitriy.
>
> 2009/10/9 Alberto Asuero Arroyo <albertoasuero@gmail.com
> <mailto:albertoasuero@gmail.com>>
>
>     Hi,
>
>     I need to store the result of select into an array of string:
>
>        /create or replace function search_engine.test/
>        /(/
>        /)/
>        /    returns integer as $$/
>        /declare/
>        /    m_array text[];/
>        /begin/
>        /    for m_array in select * from my_table loop/
>        /        raise notice 'valor 1: %',m_array;/
>        /    end loop;/
>        /    return 1;/
>        /end; $$ LANGUAGE plpgsql;/
>
>
>     This launch this errors:
>
>        /ERROR: array value must start with "{" or dimension information
>        SQL state: 22P02
>        Context: PL/pgSQL function "test" line 4 at FOR over SELECT rows/
>
>
>     Is it possible do this?? May I choose another way?
>
>     Thanks in advance
>
>     Alberto,
>
>
>
>     --
>     Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
>     <mailto:pgsql-sql@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-sql
>
>


It's has been really useful for my  to solve the dinamic Record
Introspection problem that I had.

Thanks,

Alberto