Stored procedure

I am learning how to create stored procedures using pgAdmin and Postgres. I have a couple of questions.

1) For all of my tables (admittedly not many, yet), I created columns with the type of integer to serve as indices. I now learned (well, late last might) in your documentation that Postgres has additional types (serial, serial4, &c.) that are integers that are autoincremented. I tried, therefore, to change these columns to type serial but received an error stating that type serial does not exist. Was the documentation I read mistaken, or is it a problem with pgAdmin, or did I make a mistake? Should I drop the columns in question and recreate them as having type serial (is that possible when the column is used as a foreign key in other tables)?

2) Suppose I have a lookup table with an autoincremented integer index column, used as a foreign key in a second table, and I want a stored procedure to insert data into a second table that uses the index from the first as a foreign key. Now, the stored procedure must:
a) check the name passed for the second column of the first table to see if it exists there, and if not insert it
b) whether the name provided for the second column had to be inserted or not, retrieve the index that corresponds to it
c) execute the insert into the second table using the index value retrieved from the first as the value for the foreign key column in the second table.
Doing all this in Java or C++ is trivial, and I have done so when using a database that didn't have stored procedures, but it isn't clear to me how to do this using only SQL inside a stored procedure.

I have just learned this morning that MySQL would allow the following inside a stored procedure:

INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table

I have yet to figure out how to modify this to verify that 'text' isn't already in foo, and return its index for use in foo2 if it is, but that's another question (I'd want the values in the second column in foo to be unique). But I am curious to know if Postgres has something equivalent to "LAST_INSERT_ID()". Can one embed the first insert above in an if/else block inside a stored procedure, placing the index in a variable that has scope local to the procedure, and use that variable in the second insert?

Thanks,

Ted

R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/