SQLite Forum

Force SELECT * on a virtual table to return a HIDDEN column
Login

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 [source]

Sorry, can't do it. Apart from the one you rejected here are two alternatives:

  1. Don't mark it as HIDDEN
  2. Instead of SELECT * FROM file make 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.