Hello all,
Could someone give me an example as to how I would accomplish something
like this with a function?:
3 tables:
tableA: id (serial), name (varchar), description (varchar), subcat_id
(integer)
tableB: id (serial), subcat_name (varchar), cat_id (integer)
tableC: id (serial), cat_name
I would like to create a function (ie. stored procedure) that I can pass
4 things:
name, description, subcat_name, cat_name
When the procedure runs it would do the following:
1. Check to see if cat_name exists in tableC
a. if so, get the id
b. if not, insert a new record into tableC using the supplied
cat_name, and get the id of the newly created record
2. Check to see if subcat_name exists in tableB where cat_id is the
value returned from step 1
a. if so, get the id
b. if not, insert a new record into tableB using the supplied
subcat_name and the cat_id returned from step 1, and get the id of the
newly created record
3. Insert a record into tableA with the name and description supplied to
the procedure, and the subcat_id returned from step 2
In the end, when my app calls the procedure I'd like it to automatically
create records in tables tableC and tableB if the _name fields don't
already exist, then insert the primary record into tableA using the
foreign key IDs from the other table. I'd like to do it this way
because cat_name is unique in tableC, and (subcat_name, cat_id) are
singularly unique in tableB. (ie. there can be multiple subcat_names as
long as they belong to different categories from tableC).
Any help would be greatly appreciated.
Thanks,
Brandon
