Re: Thoughts on a surrogate key lookup function? - Mailing list pgsql-general

On Sat, Nov 6, 2010 at 1:01 PM, Nick <nboutelier@gmail.com> wrote:
> Merlin, thanks for the reply. Yes, using email_addresses was a very
> silly example. Maybe the following is a better example...
>
> CREATE TABLE first_names (id INT, first_name VARCHAR);
> ALTER TABLE first_names ADD CONSTRAINT first_names_pkey PRIMARY KEY
> (id,first_name);
> ALTER TABLE first_names ADD CONSTRAINT first_names_unique_first_name
> UNIQUE (first_name);
>
> CREATE TABLE last_names (id INT, first_name VARCHAR);
> ALTER TABLE last_names ADD CONSTRAINT last_names_pkey PRIMARY KEY
> (id,last_name);
> ALTER TABLE last_names ADD CONSTRAINT last_names_unique_last_name
> UNIQUE (last_name);
>
> CREATE TABLE referrals (id INT, ref_code VARCHAR);
> ALTER TABLE referrals ADD CONSTRAINT referrals_pkey PRIMARY KEY
> (id,ref_code);
> ALTER TABLE referrals ADD CONSTRAINT referrals_unique_ref_code UNIQUE
> (ref_code);
>
> CREATE TABLE users (id INT, first_name_id INT, first_name VARCHAR,
> last_name_id INT, last_name VARCHAR, ref_code_id INT, ref_code DATE);
> ALTER TABLE users ADD CONSTRAINT users_fkey_first_names FOREIGN KEY
> (first_name_id,first_name) REFERENCES first_names(id,first_name) ON
> UPDATE CASCADE ON DELETE SET NULL;
> ALTER TABLE users ADD CONSTRAINT users_fkey_last_names FOREIGN KEY
> (last_name_id,last_name) REFERENCES last_names(id,last_name) ON UPDATE
> CASCADE ON DELETE SET NULL;
> ALTER TABLE users ADD CONSTRAINT users_fkey_referrals FOREIGN KEY
> (ref_code_id,ref_code) REFERENCES referrals(id,ref_code) ON UPDATE
> CASCADE ON DELETE SET NULL;
> CREATE TRIGGER "auto_first_name_id" BEFORE INSERT OR UPDATE ON users
> FOR EACH ROW EXECUTE PROCEDURE _auto_id('first_name_id');
> CREATE TRIGGER "auto_last_name_id" BEFORE INSERT OR UPDATE ON users
> FOR EACH ROW EXECUTE PROCEDURE _auto_id('last_name_id');
> CREATE TRIGGER "auto_ref_code_id" BEFORE INSERT OR UPDATE ON users FOR
> EACH ROW EXECUTE PROCEDURE _auto_id('ref_code_id');
>
> If I would like to insert a new user, first name, last name I would
> give the surrogates a value of NULL or -1. Their referral code must
> exist so ill give that surrogate a value of 0.
> INSERT INTO users (id, first_name_id, first_name, last_name_id,
> last_name, ref_code_id, ref_code) VALUES (1,-1,'John',-1,'Doe',
> 0,'xyz') which would...
>
> SELECT id FROM first_names WHERE first_name = 'John' INTO
> NEW.first_name_id
> IF NOT FOUND INSERT INTO first_names (first_name) VALUES ('John')
> RETURNING id INTO NEW.first_name_id
>
> SELECT id FROM last_names WHERE last_name = 'Doe' INTO
> NEW.last_name_id
> IF NOT FOUND INSERT INTO last_names (last_name) VALUES ('Doe')
> RETURNING id INTO NEW.last_name_id
>
> SELECT id FROM referral_codes WHERE ref_code = 'xyz' INTO
> NEW.ref_code_id
> IF NOT FOUND raise exception
>
> If I want to insert the new user John Smith, and I already know the
> surrogate value for John and I dont want to add a ref_code then I can
> do...
> INSERT INTO users (id, first_name_id, first_name, last_name_id,
> last_name, ref_code_id, ref_code) VALUES
> (2,1,NULL,-1,'Smith',NULL,NULL) which would...
>
> SELECT first_name FROM first_names WHERE id = 1 INTO NEW.first_name
> IF NOT FOUND raise exception
>
> SELECT id FROM last_names WHERE last_name = 'Smith' INTO
> NEW.last_name_id
> IF NOT FOUND INSERT INTO last_names (last_name) VALUES ('Smith')
> RETURNING id INTO NEW.last_name_id
>
> So by adding both the surrogate and natural keys to users table and
> toggling the surrogate on insert by 0 (must exist) or -1 (select or
> insert) I can bypass a much more complex insert statement. Is this
> frowned upon? I havent had many issues (but some ive been able to work
> around) with this as a plperl trigger and am pleased with how much
> easier it makes my inserts (besides the execution speed).

It's a neat idea, but all things considered, you are better off using
one of the two approaches I outlined above:
*) Your idea need extra composite index on two fields (each unique)
that serves no integrity purpose
*) Referring table has extra fields, pick natural or surrogate, but not both...
*) It's not faster.  Any way you slice this, you need lookup on the
master table, even if the system does it internally through RI in the
purely natural case. insert where not exists...will do exactly what
you are doing above, and does it in one statement, not two.

If you want to do this inside database, it's more common to do this in
regular function, not trigger function.  Just make a function
insert_user() that handles logic checking and dispense with all the
extra fields...

merlin