Re: Self referencing composite datatype

On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> On Aug 8, 2013, at 4:11, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
>
> > create table node as (
> > id integer primary key,
> > r integer, s integer,
> > children integer[] element references node
> > );
> >
> > so you could download 9.3rc2 and experimant with it.
> >
> > Now (on <=9.2.x) you can create the table without FK
> >
> > create table node as (
> > id integer primary key,
> > r integer, s integer,
> > children integer[]
> > );
> >
> > and check integrity by triggers.
>
>
> Or, instead of attempting to reference all child nodes from the parent,
> reference the parent node from each child node.
> That's been supported in PG versions like forever and can be queried
> fairly efficiently using recursive CTE's since PG 9.
>

If you do this, have a position number, and use that for ordering. You
need some sort of ordinality here.

Best Wishes,
Chris Travers

>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml