Postgres Pro
Downloads
Home   >   mailing lists

Thread: ORDER BY custom type

ORDER BY custom type

From
Andreas Joseph Krogh
Date:
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.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Sv: ORDER BY custom type

From
Andreas Joseph Krogh
Date:
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
 
 
Is the only solution wrapping with an outer query?
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment