How can I make Postgresql conditionally work on multiple operations?
Considering
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)
)
I want to insert a person and their multiple phone numbers at the same time in order to maintain a relationship of 1 to 1 or more. I looked at this:
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')
But only the first insert will see Newrow.
+3
source to share
1 answer
How about using it 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;
+3
source to share