Message-ID: <1fe4b9c57fe60ce5e0bdd271c4f7924c3587b775.camel@recarea.com>
Subject: Sort a table by a column value that is a column name?
From: overland <overland@recarea.com>
To: pgsql-sql@lists.postgresql.org
Date: Fri, 09 Jul 2021 09:59:25 -0700
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.30.5-1.1 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/1fe4b9c57fe60ce5e0bdd271c4f7924c3587b775.camel%40recarea.com>
Precedence: bulk

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;