Re: [GENERAL] Allowing Custom Fields
On Fri, Jan 27, 2006 at 10:25:00 -0600, Aaron Colflesh [EMAIL PROTECTED] wrote: #2 would seem to be the simplest except I'm really not too keen on the idea of manipulating a table like that on the fly (even though I did proof of concept it and it seems to be simple enough to be fairly safe if adequate checks for entries on table B are put into the system). Does anyone know of a 3rd way of doing it? It seems like this shouldn't be an all that uncommon task, so I'm hoping there is some slick way of maybe putting together a function or view to return data rows with a flexible field layout. So far all the in-db tricks I've come up with have required me to know what the field names were to generate the final query anyway, so they don't really gain me anything. Couldn't you let the user creating a view joining A and B? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Allowing Custom Fields
Bruno Wolff III wrote: On Fri, Jan 27, 2006 at 10:25:00 -0600, Aaron Colflesh [EMAIL PROTECTED] wrote: #2 would seem to be the simplest except I'm really not too keen on the idea of manipulating a table like that on the fly (even though I did proof of concept it and it seems to be simple enough to be fairly safe if adequate checks for entries on table B are put into the system). Does anyone know of a 3rd way of doing it? It seems like this shouldn't be an all that uncommon task, so I'm hoping there is some slick way of maybe putting together a function or view to return data rows with a flexible field layout. So far all the in-db tricks I've come up with have required me to know what the field names were to generate the final query anyway, so they don't really gain me anything. Couldn't you let the user creating a view joining A and B? I have yet to find a way to make a query that will take the individual row values of one table and make them appear to be columns (either by themselves or as part of a join to another table). If someone can tell me how to do that, then yes a view would be ideal. Thanks, AaronC
Re: [GENERAL] Allowing Custom Fields
On Friday 27 January 2006 08:25, Aaron Colflesh wrote: Hello folks, I've run into a challenge that doesn't appear to have been discussed in the archives anywhere. I'm designing a database that users need to have the ability to customize some. They just need the ability to add extra fields to an existing table (oh and they can't touch the predefined fields). So the database schema so far is table A (existing table), table B (contains a list of custom field names and other meta data) and table C (intersection table between A B containing the values for the custom fields for each row). That works really well and all but we have problems with retrieving the data. Due to other requirements related to reporting we need to be able to present the data in table A along with any custom fields in a table as if the custom fields were actually fields on A. I only know of two ways of doing this, and I'm hoping one of you knows of a third way (I've tried to use a function to do it but it just doesn't seem to work). You could have the two tables linked with a key, say table A ( custom_key int ) table B (custom_key int) - and this custom_key references A I'd probably go for a view that is recreated by a trigger on table B. Second alternative would be to just use a join on the tables. I don't know what kind of reporting software you use, but I don't know any that can't do a join on two tables. The worst case scenario would look like SELECT a.*,b.* FROM a JOIN b ON b.custom_key=a.custom_key that will give you one result set. There is a third option. If you know the maximum number of custom columns and possibly their data type, you could add those columns statically, like in table B (custom_key int, cust_field_1 int, cust_field_2 int, ) and then use a third table to label the custom fields, aka table C (cfield1_label varchar(80), cfield2 varchar(80) ) Your application then can grab the label for the field dynamically and the fields in table B wouldn't have to change at all. 1. Build the virtual table outside the database in application code 2. Use triggers on table B to actually create and remove custom fields on A as they are inserted/removed from B. #2 would seem to be the simplest except I'm really not too keen on the idea of manipulating a table like that on the fly (even though I did proof of concept it and it seems to be simple enough to be fairly safe if adequate checks for entries on table B are put into the system). Does anyone know of a 3rd way of doing it? It seems like this shouldn't be an all that uncommon task, so I'm hoping there is some slick way of maybe putting together a function or view to return data rows with a flexible field layout. So far all the in-db tricks I've come up with have required me to know what the field names were to generate the final query anyway, so they don't really gain me anything. Thanks, Aaron C. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Allowing Custom Fields
On Fri, Jan 27, 2006 at 10:40:05 -0600, Aaron Colflesh [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: On Fri, Jan 27, 2006 at 10:25:00 -0600, Aaron Colflesh [EMAIL PROTECTED] wrote: #2 would seem to be the simplest except I'm really not too keen on the idea of manipulating a table like that on the fly (even though I did proof of concept it and it seems to be simple enough to be fairly safe if adequate checks for entries on table B are put into the system). Does anyone know of a 3rd way of doing it? It seems like this shouldn't be an all that uncommon task, so I'm hoping there is some slick way of maybe putting together a function or view to return data rows with a flexible field layout. So far all the in-db tricks I've come up with have required me to know what the field names were to generate the final query anyway, so they don't really gain me anything. Couldn't you let the user creating a view joining A and B? I have yet to find a way to make a query that will take the individual row values of one table and make them appear to be columns (either by themselves or as part of a join to another table). If someone can tell me how to do that, then yes a view would be ideal. I missed that part. There is a way to do this with one of the contrib packages (tablefunc). To use a view though, you would need to know how many columns you were going to get at the time you created the view. So this might not work for you. ---(end of broadcast)--- TIP 6: explain analyze is your friend
