Sv: ORDER BY custom type

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

På torsdag 11. januar 2018 kl. 12:00:55, skrev Andreas Joseph Krogh <andreas@xxxxxxxxxx>:

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

andreas@xxxxxxxxxx

www.visena.com