Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7727F9FB5D5 for ; Tue, 24 Jul 2007 11:33:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63081-02-2 for ; Tue, 24 Jul 2007 11:33:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.238]) by postgresql.org (Postfix) with ESMTP id AA5B49F95E3 for ; Tue, 24 Jul 2007 11:33:08 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so1153142wri for ; Tue, 24 Jul 2007 07:33:07 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=R2bPViPxyYWQJjH/vSLpP+qHNfpCvDCg7vr+kENAMpIcy1+t9aBdLZZHfjnU0TuA0I2URSRYz2f8CwWlTAg9NFc7Bg8f+6osmx99rLyUV/AeUcp7jFdY/N0hvFsz8gexgLULnnZTh2s1sjg7WfQy0kBOlXW3jD/3cUtcCMAiZiY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=BFJxLv0w6Qje257v/3dZl+einM9W33GStK1ePRkjP0YzE61u1BhmshAQOaXsiC/qxXu0RsMAFsTzrXcE7ONIOOWkBdNaJKGisXmaDmQxggUpoQHsSnaJyY4kMOmdcYQ5TuyAgW45C5RmbaZhOlpAvS8//IB531RwiSPlAcgG7Ys= Received: by 10.78.130.6 with SMTP id c6mr1084974hud.1185287585842; Tue, 24 Jul 2007 07:33:05 -0700 (PDT) Received: by 10.78.146.6 with HTTP; Tue, 24 Jul 2007 07:33:05 -0700 (PDT) Message-ID: Date: Tue, 24 Jul 2007 15:33:05 +0100 From: "Peter Childs" To: pgsql-sql@postgresql.org Subject: Re: is there a 'table' data type in pg? In-Reply-To: <20070724142328.GA25489@apartia.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_126364_13820260.1185287585760" References: <20070724124246.GA22602@apartia.fr> <87sl7d28pn.fsf@oxford.xeocode.com> <20070724142328.GA25489@apartia.fr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/141 X-Sequence-Number: 28876 ------=_Part_126364_13820260.1185287585760 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 24/07/07, Louis-David Mitterrand wrote: > > On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote: > > "Louis-David Mitterrand" 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. ------=_Part_126364_13820260.1185287585760 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

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.
------=_Part_126364_13820260.1185287585760--