Thread: array_agg to array

array_agg to array

From

Philipp Kraus

Date:

16 May 2018, 12:14:45

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?

Thanks

Phil

Re: array_agg to array

From

Pavel Stehule

Date:

16 May 2018, 13:00:46

2018-05-16 8:14 GMT+02:00 Philipp Kraus <philipp.kraus@tu-clausthal.de>:

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?

Thanks

maybe you want array_to_string function

postgres=# select array['a','b'];
┌───────┐
│ array │
╞═══════╡
│ {a,b} │
└───────┘
(1 row)

postgres=# select array_to_string(array['a','b'],'');
┌─────────────────┐
│ array_to_string │
╞═════════════════╡
│ ab │
└─────────────────┘
(1 row)

Regards

Pavel

Phil

Re: array_agg to array

From

Torsten Förtsch

Date:

16 May 2018, 13:10:54

On Wed, May 16, 2018 at 8:14 AM, Philipp Kraus <philipp.kraus@tu-clausthal.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)

Re: array_agg to array

From

Philipp Kraus

Date:

16 May 2018, 13:15:07

Am 16.05.2018 um 09:10 schrieb Torsten Förtsch <tfoertsch123@gmail.com>:

select array_agg(i[1]) as e from regexp_matches( 'H2O', '[0-9]*[A-Z][a-z]?\d*|$(?:[^()]*(?:\(.*$)?[^()]*)+\)\d+', 'g') t(i);

perfect, this helps with the t(i) call

Phil

Attachment

signature.asc