Re: array_agg to array

On Wed, May 16, 2018 at 8:14 AM, Philipp Kraus <
philipp(dot)kraus(at)tu-clausthal(dot)de> wrote:

> Hello,
>
> I have got a function with a reg expr to split chemical formulas e.g. H2O
> -> H2 O.
>
> CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS
> $$
> select array_agg(i::text) as e from ( select unnest( regexp_matches(
> $1, '[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') ) )
> i;
> $$ LANGUAGE SQL IMMUTABLE;
>
> For H2O I get an array with {(H2),(O)}
> How I can return the inner elements as text, I would like to get {H2,O}
> without round brackets?
>

like this?

postgres=# select array_agg(i[1]) as e from regexp_matches( 'H2O',
'[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') t(i);
e
--------
{H2,O}
(1 row)