Re: How to alias attributes in an ARRAY_AGG expression

On Wed, Nov 13, 2013 at 4:09 PM, Merlin Moncure <mmoncure(at)gmail(dot)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 ...
> )
>

Thanks, will do
c