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
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
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.
Cheers,
Ovid--
Buy the book - http://www.oreilly.com/catalog/perlhks/
Tech blog - http://use.perl.org/~Ovid/journal/
Twitter - http://twitter.com/OvidPerl
Official Perl 6 Wiki - http://www.perlfoundation.org/perl6
