Hi all,
I have a similar setup like the following:
CRETAE TABLE users (id int primary key, name varchar(30)); INSERT INTO users VALUES (1, 'john'); INSERT INTO users VALUES (2, 'frank'); INSERT INTO users VALUES (3, 'david');
CREATE TABLE groups (id int primary key, name varchar(20), users_ids varchar(100));
INSERT INTO groups VALUES (1, 'group 1', '1,2,3');
The field 'users_ids' in the table 'groups' holds the primary keys of related users (I know that there are better ways to design such a relation), but I like to do the following select statement without joining tables and grouping...
SELECT name, user_list(users_ids) from groups; should return => 'group 1', 'john, frank, david'
I now like to create the user function user_list(), but I didn't find out how to execute a select query inside this user function. Is this possible without locking the database out? Where do I get the database pointer from the already opened database?
Is it possible to create a simple user function that does this?
If it is more easy to get the desired result if I create a relation table between users and groups and use an aggregate function?
Thanks for any suggestions and help Marcel