[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Order by lower(column-alias) doesn't work...
From:       Andreas Joseph Krogh <andreas () visena ! com>
Date:       2020-05-28 13:36:06
Message-ID: VisenaEmail.33.d65bf4e0fa1ac41d.1725b7dfe83 () tc7-visena
[Download RAW message or body]

[Attachment #2 (multipart/related)]

[Attachment #4 (multipart/alternative)]


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
[Attachment #7 (text/html)]

<div>På torsdag 28. mai 2020 kl. 15:26:42, skrev Tom Lane &lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;:</div>

<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt \
0.8ex; padding-left: 1ex;"> <div style="display:inline; font-family: monospace; \
font-size: 12px;">Andreas Joseph Krogh &lt;andreas@visena.com&gt; writes:<br> &gt; Is \
there a way to define &quot;sorting-rules&quot; on custom-types so that I can \
have<br> &gt; ORDER BY &lt;my_array_of_custom_types_alias&gt; and PG will pick my \
custom odering?<br> <br>
You'd have to write your own type, which would be a lotta work :-(.<br>
<br>
A possible partial answer is to define the composite type as<br>
<br>
firstname citext, lastname citext, other-fields-here<br>
<br>
and then the regular composite-type comparison rule would give you<br>
approximately what you said you wanted ... but only approximately.<br>
<br>
regards, tom lane</div>
</blockquote>

<div>  </div>

<div>  </div>

<div>Hm, ok. I think the most non-intrusive way for me is to craft a sub-select \
producing the &quot;varchar-string-flattened&quot; so I can order by that \
alias.</div>

<div>  </div>

<div class="visena-email-signature" \
data-visena-message-id="&lt;VisenaEmail.33.d65bf4e0fa1ac41d.1725b7dfe83@tc7-visena>"> \
<div>--<br> Andreas Joseph Krogh</div>
</div>



[prev in list] [next in list] [prev in thread] [next in thread]