Subject: Re: Sort a table by a column value that is a column name?
To: pgsql-sql@lists.postgresql.org
References: <1fe4b9c57fe60ce5e0bdd271c4f7924c3587b775.camel@recarea.com>
From: Rob Sargent <robjsargent@gmail.com>
Message-ID: <727312db-517f-8a27-49c2-1b452ba8fee2@gmail.com>
Date: Fri, 9 Jul 2021 10:32:21 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <1fe4b9c57fe60ce5e0bdd271c4f7924c3587b775.camel@recarea.com>
Content-Type: multipart/alternative;
 boundary="------------0B08360B72C34F6AB4EBEB68"
Content-Language: en-CA
Archived-At: 
 <https://www.postgresql.org/message-id/727312db-517f-8a27-49c2-1b452ba8fee2%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0B08360B72C34F6AB4EBEB68
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 7/9/21 10:59 AM, overland wrote:
> I'm writing a program and I'm aiming to seperate logic from the database and at the same time optimize program performance. I was doing a sort on Postgresql 13 query results in a program but pushing
> the sort to postgresql would optimize performance. So I modified an existing query to do the sorting now and it isn't sorting as I want, but I don't know what to expect. I'm looking to sort a table
> using a column name that is stored in another table. I don't know the column to sort on when the query is written.
>
> An example is below that is quick and dirty and shows what I'm trying to do. There isn't an error when the query is executed, yet the sort doesn't work and fails sighlently. Is there another way to
> accomplish the same thing?
>
>
>
>
>
> CREATE TABLE list (
>      id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
>      attribute TEXT,
>      property TEXT,
>      descid INT
> );
>
>
> CREATE TABLE descriptor (
>      id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
>      name TEXT
> );
>
> INSERT INTO descriptor(name) VALUES('attribute'), ('property');
> INSERT INTO list(attribute, property, descid) VALUES('todo', 'camping', 1);
> INSERT INTO list(attribute, property, descid) VALUES('hooplah', 'glamping', 1);
> INSERT INTO list(attribute, property, descid) VALUES('stuff', 'other', 1);
> INSERT INTO list(attribute, property, descid) VALUES('car', 'bike', 2);
> INSERT INTO list(attribute, property, descid) VALUES('cat', 'hat', 2);
> INSERT INTO list(attribute, property, descid) VALUES('bat', 'that', 2);
>
>
>
>
> SELECT attribute, property, descid
> FROM list AS l
> JOIN descriptor AS d ON l.descid = d.id
> WHERE l.id < 4
> ORDER BY name;
>
>
>
And I take it your having trouble with "name".

Keep in mind that one can sort by values not in the select criteria but 
that won't help the client much.

I think you'll have to do a case analysis on the available values of 
descriptor.text (which I suspect the client is already doing) and 
formulate the request based on the clients choice of, in this case, 
either "attribute" or "property".  You can either prepare a map of all 
possible statements or generate the sql on-demand (or maybe generate the 
prepared statements as needed and populate the map)

--------------0B08360B72C34F6AB4EBEB68
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 7/9/21 10:59 AM, overland wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:1fe4b9c57fe60ce5e0bdd271c4f7924c3587b775.camel@recarea.com">
      <pre class="moz-quote-pre" wrap="">I'm writing a program and I'm aiming to seperate logic from the database and at the same time optimize program performance. I was doing a sort on Postgresql 13 query results in a program but pushing
the sort to postgresql would optimize performance. So I modified an existing query to do the sorting now and it isn't sorting as I want, but I don't know what to expect. I'm looking to sort a table
using a column name that is stored in another table. I don't know the column to sort on when the query is written.

An example is below that is quick and dirty and shows what I'm trying to do. There isn't an error when the query is executed, yet the sort doesn't work and fails sighlently. Is there another way to
accomplish the same thing?


CREATE TABLE list (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    attribute TEXT,
    property TEXT,
    descid INT
);


CREATE TABLE descriptor (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT
);

INSERT INTO descriptor(name) VALUES('attribute'), ('property');   
INSERT INTO list(attribute, property, descid) VALUES('todo', 'camping', 1);
INSERT INTO list(attribute, property, descid) VALUES('hooplah', 'glamping', 1);
INSERT INTO list(attribute, property, descid) VALUES('stuff', 'other', 1);
INSERT INTO list(attribute, property, descid) VALUES('car', 'bike', 2);
INSERT INTO list(attribute, property, descid) VALUES('cat', 'hat', 2);
INSERT INTO list(attribute, property, descid) VALUES('bat', 'that', 2);


SELECT attribute, property, descid
FROM list AS l
JOIN descriptor AS d ON l.descid = d.id
WHERE l.id &lt; 4
ORDER BY name;


</pre>
    </blockquote>
    <font face="Courier New, Courier, monospace">And I take it your
      having trouble with "name".<br>
      <br>
      Keep in mind that one can sort by values not in the select
      criteria but that won't help the client much.  <br>
      <br>
      I think you'll have to do a case analysis on the available values
      of descriptor.text (which I suspect the client is already doing)
      and formulate the request based on the clients choice of, in this
      case, either "attribute" or "property".  You can either prepare a
      map of all possible statements or generate the sql on-demand (or
      maybe generate the prepared statements as needed and populate the
      map)<br>
    </font>
  </body>
</html>

--------------0B08360B72C34F6AB4EBEB68--