On Wed, Nov 13, 2013 at 4:09 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
[snip]
> Well, for starters, define "totally inefficient".
I meant that it takes long time compared to my first alternative, which I not posted, due to the subquery, there should be no need for that if I can alias the current resultset with just array_agg(fields)
> Try creating a composite type and caseting:
> ARRAY_AGG((media_files.position, media_files.token) ORDER BY > media_files.position) as media_files
could become
ARRAY_AGG((media_files.position, media_files.token) ORDER BY media_files.position)::foo[] as media_files
where foo is the type with the names as you want them. Also, when not grouping, don't be afraid to try the array() constructor syntax:
Thanks for the suggestion, I tried but I wasn't able to set an alias, I tried something horrific like this:
create type media_file_detail as (position integer AS myalias);
and Google failed me to provide some answers.
select a, array( select b from b where b.id = a.id order by ... )