Re: Self-referential records

On Sun, Jan 24, 2010 at 10:36 PM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> wrote:
> Ovid <curtis_ovid_poe(at)yahoo(dot)com> wrote:
>
>> Assuming I have the following table:
>>
>>     CREATE TABLE refers (
>>       id        SERIAL  PRIMARY KEY,
>>       name      VARCHAR(255) NOT NULL,
>>       parent_id INTEGER NOT NULL,
>>       FOREIGN KEY (parent_id) REFERENCES refers(id)
>>   );
>> I need to insert two records so that "select * from refers" looks like this:
>>
>>     =# select * from refers;
>>      id | name | parent_id
>>     ----+------+-----------
>>       1 | xxxx |         1
>>       2 | yyy  |         2
>
> I think you mean id=1, parent_id=2 and id=2, parent_id=1, or?
>
>>
>> The first record can't be inserted because I don't yet know the parent_id. The second record can be inserted after the first, but I since this is merely a large .sql file that I intend to shove into the PG, I'd much rather declare a variable in the script to get this done.  I'm thinking something like the following pseudo-code:
>>
>>     INSERT INTO refers (name, parent_id) VALUES ('xxxx', :id);
>>     SELECT id INTO :parent_id FROM refers WHERE name='xxxx';
>>     INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
>>
>> Obviously the above is gibberish, but hopefully it makes clear what I'm trying to do :)
>>
>> Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies associated with NULL values.
>
> 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?

> test=*# insert into refers (name, parent_id) select 'yyy', id from refers where name = 'xxx';
> INSERT 0 1
> Zeit: 0,436 ms
> test=*# update refers set parent_id = (select id from refers where name = 'yyy') where name = 'xxx';
> UPDATE 1
> Zeit: 0,431 ms
> test=*# select * from refers;
>  id | name | parent_id
> ----+------+-----------
>  2 | yyy  |         1
>  1 | xxx  |         2
> (2 Zeilen)
>
>
> The next release 9.0 contains (i hope) writes CTE, with this featue you can do:
>
> 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
> Time: 3,753 ms
> test=*#
>
> with
>  t1 as (select nextval('refers_id_seq') as id),
>  t2 as (insert into refers (id, name, parent_id) select nextval('refers_id_seq'), 'yyy', t1.id from t1 returning *),
>  t3 as (insert into refers (id, name, parent_id) select t1.id, 'xxx', t2.id from t1, t2)
> select true;
>  bool
> ------
>  t
> (1 row)
>
> Time: 0,853 ms
> test=*# select * from refers;
>  id | name | parent_id
> ----+------+-----------
>  2 | yyy  |         1
>  1 | xxx  |         2
> (2 rows)
>
>
> That's (the two insert's) are now one single statement ;-)
>
>
> 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°
>
> --
> 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
>

to Ovid,

if you are going to create a tree in the table, you should certainly
allow the parent_id be null, otherwise, what would be the parent of
root? if you are meant to create a circle...god, what is that for? may
i have your story?

--
Best Regards,
David Shen

http://twitter.com/davidshen84/
http://meme.yahoo.com/davidshen84/