Thread: 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.<br /><br />You should use an array constructor:<br /><br />DECLARE<br /> m_array text[];<br /> [..]<br />BEGIN<br/>FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP<br />[..]<br />END LOOP;<br /><br />Regards,<br/>Igrishin Dmitriy.<br /><br /><div class="gmail_quote">2009/10/9 Alberto Asuero Arroyo <span dir="ltr"><<ahref="mailto:albertoasuero@gmail.com">albertoasuero@gmail.com</a>></span><br /><blockquote class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Hi,<br/><br /> I need to store the result of select into an array of string:<br /><br /> /create or replace function search_engine.test/<br/> /(/<br /> /)/<br /> / returns integer as $$/<br /> /declare/<br /> / m_arraytext[];/<br /> /begin/<br /> / for m_array in select * from my_table loop/<br /> / raise notice'valor 1: %',m_array;/<br /> / end loop;/<br /> / return 1;/<br /> /end; $$ LANGUAGE plpgsql;/<br /><br/><br /> This launch this errors:<br /><br /> /ERROR: array value must start with "{" or dimension information<br/> SQL state: 22P02<br /> Context: PL/pgSQL function "test" line 4 at FOR over SELECT rows/<br /><br/><br /> Is it possible do this?? May I choose another way?<br /><br /> Thanks in advance<br /><br /> Alberto,<br /><fontcolor="#888888"><br /><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/>
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