Sv: ORDER BY custom type
От | Andreas Joseph Krogh |
---|---|
Тема | Sv: ORDER BY custom type |
Дата | |
Msg-id | VisenaEmail.16e.6f0ceedf3cd46f94.160e543e392@tc7-visena обсуждение исходный текст |
Ответ на | ORDER BY custom type (Andreas Joseph Krogh <andreas@visena.com>) |
Список | pgsql-general |
På torsdag 11. januar 2018 kl. 12:00:55, skrev Andreas Joseph Krogh <andreas@visena.com>:
Hi all.I have this custom-type:CREATE TYPE PERSONTYPE AS ( id BIGINT, firstname VARCHAR, lastname VARCHAR, initials VARCHAR );I'm returning a column with this type using the following query, which lists activities and its responsible-person (using the PERSONTYPE):SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW(p.entity_id, p.firstname, p.lastname, p.initials)::persontype FROM onp_crm_person p WHERE act.responsible = p.onp_user_id) AS responsible_person from onp_crm_activity act ORDER BY responsible_person ASC ;But I need to ORDER BY different properties of PERSONTYPE, ie. initials, and am wondering if there exists a mechanism to achieve the following:SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW(p.entity_id, p.firstname, p.lastname, p.initials)::persontype FROM onp_crm_person p WHERE act.responsible = p.onp_user_id) AS responsible_person from onp_crm_activity act ORDER BY responsible_person.initials ASC ;How do I do that?Thanks.
I see that if I wrap the query in an outer query I can do it like this:
SELECT * FROM ( SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW(p.entity_id, p.firstname, p.lastname, p.initials)::persontype FROM onp_crm_person p WHERE act.responsible = p.onp_user_id) AS responsible_person from onp_crm_activity act ) AS q ORDER BY (q.responsible_person).initials ASC ;
Without the outer query:
SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW(p.entity_id, p.firstname, p.lastname, p.initials)::persontype FROM onp_crm_person p WHERE act.responsible = p.onp_user_id) AS responsible_person from onp_crm_activity act ORDER BY (responsible_person).initials ;
It fails with:
ERROR: column "responsible_person" does not exist
LINE 6: ORDER BY (responsible_person).initials
LINE 6: ORDER BY (responsible_person).initials
Is the only solution wrapping with an outer query?
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
В списке pgsql-general по дате отправления: