Re: Stored procedure

On 12/22/05, Ted Byers <r(dot)ted(dot)byers(at)rogers(dot)com> 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 ;)