On Mon, 27 Jan 2003, Antti Haapala wrote:
> > I don't see why you need a unqiue identifier per row, nor do I see why,
> > if you are going to have one, it needs to be the same type across all
> > tables.
(Note here: it may not have been quite clear, but I'm not asking for
specific instances of where you might want to do this; I'm asking why it
should be forced upon every single table in the world, unless people a)
know that postgresql does this, and b) use special SQL extensions that
are not compatable with any other DMBS in the world.)
> If i had table with multi col primary key like...
>
> create table devices (
> major int4,
> minor int4,
> primary key (major, minor)
> );
>
> ... and do this:
>
> insert into devices (major, minor values (224, find_free_minor_for(224))
>
> should the database report something like
>
> INSERT '{<([\'224\', \'89\'])>}' 1
>
> which I could then parse in my client program and try to recover my
> fresh brand new primary key from it? No thanks...
It's up to you. It sounds like in this particular application, you want a
single integer as the primary key. So I have no objection to you changing
the table to be
create table devices (id serial PRIMARY KEY,major int4,minor int4,CONSTRAINT major_minor_unique UNIQUE (major,
minor) );
and then selecting currval('devices_id_seq') in order to find out what the
id of that record is.
But my first question here is, why do you want to do this with what is
effectively a hidden column, rather than explicitly showing that you need
this, as above? And why do you want to run the risk of OID wraparound when
you don't have to?
Next, other applications might not need to parse whatever the database
reports, or may know in advance what they've inserted. So why do you want
to, by default, impose the overhead of this special hidden column on these
other applications?
> Anyways, I've got an idea: what about having option that INSERTs return
> "oid_status" in form...
I don't understand exactly how an INSERT statement "returns" anything.
An INSERT statement is not a function, is it?
However, I have no objection to adding a function or other method to get
the primary key of the most recent insertion, assuming it exists, for
those folks with multi-column primary keys. Presumably it would generate
a result set just like a regular SELECT....
cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're
alllight. --XTC