Re: Self-referential records

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
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°