Search Postgresql Archives

Re: Stored procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux