i have a couple of tables, all of which have something along the likes of

id: uuid

and

created_at: timestamp

, and the following additions: - the table

urls

has a

host_and_path: string

column containing a normalized url - the table

website_data

has (among other columns) a

url_id: uuid

column pointing to a

urls

entry both were created through the ui, no special sql wizardry going on here when creating a

website_data

entry from my input data (which includes a url that i normalize) i'd like to create a

urls

entry if one doesn't exist, then take its id and use it as the newly created

website_data

entry's

url_id

, all in one go, and possibly for many entries in one call. is that possible today or do i need to resort to an ugly and inefficient multi-step solution (upsert url returning id and normalized address, iterate through input data to match url ids to data objects, create

website_data

entry for each input datum using the info i need and the correct

url_id

)?

Hello @omri! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

I'm not clear on what you are trying to do exactly. BUT if from the client API then it is separate steps for each different table insert (although you can insert an array of rows to a those tables). You are probably better off writing a Postgres function to do everything involved with multiple inserts and/or processing and call with rpc. The other advantage of this, besides one client call, is that it is treated as a transaction and if any operation fails, they are all rolled back.

brilliant, I think I'll try hacking something together in pl/pgsql and report back with the results