Re: Stored procedure - Mailing list pgsql-general

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