Re: [HACKERS] Get OID of just inserted record - Mailing list pgsql-hackers

hi...

> 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.

both actually, though the former has much greater interest than the latter in
terms of implications... the second example allows a streamlining of tasks (not
having to do a select on a newly inserted piece of data, therefore cutting down
on statements and perhaps even back-end processing)... the first example
though, would only be possible otherwise with several lines of code, and if it
is viewed as an implicit mini-transaction (see below) then it would add
some rather new functionality.
the reason this sparked in my head originaly was that the fellow who posted
the first question was wondering about output (select) from an input function
(insert) .. i started thinking about it ... having statements able to operate
both ways (input/output) simultaneously would be quite nice, especially from a
power user's point of view... intuitively it makes sense (to me anyways =) and
would allow more complex tasks to be handled with less
code/statements/processing

> 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?

i think the task should be refused in entirety so as not to cause unexpected
results. performing insert/select tasks would require more permissions to
the system in general than someone just wanting to do an insert, but that is not
unusual in any way and should be expected... 

further, if any part of the query broke, the entire thing should fail... it
should act as an implicit mini-transaction, consisting of exactly one
statement... so that if a piece of it failed, any and all remaining parts (outer
'loops') of the query are not processed and any previous parts (inner 'loops')
are rolled-back.. and of course an error would come spittering forth. the
implications this holds towards data integrity and conglomerating/atomizing
changes to the dataset are obvious.

as you mentioned, i haven't seen this anywhere else, ... how
much use would it get? well. i know i'd use it if it were available.. i use
triggers/rules, procedures and external code to do what i need now.. so
"sub-inserts" (as you aptly called them) wouldn't really push the bounds of
what is possible, but i think they would push the bounds of what is easily and
dependly possible. 

my 0.02 (and that's canadian.. so..)

-- 
Aaron J. Seigo
Sys Admin