SQLite Forum

Re-use and execute SQL code stored in tables (eval?)
Login

Re-use and execute SQL code stored in tables (eval?)

(1) By BohwaZ (bohwaz) on 2020-11-05 20:54:20 [link] [source]

Hey, I know this has already probably been answered, but is it possible to re-use the content of a table cell in a query?

Not just executing a query stored in a table, but using one of its columns as SQL code for another query.

For example:

CREATE TABLE user_pricings (condition TEXT);
CREATE TABLE users (name, age);
INSERT INTO users ('bohwaz', 42);
INSERT INTO user_pricings ('CASE WHEN age < 18 THEN 42.00 ELSE 55.00 END');

SELECT *, `(SELECT condition FROM user_pricings)` FROM users;

Which would be interpreted as:

SELECT *, CASE WHEN age < 18 THEN 42.00 ELSE 55.00 END FROM users;

Thanks :)

(2) By Keith Medcalf (kmedcalf) on 2020-11-05 22:08:05 in reply to 1 [link] [source]

You would have to do the 'building of the dynamic SQL yourself' in your application. Alternatively you could make your 'snippet' of SQL something that could actually be evaluated as an SQL statement (it is not in the example shown) and modify the eval() function to bind parameters (after the first which is the statement) as parameters (currently it takes one or two parameters, the second of which is the separator).

eg:

make the SQL "condition" contain 'select case when ? < 18 then 42.00 else 55.00 end' so that it is actually well formed SQL, then use a modified eval() function that binds parameters after the first to the statement before running it, then you could use something like:

select *, eval((select condition from user_pricings), age) from users;

(3) By Larry Brasfield (LarryBrasfield) on 2020-11-05 22:08:54 in reply to 1 [source]

(4) By little-brother on 2020-11-05 22:47:02 in reply to 1 [link] [source]

(5) By BohwaZ (bohwaz) on 2020-11-08 00:41:14 in reply to 4 [link] [source]

Thank you, I didn't know about generated columns, sounds nice, but unfortunately this is a widely distributed web-app and the currently required version of SQLite is only 3.16, and I can't use any extension either. I knew about the eval() extension but can't use it here.

I will just do an extra loop in the app code itself then.

Thanks everyone for your help :)