Multiple inserts PostgreSQL

Question

I'm trying to perform multiple inserts from 1 sql query. To break down what I'm trying to do here is the DB structure:

links:
  - id // uuid_generate_v4()
  - hash

permissions:
  - id // uuid_generate_v4()
  - name

photos:
  - id // uuid_generate_v4()
  - url

link_permissions:
  - link_id
  - permission_id

link_photo:
  - link_id
  - photo_id

Now whenever I insert a link I need to also insert its permissions and photos. This is the sql queries I've attempted so far.

WITH link as (
    INSERT INTO links(hash) VALUES ('my-random-hash') 
    RETURNING *
) 
INSERT INTO link_photo(link_id, photo_id) 
VALUES ((select link.id from link), '095ccacf-ebc1-4991-8ab0-cac13dac02b7'), 
INSERT INTO link_permission(link_id, permission_id) 
VALUES ((select link.id from link), '506f3302-fe9f-4982-8439-d6781f646d01')

and

WITH link as (
  INSERT INTO links(hash) VALUES ('my-random-hash') 
  RETURNING *
) 
(INSERT INTO link_photo(link_id, photo_id) 
  VALUES ((select link.id from link), '095ccacf-ebc1-4991-8ab0-cac13dac02b7')), 
 INSERT INTO link_permission(link_id, permission_id) 
 VALUES ((select link.id from link), '506f3302-fe9f-4982-8439-d6781f646d01')

How would I write this query?

Answer 1

Just put the the second insert into another CTE:

WITH link as (
    INSERT INTO links(hash) VALUES ('my-random-hash') 
    RETURNING *
) , lp as (
  INSERT INTO link_photo(link_id, photo_id) 
  VALUES ((select link.id from link), '095ccacf-ebc1-4991-8ab0-cac13dac02b7')
)
INSERT INTO link_permission(link_id, permission_id) 
VALUES ((select link.id from link), '506f3302-fe9f-4982-8439-d6781f646d01');

Alternatively, don't use values, that makes the query a bit easier to read (I think)

WITH link as (
    INSERT INTO links(hash) VALUES ('my-random-hash') 
    RETURNING *
) , lp as (
  INSERT INTO link_photo(link_id, photo_id) 
  select id, '095ccacf-ebc1-4991-8ab0-cac13dac02b7'
  from link
)
INSERT INTO link_permission(link_id, permission_id) 
select id, '506f3302-fe9f-4982-8439-d6781f646d01'
from link;