Are there any existing trigger functions (preferably C) that could
retrieve a missing value for a compound foreign key on insert or
update? If this overall sounds like a really bad idea, please let me
know as well. This functionality could really speed my project up
though.
For example,
CREATE TABLE email_addresses (id BIGSERIAL, email_address VARCHAR);
ALTER TABLE email_addresses ADD CONSTRAINT _email_addresses_pkey
PRIMARY KEY (id,email_address);
CREATE TABLE users (id BIGSERIAL, email_address_id BIGINT,
email_address VARCHAR);
ALTER TABLE users ADD CONSTRAINT users_fkey_email_address_id FOREIGN
KEY (email_address_id,email_address) REFERENCES
email_addresses(id,email_address) ON UPDATE CASCADE ON DELETE SET
NULL;
CREATE TRIGGER "1-auto_email_address_id" BEFORE INSERT OR UPDATE ON
users FOR EACH ROW EXECUTE PROCEDURE _auto_id('email_address_id');
If I would like to insert a new user AND new email_address I would
assign the email_address_id of NULL or -1.
INSERT INTO users (id, email_address_id, email_address) VALUES
(1,-1,'foo@bar.com') which would do...
SELECT id FROM email_addresses WHERE email_address = 'foo@bar.com'
INTO NEW.email_address_id
If it cannot find a value, it then does...
INSERT INTO email_addresses (email_address) VALUES ('foo@bar.com')
RETURNING id INTO NEW.email_address_id
If I would like to insert a new user and existing email address, I
would assign the email_address_id of 0.
INSERT INTO users (id, email_address_id, email_address) VALUES
(2,0,'foo@bar.com') which would...
SELECT id FROM email_addresses WHERE email_address = 'foo@bar.com'
INTO NEW.email_address_id
If it cannot find a value, it will raise an exception.
If I insert or update users and email_address_id is > 0 then it gets
the natual value by id...
INSERT INTO users (id, email_address_id, email_address) VALUES
(3,2,NULL) which will
SELECT email_address FROM email_addresses WHERE id = 2 INTO
NEW.email_address
And if both email_address_id and email_address are NULL then, both
values just get inserted into users as null.
Declaring the surrogate as -1 (select or insert) or 0 (select) would
save time having to lookup or create the value before inserting into
users. Ive been using a plperl function for this and really like the
results but im wondering if theres a faster way.
The foreign key constraint already scans the email_addresses table for
values so im wondering if theres a way to bootstrap that process or
maybe thats too risky? Any thoughts would be greatly appreciated.