Thread: RE: [HACKERS] Get OID of just inserted record

Well, with autocommit on, the statement would fail, and I would expect the
insert to then roll back, if the select part failed.  No problem, really.

MikeA

-----Original Message-----
From: D'Arcy" "J.M." Cain
To: aaron@gtv.ca
Cc: peter_e@gmx.net; e99re41@DoCS.UU.SE; pgsql-hackers@postgreSQL.org
Sent: 11/3/99 5:04 AM
Subject: Re: [HACKERS] Get OID of just inserted record

Thus spake Aaron J. Seigo
> > => insert into foo values (4, 'aaa');
> > INSERT 7998067 1
> 
> this assumes that one is using libpq.. it would be nice to have access
to this
> from psql or anywhere for that matter.. and not just oids.. but, say
for
> instance, default values in tables that are generated dynamically...
etc

Just to see if I understand you, is this what you want to be able to do?

UPDATE t1 SET other_oid =   (INSERT INTO t2 VALUES (1, 'aaa') RETURN OID)   WHERE someting = 'something';

or

SELECT (INSERT INTO t2 (f1, f2) VALUES (1, 'aaa') RETURN f3);

In other words, sub-inserts.  It is kind of a neat idea.  I don't know
that it is worth spending much time on but it would be a neat feature
that no one else has.

Just wondering, how would you handle insert only tables?  That is, you
have insert privleges but not select.  Would you still return the field
or fields requested surprising the database designer, accept the insert
but return an error or refuse the insert entirely since the task could
not be completed?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

************

Thus spake Ansley, Michael
>Well, with autocommit on, the statement would fail, and I would expect the
>insert to then roll back, if the select part failed.  No problem, really.

Well, autocommit would only matter if it was decided that it wasn't an
atomic transaction.  If, as seems both sensible and consensed (look, I
made up another word :-) the transaction should be atomic, then the
state of autocommit shouldn't matter.

However, it almost begs the question of whether there should be another
permission that could be granted.  We may want to allow someone to see
the value of just inserted data after adjustments but not on the table
in general.  This statement would give us that as well if we added a
new perm.

GRANT INSERT, SELECT_ON_INSERT ...

or

GRANT INSERT, RSELECT... -- for Restricted SELECT.  ISELECT perhaps?

So someone can get the serial number of an entry that they just inserted
but they wouldn't be able to look at the table in general.  That's a
feature that I could have used in a database I have.  Instead I had to
give SELECT perms to a user on a table that I would have preferred to
otherwise keep hidden.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Dear Sir.

I apolize for bothering you if this mail bothers you.
Would you mind if I ask you to introduce to me any excellent network hacker around you if  you happen to have any
informationabout this kind of matter?
 
Otherwise, may I ask you are willing to introduce your acquaintances who seems to well be informed about Institution
ComputerSystem Accessing?  It is not harmful to anybody at all.  
 
But I will pay for 20,000 dollars for his job.  I say again it is not harmful at all. I just want to confirm why
certainrecord missed.
 
I can make a business trip and meet him for details later. 

Sincerely,

Timothy