From: Gavan Schneider <list.pg.gavan@pendari.org>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Cc: Sbob <sbob@quadratum-braccas.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Subject: Re: sort output per alpha-numeric?
Date: Thu, 02 Dec 2021 10:35:26 +1100
Message-ID: <3ADA4F64-1223-44EF-97E1-0BCA1D9A643F@pendari.org> (raw)
In-Reply-To: <CAKFQuwYL_gQ9JoPehdCj8NKevW77636xSNr2PvSR_BS+8_KhnQ@mail.gmail.com>
References: <6b1034cc-d98d-f3a7-6dd9-264bb0f934d8@quadratum-braccas.com>
<CAKFQuwYL_gQ9JoPehdCj8NKevW77636xSNr2PvSR_BS+8_KhnQ@mail.gmail.com>
On 2 Dec 2021, at 9:50, David G. Johnston wrote:
> On Wed, Dec 1, 2021 at 3:38 PM Sbob <sbob@quadratum-braccas.com>
> wrote:
>
>> I want the displayed print\_size to be ordered by size (8x10, then
>> 11x14, etc)
>>
>> Is there an easy way to do this?
>
>
> You can sort by an expression. For the data as shown the following
> should
>
A little more heavy handed might be to convert the two numbers into a
single integer for ranking, e.g.,
CREATE OR REPLACE FUNCTION image_size_rank ( spec TEXT )
RETURNS INTEGER
LANGUAGE SQL
AS
$$
SELECT
1000 * substring( spec from '^([0-9]+)x.*' )::INTEGER
+ substring( spec from '^[0-9]+x([0-9]+)$' )::INTEGER
;
$$;
so —
pendari=# SELECT image_size_rank ('19x14');
image_size_rank
-----------------
19014
(1 row)
Another wrinkle in the human versus data domain is to constrain the
print size specifications so the 1st dimension is always equal to or
smaller(/larger) than the second (unless the order of the dimensions is
relevant to orientation).
The code snippet above makes 8x10 different to 10x8 but most of us who
did prints in wet baths would consider them them equal.
Gavan Schneider
+61 405 124 883
dr.gavan.schneider@pendari.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: list.pg.gavan@pendari.org, pgsql-admin@lists.postgresql.org, sbob@quadratum-braccas.com, david.g.johnston@gmail.com
Subject: Re: sort output per alpha-numeric?
In-Reply-To: <3ADA4F64-1223-44EF-97E1-0BCA1D9A643F@pendari.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox