CODEWITHSUNDEEP
sqlpostgresqlinsert
Abraham P
Abraham P

Reputation: 15481

Reuse inserted id

Say I have a table Contracts:

  id  |  name | document_url  
-----------------------------

Where the documentUrl is automatically generated/uploaded to S3 and has the following structure:

https://s3-bucket.aws.com/${id}.pdf

My question is how I can insert that in a single INSERT query.

INSERT INTO contracts(name, document_url)
VALUES('bob', ${process.env.S3_BUCKET}.aws.com/????.pdf}

I am calling this code from inside a NodeJS server, and the bucket is stored as an environment variable outside the database on a per server basis. Is there some way to get the id of the row currently being inserted, or do I have to insert returning, and then update within a transaction?

Upvotes: 0

Views: 104

Answers (1)

Glenn
Glenn

Reputation: 9170

Perhaps your table can skip trying to store document_url and look more like:

id  |  name | s3_bucket  
-----------------------

Then query with:

SELECT name
      ,'https://' || s3_bucket  || '/' || id || '.pdf' AS document_url
  FROM contracts

Creating a view based on the query will encapsulate the composition so that those querying don't need to know how to paste it together:

CREATE VIEW contract_documents AS
SELECT name
      ,'https://' || s3_bucket  || '/' || id || '.pdf' AS document_url
  FROM contracts

then externally:

SELECT name, document_url
  FROM contract_documents

Upvotes: 2

Related Questions