From my possibly naive point of view, starting from thinking about relational algebra, it seems to me that
insert into <table> (<col list>)
values (<val list>)
returning <col list>
into <var list>

is a special case of

insert into <table> (<col list>)
<table expression1>
returning <table expression2>


where <table expression2> is constrained to be a subset of the columns, but all the rows of <table expression1>, with possible column renaming. It appears to me that to make sense of the result set one needs to require the columns in <table expression2> to include the primary key of <table>.

Possibly a comprehensible syntax would be
insert into <table> (<col inst>)
<table expression>
returning (<renamed_col_list>)

<renamed_col_list> being a comma delimited list whose elements are either column names or of the form
<col name> as <alias>, just like in the select clause of a select statement.

I am still unclear on exactly how result sets (rather than an individual row) get back to the requesting program, but it looks to me as if the same mechanism would work here.

Generally speaking, to what extent are you considering extending standard SQL? I personally but perhaps unrealistically might be in favor of constructing a syntactically reasonable relational language as an option to use instead of SQL.

Thanks

David Jencks

d_jencks@...

----- Original Message -----
From: Ann Harrison
To: IB-Architect@egroups.com
Sent: Monday, July 17, 2000 4:25 PM
Subject: [IB-Architect] Insert Returning


I've been going over my list of little features that
might make people's lives better and ran across this.

Internally, InterBase has a blr verb called blr_store2.
Its arguments are a list of assignments between a
message fields and columns, and a list of assignments
between columns and message fields. In short, it
returns a list of values.

Something like:

insert into <table> (<col list>)
values (<val list>)
returning <col list>
into <var list>

Useful for values set by triggers and generators.

In GDML this worked nicely, because a STORE (insert)
never created more than one row. Where SQL would
say
insert into <table> (<col list>)
select <col list> from <selection expression>

GDML would say

for <selection expression>
store <ctx> in <table>
begin
<assignment list>
end;
end_for;

In the GDML case, returning one set of values for each
insertion makes sense, just like returning values from
nested loops.

The SQL mass operation is another kettle of fish - and
one of the reasons I particularly like languages that
use one syntax for two semantically different operations,
but lets not get into that.

Any thoughts?

Ann





------------------------------------------------------------------------------



------------------------------------------------------------------------------
To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com





[Non-text portions of this message have been removed]