Re: How to alias attributes in an ARRAY_AGG expression

Поиск
Список
Период
Сортировка
От Claudio Poli
Тема Re: How to alias attributes in an ARRAY_AGG expression
Дата
Msg-id CANp6QoLvHV+ZneCCFm3WjRcw=t2BC6b014fYzEP1054XxpXisg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: How to alias attributes in an ARRAY_AGG expression  (Merlin Moncure <mmoncure@gmail.com>)
Ответы Re: How to alias attributes in an ARRAY_AGG expression
Список pgsql-novice
Дерево обсуждения

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 ...
  )

Thanks, will do
c

В списке pgsql-novice по дате отправления: