Re: Self referencing composite datatype

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.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.