Postgres: use RETURNING value in another query in script-CodePudding

I'm running postgres in a docker container. I use volume mount to load initial data using a SQl file.

I need to run first query to create a row in ORG_ORGANISATION table. I use "RETURNING" keyword of postgres to output the ID of created ORG_ORGANISATION.

When I tried to use the returned value in another insert query, I get the below errors.

2022-07-09 05:30:54.768 UTC [52] ERROR: column "org_id" does not exist at character 91 2022-07-09 05:30:54.768 UTC [52] HINT: There is a column named "org_id" in table "org_entity", but it cannot be referenced from this part of the query. 2022-07-09 05:30:54.768 UTC [52] STATEMENT: INSERT INTO ORG_ENTITY(ORG_ID, PARENT_ENTITY_ID, NAME, DISPLAY_NAME, DESCRIPTION) VALUES (org_id, org_id, "Asia Pacific Regiion", "APAC", "Regiion of APAC"), (org_id, org_id, "Region of Europe, Middle East and Africa", "EMEA", "Regiion of EMEA"),

INSERT INTO ORG_ORGANISATION (NAME, DISPLAY_NAME, DESCRIPTION ) VALUES('TestOrg', 'Green Tech Services', 'An organization committed to measure GHG emissions and reduce GHG emissions')
RETURNING org_id; 
INSERT INTO ORG_ENTITY(ORG_ID, PARENT_ENTITY_ID, NAME, DISPLAY_NAME, DESCRIPTION)
VALUES
(org_id, org_id, "Asia Pacific Regiion", "APAC", "Regiion of APAC"),
(org_id, org_id, "Region of Europe, Middle East and Africa", "EMEA", "Regiion of EMEA"),
(org_id, org_id, "Region of Americas", "AMERICAS", "Regiion of AMERICAS");

Can someone help me to get rid of this error?

Thanks in advance.

CodePudding user response：

Those are two separate statements - why would one statement be able to see into another one? You need to combine this into a single query.

WITH new_row AS (
    INSERT INTO ...
    RETURNING org_id
)
INSERT INTO org_entity (...)
SELECT new_row.org_id, 'blah', 'blah', 'blah' FROM new_row
UNION ALL
SELECT new_row.org_id, 'blah2', 'blah2', 'blah2' FROM new_row
UNION ALL
...
;

You could put the values into another subquery and do a cross-join against the single new id too, but this is probably easier for you to understand.

You may want to read some introductory guides to SQL too to get to grips with scoping and statements and transactions and such.

CodePudding user response：

You need to combine the two INSERTs into a single one using a data modifying common table expression. You also need to use single quotes for string constants. "APAC" is an identifier, e.g. a column name. 'APAC' is a string constant.

with new_org as (
  insert into org_organisation 
    (name, display_name, description ) 
  values
    ('TestOrg', 'Green Tech Services', 'An organization committed to measure GHG emissions and reduce GHG emissions')
  returning org_id
)  
insert into org_entity(org_id, parent_entity_id, name, display_name, description)
select o.org_id, o.org_id, v.name, v.display_name, v.description
from new_org o
cross join ( 
    values
      ('Asia Pacific Regiion', 'APAC', 'Regiion of APAC'),
      ('Region of Europe, Middle East and Africa', 'EMEA', 'Regiion of EMEA'),
      ('Region of Americas', 'AMERICAS', 'Regiion of AMERICAS')
   ) as v(name, display_name, description);

As the insert into org_organisation only returns a single row, the cross join won't generate more rows than specified in the values clause