|
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/ |
