0

I wanted to know if it is possible to create one WITH statement and add multiple data values to other table with select, or do any equivalent thing.

I have 2 tables

  1. one has data
create table bg_item(
    item_id text primary key default 'a'||nextval('bg_item_seq'),
    sellerid bigint not null references bg_user(userid),
    item_type char(1)not null default 'N', --NORMAL (public)
    item_upload_date date NOT NULL default current_date,
    item_name varchar(30) not null,
    item_desc text not null,
  1. other has images link
create table item_images(
    img_id bigint primary key default nextval('bg_item_image_seq'),
    item_id text not null references bg_item (item_id),
    image_link text not null
);

The user can add item to sell and upload images of it, now these images can be 3 or more, now when i will add the images, and complete item's description and everything from app, my request goes to backend, and i want to perform the query that it adds the user's item, sends me the item's id (which is a sequence in PostgresSQL) and use that id to reference images that i am inserting.

Currently i was doing this (for 1 image):

WITH ins1 AS (
   INSERT INTO bg_item(sellerid,item_type,item_date,item_name,item_desc,item_specs,item_category)
   VALUES (1005, 'k',default,'asdf','asdf','asd','asd')
   RETURNING item_id
   )
   INSERT INTO item_images (item_id, image_link) 
   select item_id,'asdfg.asd.asdf.com' from ins1

(for 3 images)

   INSERT INTO bg_item(sellerid,item_type,item_date,item_name,item_desc,item_specs,item_category)
   VALUES (1005, 'k',default,'asdf','asdf','asd','asd')
   RETURNING item_id
   )
   INSERT INTO item_images (item_id, image_link) 
   select item_id,'asdfg.asd.asdf.com' from ins1
select item_id,'asdfg.asdaws3f.com' from ins1
select item_id,'asdfg.gooolefnsd.sfsjf.com' from ins1

This would work for 3 images.

So my question is how to do it with n number of images? (as user can upload from 1 to n images)

Can i write a for loop? a procedure or function?

References:

With and Insert

Sql multiple insert select

I didn't understand the Edit 3 (if it is related to my answer) in the above one.

One Solution i can think of is to write a procedure to return me item_id and write one more procedure to run multiple inserts, but i want a more efficient solution.

Daniyal Shaikh
  • 419
  • 3
  • 12

1 Answers1

0

If you are going to work with SQL then there is a concept you need to expel from your thoughts -- LOOP. As soon as you think it, it is time to rethink. It does not exist is SQL and is not typically needed. SQL works in sets of qualifying things not individual things.
Now to your issue, it can be done is 1 statement. You pass your image list as an array of text in the with clause, then unnest that array and join to your existing cte during the Insert/Select:

with images (ilist) as 
     ( 
      select array['image1','image2','image3','image4','image5']
     )
   , item (item_id) as
     ( 
       insert into bg_item(sellerid,item_type,item_date,item_name,item_desc,item_specs,item_category)
            values (1005, 'k',default,'asdf','asdf','asd','asd')
         returning item_id
     )
   insert into item_images (item_id, image_link) 
      select item_id,unnest (ilist) 
        from images
        join item on true;
Belayer
  • 13,578
  • 2
  • 11
  • 22