Обсуждение: 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,

"Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> 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.

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. 

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.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote:
> "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> 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,

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.