Subject Insert into Select
Author
Post date 2019-12-01T03:56:42Z
I know I can achieve this functionality via a stored procedure - but can this be done with "pure" SQL? Given the following basic tables:

create domain D_ID as integer not null;
create domain D_NAME as varchar(50) not null;

create table PARENTS (
  PARENT_ID D_ID not null,
  PARENT_NAME D_NAME not null);
alter table PARENTS add constraint PK_PARENTS primary  key (PARENT_ID);
alter table PARENTS add constraint UNQ_PARENT_NAME (PARENT_NAME) using index IDX_PARENT_NAME;

create table CHILDREN (
  CHILD_ID D_ID not null,
  PARENT_ID D_ID not null,
  CHILD_NAME D_NAME not null);
alter table CHILDREN add constraint PK_CHILDREN primary  key (CHILD_ID);
alter table CHILDREN add constraint UNQ_CHILD_NAME (CHILD_NAME) using index IDX_CHILD_NAME;
alter table CHILDREN add constraint FK_CHILDREN_PARENT_ID foreign key (PARENT_ID) references PARENTS(PARENT_ID) on delete cascade on update cascade using index IDX_CHILDREN_PARENT_ID;

and there are before insert triggers with generators for the primary keys.

The following should be a valid select:
select CHILD_ID, PARENT_NAME, CHILD_NAME from CHILDREN natural join PARENTS;

Now - given all the above, is there a way of performing:
update or insert into (select CHILD_ID, PARENT_NAME, CHILD_NAME from CHILDREN natural join PARENTS) values ( 0, 'parent name', 'child name') matching (PARENT_NAME, CHILD_NAME) returning (CHILD_ID);

In the example above the before insert triggers assign a generator value for ID's less than 1.

As I said - I can do this via stored procedures - but is there a way to do it without?
--
Daniel