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
