Force SELECT * on a virtual table to return a HIDDEN column
(1) By Teddr (teddyr) on 2020-05-03 00:39:44 [link] [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.
Imagine:
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 path
, directory
, filename
, inode
returned.
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 file
make a VIEW called "filelist". The VIEW isSELECT *, 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 [source]
Thanks for the quick reply Simon. A VIEW
is a good idea, I'll see if this is workable.