Re: Self-referential records - Mailing list pgsql-general

Ovid <curtis_ovid_poe@yahoo.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
thefirst, but I since this is merely a large .sql file that I intend to shove into the PG, I'd much rather declare a
variablein 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
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°