Problems trying to use multiple CTEs in one SQL statement-CodePudding

I'm not quite sure how to form this in the right way. I need to insert multiple companies. Each CTE here inserts records into a few company tables.

Initially I was running just one CTE that looked like this and worked fine:

WITH newCompany AS 
(
    INSERT INTO company (company_id, company_name)
    VALUES (1, 'CompanyA')
    RETURNING company_id, company_name
),
newProfile AS 
(
    INSERT INTO companyProfile (company_id, profile_header_image_url)
        SELECT company_id, 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg' 
        FROM newCompany
    RETURNING profile_id, profile_header_image_url
),
newCompanyProfile AS 
(
    INSERT INTO company_profile (company_id, profile_id)
        SELECT c.company_id, p.profile_id 
        FROM newCompany c, newProfile p
)
SELECT
    c.company_id,
    c.company_name,
    p.profile_id,
    p.profile_header_image_url
FROM 
    newCompany c, newProfile p

But now I'm trying to add multiple CTEs (one for each company). so I tried to add RECURSIVE and tried to surround each CTE with () and add a comma between CTEs, but I'm just not there yet:

--CTE #1
WITH RECURSIVE newCompany AS ((
    insert into company (company_id,company_name)
    values(1, 'CompanyA')
    returning company_id, company_name
),
newProfile AS (
    insert into companyProfile (company_id, profile_header_image_url)
    SELECT company_id, 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg' from newCompany
    returning profile_id, profile_header_image_url
),
newCompanyProfile AS (
    insert into company_profile (company_id, profile_id)
    SELECT c.company_id, p.profile_id from newCompany c, newProfile p
)
SELECT
    c.company_id,
    c.company_name,
    p.profile_id,
    p.profile_header_image_url
FROM newCompany c, newProfile p),

--CTE #2
newCompany AS ((
    insert into company (company_id,company_name)
    values(2, 'CompanyB')
    returning company_id, company_name
),
newProfile AS (
    insert into companyProfile (company_id, profile_header_image_url)
    SELECT company_id, 'https://storage.googleapis.com/xxxx/assets/xxxx/header.jpg' from newCompany
    returning profile_id, profile_header_image_url
),
newCompanyProfile AS (
    insert into company_profile (company_id, profile_id)
    SELECT c.company_id, p.profile_id from newCompany c, newProfile p
)
SELECT
    c.company_id,
    c.company_name,
    p.profile_id,
    p.profile_header_image_url
FROM newCompany c, newProfile p)

Error: query error error: syntax error at or near "insert"

CodePudding user response：

Don't shoot the messenger. Here's an example of inserting company, profile, and company_profile (for more than one company) all in one expression with CTE terms.

There doesn't seem to be a reason for the mapping table company_profile if the relationship is one to one. I left that in anyway.

The test case: