Order by lower(column-alias) doesn't work...
Hi.
This works:
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from
onp_crm_person p order by fullname;
But this doesn't:
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from
onp_crm_person p order by lower(fullname);
ERROR: column "fullname" does not exist
LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);
This is just an example-query, in my real query I have a more complex query
generating an array of a custom-type which is then referenced to as a
column-alias, and then ORDER BY on a function tranforming this array doesn't
work:
SELECT ...
ARRAY(WITH RECURSIVE t AS (SELECT
...
) SELECT ROW(t.entity_id, t.name)::BigIntVarChar from t order by level DESC)
as folder_parent_array
ORDER BY bigintvarchar_to_text_value_flatten(folder_parent_array) ASC;
column "folder_parent_array" does not exist
What bigintvarchar_to_text_value_flatten() does is to take the "varchar"-part
out of the BigintVarchar-type and "flatten" the array by that value so that it
sorts nicely.
Any way round this?
--
Andreas Joseph Krogh
On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:
This works:
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from onp_crm_person p order by fullname;But this doesn't:
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from onp_crm_person p order by lower(fullname);
ERROR: column "fullname" does not exist
LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);
Wrap the original query in either a CTE or a temporary table.
eg
=> SELECT REPLACE(name, '_', ' ') AS nm FROM subs ORDER BY lower(nm);
ERROR: column "nm" does not exist
=> SELECT * FROM (SELECT REPLACE(name, '_', ' ') AS nm FROM subs) AS t
ORDER BY lower(nm);
[results]
=> WITH t AS (SELECT REPLACE(name, '_', ' ') AS nm FROM subs) SELECT
* FROM t ORDER BY lower(nm);
[results]
Geoff
På torsdag 28. mai 2020 kl. 14:50:54, skrev Geoff Winkless <pgsqladmin@geoff.dj
<mailto:pgsqladmin@geoff.dj>>:
On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:
This works:
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from
onp_crm_person p order by fullname;
But this doesn't:
select p.firstname, p.lastname, p.firstname || p.lastname as fullname from
onp_crm_person p order by lower(fullname);
ERROR: column "fullname" does not exist
LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);
Wrap the original query in either a CTE or a temporary table.
eg
[..]
Yea, I was hoping to avoid that, as the query is generated and rewriting it is
a pain...
Is there a way to define "sorting-rules" on custom-types so that I can have
ORDER BY <my_array_of_custom_types_alias> and PG will pick my custom odering?
--
Andreas Joseph Krogh
Andreas Joseph Krogh <andreas@visena.com> writes:
Is there a way to define "sorting-rules" on custom-types so that I can have
ORDER BY <my_array_of_custom_types_alias> and PG will pick my custom odering?
You'd have to write your own type, which would be a lotta work :-(.
A possible partial answer is to define the composite type as
firstname citext, lastname citext, other-fields-here
and then the regular composite-type comparison rule would give you
approximately what you said you wanted ... but only approximately.
regards, tom lane
På torsdag 28. mai 2020 kl. 15:26:42, skrev Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>>:
Andreas Joseph Krogh <andreas@visena.com> writes:
Is there a way to define "sorting-rules" on custom-types so that I can have
ORDER BY <my_array_of_custom_types_alias> and PG will pick my custom
odering?
You'd have to write your own type, which would be a lotta work :-(.
A possible partial answer is to define the composite type as
firstname citext, lastname citext, other-fields-here
and then the regular composite-type comparison rule would give you
approximately what you said you wanted ... but only approximately.
regards, tom lane
Hm, ok. I think the most non-intrusive way for me is to craft a sub-select
producing the "varchar-string-flattened" so I can order by that alias.
--
Andreas Joseph Krogh