Hi
I implemented a custom function that returns a comma separated list of
primary keys as a string by making recursive queries. This works well
if I don't use subqueries. But I like to use subqueries like this
SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));
Below some example data I used to test my custom function...
Is it possible to implement a custom function where the result can be
used in a subquery like above?
Regards
Marcel
Here a dump of an example database:
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO "users" VALUES(1,'Marge');
INSERT INTO "users" VALUES(2,'Homer');
INSERT INTO "users" VALUES(3,'Lisa');
INSERT INTO "users" VALUES(4,'Bart');
INSERT INTO "users" VALUES(5,'Maggie');
CREATE TABLE relations (parent INTEGER, child INTEGER);
INSERT INTO "relations" VALUES(1,3);
INSERT INTO "relations" VALUES(1,4);
INSERT INTO "relations" VALUES(1,5);
INSERT INTO "relations" VALUES(2,3);
INSERT INTO "relations" VALUES(2,4);
And here the output of my custom function (parents), which is similar
to the builtin function group_concat():
sqlite> SELECT name,parents('relations', id) FROM users WHERE id = 3;
name parents('relations', id)
---------- ------------------------
Lisa 1,2
But the following query returns nothing
SELECT name FROM users WHERE id IN (SELECT parents('relations', id)
FROM users WHERE id = 3);
because the result of the custom function is a string ("1,2")...
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
