CODEWITHSUNDEEP
sqlpostgresql
Rodrigo
Rodrigo

Reputation: 3278

Multiple inserts PostgreSQL

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?

Upvotes: 4

Views: 327

Answers (4)

user330315
user330315

Reputation:

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;

Upvotes: 3

Schwern
Schwern

Reputation: 164679

Now whenever I insert a link I need to also insert its permissions and photos.

There's several ways you can accomplish this. You can use returning id into link_id to place the link id into the variable link_id and reference that in later statements.

Since this all has to happen in concert, you can write a function to handle it. This ensures it always happens together, you don't have to try and cram it all into one statement, and it makes it easier to use variables. You can also add a transaction and error handling should any part fail.

create function add_link( photo_id uuid, permission_id uuid)
    returns uuid
    as
$func$
declare
    link_id uuid;
begin   
    insert into links(id, hash)
    values (default, 'probably have the function generate this?')
    returning id into link_id;

    insert into link_photo(link_id, photo_id)
    values (link_id, photo_id);

    insert into link_permission(link_id, permission_id)
    values (link_id, permission_id);

    return link_id;
end
$func$ language plpgsql;

test=# select * from add_link(uuid_generate_v4(), uuid_generate_v4());
               add_link               
--------------------------------------
 1c18b24c-c7ac-4169-8449-1c18c9087f8b
(1 row)

Upvotes: 1

Joakim Danielson
Joakim Danielson

Reputation: 51882

Wouldn't your first attempt work (I haven't tried myself) if you replaced "RETURNING *" with "RETURNING id" and selecting that.

See the answer to this question for the full query

Upvotes: 0

Dionei Miodutzki
Dionei Miodutzki

Reputation: 657

Postgresql's "with query" only works with one statetment.

You can do it in "one run" by using an anonymous code block, but it will probably be a better idea to create a proper plpgsql insert function.

Upvotes: 1

Related Questions