Re: Self-referential records

Xi Shen <davidshen84(at)googlemail(dot)com> wrote:

> > To handle that you can set the constzraint deferrable, initially
> > deferred:
> >
> > test=# CREATE TABLE refers ( id SERIAL  PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) deferrable initially deferred);
> > NOTICE:  CREATE TABLE will create implicit sequence "refers_id_seq" for serial column "refers.id"
> > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" for table "refers"
> > CREATE TABLE
> > Zeit: 25,599 ms
> > test=*# insert into refers (name, parent_id) values ('xxx',0);
> > INSERT 0 1
> > Zeit: 0,662 ms
>
> to Andreas,
>
> this 'deferrable' thing is really cool. but i have a question. at this
> point, where i insert this text, if i select the refers table, what
> would the parent_id looks like?

0, as i inserted:

test=*# insert into refers (name, parent_id) values ('xxx',0);
INSERT 0 1
Zeit: 0,636 ms
test=*# select * from refers;
id | name | parent_id
----+------+-----------
1 | xxx | 0
(1 Zeile)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°