pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedis there a 'table' data type in pg?
4+ messages / 3 participants
[nested] [flat]
* is there a 'table' data type in pg?
@ 2007-07-24 12:42 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-07-24 14:10 ` Re: is there a 'table' data type in pg? Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 4+ messages in thread
From: Louis-David Mitterrand @ 2007-07-24 12:42 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is there a 'table' data type in pg?
2007-07-24 12:42 is there a 'table' data type in pg? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-07-24 14:10 ` Gregory Stark <stark@enterprisedb.com>
2007-07-24 14:23 ` Re: is there a 'table' data type in pg? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory Stark @ 2007-07-24 14:10 UTC (permalink / raw)
To: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is there a 'table' data type in pg?
2007-07-24 12:42 is there a 'table' data type in pg? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-07-24 14:10 ` Re: is there a 'table' data type in pg? Gregory Stark <stark@enterprisedb.com>
@ 2007-07-24 14:23 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-07-24 14:33 ` Re: is there a 'table' data type in pg? Peter Childs <peterachilds@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Louis-David Mitterrand @ 2007-07-24 14:23 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is there a 'table' data type in pg?
2007-07-24 12:42 is there a 'table' data type in pg? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-07-24 14:10 ` Re: is there a 'table' data type in pg? Gregory Stark <stark@enterprisedb.com>
2007-07-24 14:23 ` Re: is there a 'table' data type in pg? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-07-24 14:33 ` Peter Childs <peterachilds@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Childs @ 2007-07-24 14:33 UTC (permalink / raw)
To: pgsql-sql
On 24/07/07, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
wrote:
>
> 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,
>
> ---------------------------(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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-07-24 14:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-24 12:42 is there a 'table' data type in pg? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-07-24 14:10 ` Gregory Stark <stark@enterprisedb.com>
2007-07-24 14:23 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-07-24 14:33 ` Peter Childs <peterachilds@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox