如何在多个语句中使用带子句的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)
)

我希望能一次性插入一个人和他们的多个电话号码，以维护1对1或1对多的关系。我正在查看以下内容：

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')

但只有第一个插入操作会看到Newrow。

- Ron H

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;

- Gordon Linoff