sql - 如何让带有子句的 Postgresql 处理多个语句？

Question

给定

Create table Person(
    PersonID int Primary Key default nextval('My_seq'),
    PersonName text
)
create table PhoneNumber(
    PhoneNumberID int primary Key default nextval('My_seq'),
    PersonID int,
    PhoneNumber text,
    Label text, 
    CONSTRAINT fk_PersonID
       FOREIGN KEY(PersonID) REFERENCES Person(PersonID)
)

我想一次插入一个人和他们的多个电话号码以保持一对一或更多的关系。我在看这个:

With newRow as (
 insert into person (PersonName) values('Mr. Black') returning PersonID
)
insert into PhoneNumber (PersonID, PhoneNumber, Label) 
  Values((Select PersonID from newRow),'555-1234','Home')
insert into PhoneNumber (PersonID, PhoneNumber, Label) 
  Values((Select PersonID from newRow),'454-1234','Office')
insert into PhoneNumber (PersonID, PhoneNumber, Label) 
  Values((Select PersonID from newRow),'333-1234','Cell')

但只有第一个 insert 会看到 Newrow。

Answer 1

使用 union all 怎么样？

With newRow as (
     insert into person (PersonName) values('Mr. Black') returning PersonID
)
insert into PhoneNumber (PersonID, PhoneNumber, Label) 
    select PersonId, '555-1234', 'Home' from newRow union all
    select PersonID, '454-1234', 'Office' from newRow union all
    select PersonID, '333-1234', 'Cell' from newRow;