On 12/22/05, Ted Byers <r.ted.byers@xxxxxxxxxx> wrote:
> 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.
columns doesn't serve as indices... columns could be indexed,
instead... there is a difference...
> I now learned (well, late last might) in your documentation that Postgres has
> additional types (serial, serial4, &c.) that are integers that are autoincremented.
serial is not a type is a shorthand for integer with a default
expresion that retrives next value in a sequence...
>I tried, therefore, to change these columns to type serial but
received an error
> stating that type serial does not exist.
how did you try? what was the exact error you receive?
> 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)?
>
no... unless you drop the foreign key constraint as well
> 2) Suppose I have a lookup table with an autoincremented integer index
> column, used as a foreign key in a second table,
indexed column... no index column (there is not such a thing)
> and I want a stored procedure to insert data into a second table that uses the
> index from the first as a foreign key.
i think you are overusing the word "index"
> 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
if exists(select * from table2 where fld = value_from_second_fld_table1) then
...
end if;
> b) whether the name provided for the second column had to be inserted or
> not, retrieve the index that corresponds to it
you don't retrieve indexes...
> 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
and this of course is bad... if a insert NULL i want the NULL to be inserted.
SQL Standard way of doing things is "ommiting the auto incremental fld at all"
INSERT INTO foo (text) VALUES ('text');
> INSERT INTO foo2 (id,text)
> VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
>
INSERT INTO foo2 (id, text) VALUES (currval('seq'), 'text')
> 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()".
currval()
> 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/
you should read the manual in the sections about triggers, sequences,
and so on...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
