Обсуждение: 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