Home > mailing lists

Thread: Array_agg and dimensions in Array

Array_agg and dimensions in Array

From

Mike Martin

Date:

14 January 2019, 02:05:21

I have come across a problem which I cant seem to solve in a nice way

Basically I have a (small) table of tags

What I need to is combine two concatenated fields with a literal value as an array element.

First thought was using array_agg with a pre-created array as

select array_agg(ARRAY['-metadata',optname||'='||optvalue])) metaopt from encodeopts

where alias is not null and opttype in ('tag','tagn')
group by transref,fileid ) a

However this results in a multi-dimensional array, rather than a single dimensioned one, which makes it impossible to join with the rest of an array created elsewhere in the query

This works, but is very cludgy

select ARRAY['-map_metadata','-1']||array_agg(metaopt) from
(select unnest(array_agg(ARRAY['-metadata',optname||'='||optvalue])) metaopt from encodeopts
where alias is not null and opttype in ('tag','tagn')
group by transref,fileid ) a

So does this

select string_to_array(string_agg('-metadata',||'||'||optname||'='||optvalue])),'||') metaopt from encodeopts
where alias is not null and opttype in ('tag','tagn')
group by transref,fileid

but again cludgy

Any ideas appreciated

Mike

Re: Array_agg and dimensions in Array

From

Alexey Bashtanov

Date:

16 January 2019, 19:34:59

Hi Mike,

I have come across a problem which I cant seem to solve in a nice way
Basically I have a (small) table of tags
What I need to is combine two concatenated fields with a literal value as an array element.

You can create a custom aggregate function like this:

alexey@[local]/alexey=# create aggregate array_cat_agg(anyarray) (SFUNC = array_cat, STYPE = anyarray, COMBINEFUNC = array_cat, PARALLEL = SAFE);
CREATE AGGREGATE

And use it like this:

alexey@[local]/alexey=# select grp, array_cat_agg(array['--foo', bar || '=' || baz]) from (values ('g1', 'a', 'b'), ('g1', 'c', 'd'), ('g2', 'e', 'f')) _ (grp, bar, baz) group by grp;
┌─────┬───────────────────────┐
│ grp │ array_cat_agg │
├─────┼───────────────────────┤
│ g2 │ {--foo,e=f} │
│ g1 │ {--foo,a=b,--foo,c=d} │
└─────┴───────────────────────┘
(2 rows)

Is that what you need?

Best, Alex