Thread: Stored procedure

On 12/22/05, Ted Byers <r.ted.byers@rogers.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 ;)

Hi Jaime,

Thanks.

I'd suggest the manual be edited by an educator, since it is a little dense
for someone coming to it for the first time.  Once I read your reply to me,
and reread the manual, I understood.  But on first reading, it is a little
too dense and short on examples.

Regarding serial:
>>  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?

I tried:
ALTER TABLE "People".addy ALTER COLUMN aid TYPE serial

and the error I received is:
ERROR:  type "serial" does not exist

I understand this now, but it seems pgAdmin creates the illusion serial can
be treated like genuine types by including serial along with all the other
types in the drop down list used to set type when creating a new column.

Regarding autoincrement:
>>
>> 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.

In programming in C++, I often pass a value of null or 0 as an argument to a
function; this is done to use the null value as a flag to control the
behaviour of the function at a very fine degree of granularity.  This is a
commonly used and powerful idiom in C++ programming.It is curious, though,
that on thinking about this, I have not used this idiom nearly as much when
I am programming in Java. I can't explain why.

>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')

On reading more about this, my inclination was to do as you did.  However, I
would point out that the sample code I showed here was taken directly from
the MySQL reference manual.  If it matters, I can provide the precise
location in the manual.  I guess some folk disagree with you about how good
or bad it is.  I'll reserve judgement until I have more experience working
with databases.

Assuming I have set up a sequence called 'seq', and set the default value of
id in foo to be nextval('seq'), then the following is getting close to what
I need (there seems to be only one thing left - what do I replace the
question mark with in order to get the id value from the initial select and
pass it to the insert in the first block):

if exists(select id from foo where x = "text") then
    INSERT INTO foo2 (foo_id, foo2_text) VALUES (?,"more_text")
else
    INSERT INTO foo (text) VALUES ('text')
    INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
"more_text")
end if;

The second block of the conditional statement looks like it ought to
properly handle inserting new data into foo, autoincrementing id in foo and
providing the value of id to the insert into foo2.  However, for the first
block, there is no way to know where 'text' is located in the table, so it
is necessary to get the value of id from the SQL statement used as the
argument for exists() and pass it to the insert into foo2 (where the
question mark is located).

Thanks for your time.

Ted

>
> Assuming I have set up a sequence called 'seq', and set the default value of
> id in foo to be nextval('seq'), then the following is getting close to what
> I need (there seems to be only one thing left - what do I replace the
> question mark with in order to get the id value from the initial select and
> pass it to the insert in the first block):
>
> if exists(select id from foo where x = "text") then
>    INSERT INTO foo2 (foo_id, foo2_text) VALUES (?,"more_text")
> else
>    INSERT INTO foo (text) VALUES ('text')
>    INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
> "more_text")
> end if;
>
> The second block of the conditional statement looks like it ought to
> properly handle inserting new data into foo, autoincrementing id in foo and
> providing the value of id to the insert into foo2.  However, for the first
> block, there is no way to know where 'text' is located in the table, so it
> is necessary to get the value of id from the SQL statement used as the
> argument for exists() and pass it to the insert into foo2 (where the
> question mark is located).
>
> Thanks for your time.
>
> Ted
>

maybe you can rewrite this to something else:


in the declare section declare a var

declare
     var1      foo.id%TYPE;

[...and then in the begin section, where all code happens...]

select into var1 id from foo where x = "text";
if var1 is not null then
    INSERT INTO foo2 (foo_id, foo2_text) VALUES (var1,"more_text")
 else
    INSERT INTO foo (text) VALUES ('text')
    INSERT INTO foo2 (foo_id, foo2_text) VALUES (currval('seq'),
 "more_text")
 end if;

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Thu, 2005-12-22 at 12:42 -0500, Jaime Casanova wrote:
> On 12/22/05, Ted Byers <r.ted.byers@rogers.com> wrote:
> >
> > 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');

and then there is the god old DEFAULT value:

  INSERT INTO foo (auto,text) VALUES(DEFAULT,'text');


gnari