Force SELECT * on a virtual table to return a HIDDEN column
(1) By Teddr (teddyr) on 2020-05-03 00:39:44 [source]
Hello SQLite Forum, I am looking for a way to solve a unique problem. I have several virtual tables that have a common
HIDDEN column. I would like to return that column with a
SELECT * if a query uses the column in a predicate.
CREATE TABLE file(`path` TEXT, `directory` TEXT, `filename` TEXT, `inode` BIGINT, `file_id` TEXT HIDDEN);
And the intended use is:
SELECT * FROM file WHERE path = '/home/me/file.txt';
You should see the
What I want to do is support also returning
file_id by default if someone writes a query:
SELECT * FROM file WHERE file_id = N;
I know the query I proposed could simply be written
SELECT *, file_id ... but I am aiming for a dont-repeat-yourself experience.
I do not think what I want to achieve is possible, but I wanted to ask if anyone knew of alternatives?
(2) By Simon Slavin (slavin) on 2020-05-03 13:13:34 in reply to 1 [link] [source]
Sorry, can't do it. Apart from the one you rejected here are two alternatives:
- Don't mark it as HIDDEN
- Instead of
SELECT * FROM filemake a VIEW called "filelist". The VIEW is
SELECT *, file_id FROM file. Use this view instead of the table itself when you want the extra column.
(3) By Teddr (teddyr) on 2020-05-04 01:39:25 in reply to 2 [link] [source]
Thanks for the quick reply Simon. A
VIEW is a good idea, I'll see if this is workable.