[SQL] is there a 'table' data type in pg?
Hello, In my forum app a new post can be related to several types of objects: person, location, event, simple text subject, etc. so in my 'forum' table I plan to add an id_subject column which can contain a reference to any number of different tables (location, person, etc.). What I need to know is to _what_ table the id_subject belongs. Can I use a another column to store the type of the id_subject (ie: the tabled it belongs to) ? Then I would be able to query that table for additional info to print alongside the forum posts. Thanks for your insights, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] is there a 'table' data type in pg?
On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: Can I use a another column to store the type of the id_subject (ie: the tabled it belongs to) ? Then I would be able to query that table for additional info to print alongside the forum posts. There are ways to identifier tables in Postgres but there's no way to run a query against a table using them. Bummer, I suspected as much. I would strongly recommend you define your own list of object_types, probably even have an object_type table with a primary key, a description column, and a table_name column. Then you can in your application construct the appropriate query depending on the object_type. Good fallback solution. One alternative you could do is have a set-returning plpgsql function which has a big if statement and performs the right kind of query. I think the records would have to all be the same -- they can't be different kinds of records depending on the type of object. Will look at that one, always willing to dig deeper into pg's more complex ways :) Thanks for your help, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] is there a 'table' data type in pg?
On 24/07/07, Louis-David Mitterrand [EMAIL PROTECTED] wrote: On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: Can I use a another column to store the type of the id_subject (ie: the tabled it belongs to) ? Then I would be able to query that table for additional info to print alongside the forum posts. There are ways to identifier tables in Postgres but there's no way to run a query against a table using them. Bummer, I suspected as much. I would strongly recommend you define your own list of object_types, probably even have an object_type table with a primary key, a description column, and a table_name column. Then you can in your application construct the appropriate query depending on the object_type. Good fallback solution. One alternative you could do is have a set-returning plpgsql function which has a big if statement and performs the right kind of query. I think the records would have to all be the same -- they can't be different kinds of records depending on the type of object. Will look at that one, always willing to dig deeper into pg's more complex ways :) Thanks for your help, ---(end of broadcast)--- TIP 6: explain analyze is your friend The words table partitioning spring to mind. you could build a view of all the sub tables and then select by tablename='whatever' You may also want to look into inheritance Only some ideas Peter.
