Using postgreSQL 9.1.9, I have some SQL requests like this:
INSERT INTO "my_table" VALUES(10,'James','California');
Below a simplified table of the original names lookup table...
names
name_id name
--- -----
3 James
but in fact I don't have to enter (into "my_table") the text value provided by the SQL request (name of person, name of the state) but its corresponding ID located in another table (ex: names table)
So i was thinking on creating a trigger, calling a function that should execute the select SQL request and return the modified row to be inserted.
Is that possible?
CREATE FUNCTION insert_by_ID() RETURNS TRIGGER AS '
BEGIN
--Can we execute this query, store the result in a variable to be added
to the NEW record?
SELECT name_id from names where name=NEW.name;
--this request would be...
--SELECT name_id from names where name='James'
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER insert_info_ID
BEFORE INSERT ON my_table
FOR EACH ROW
EXECUTE PROCEDURE insert_by_ID();