2
\$\begingroup\$

I have a function where I am trying to assign book ids to certain students on registration of a new student.

FOR book_id IN
   SELECT a.bookid
   FROM  books a
   WHERE  a.isdelete = false and published = true

LOOP
IF NOT EXISTS (select 1 from studentbooks where  bookid = book_id and  studentid = id) THEN
     INSERT INTO studentbooks(bookid, studentid) values (book_id::uuid, id );
END IF;

END LOOP;

I wrote something like this and it works fine for smaller set of data and under performs when we have more books to loop through. I just wanted a have a review here.

\$\endgroup\$
2
  • \$\begingroup\$ Where does id column come from in your code? \$\endgroup\$ Jan 3, 2020 at 12:51
  • \$\begingroup\$ I am sorry its an input parameter. The id for the new student \$\endgroup\$ Jan 3, 2020 at 13:08

1 Answer 1

2
\$\begingroup\$

Instead of extracting all books and execute a separate sub-query on each FOR loop iteration - we can select only the books that don't have matches in studentbooks table for specified student (presented by id argument) by running PostgreSQL LEFT JOIN with specific WHERE clause.

Furthermore, to make it one-shot action - we can insert data from query using INSERT INTO SELECT statement.

The query for the whole task:

INSERT INTO
   studentbooks(bookid, studentid) 
   SELECT
      b.bookid, your_function_name.id 
   FROM
      books b 
      LEFT JOIN
         studentbooks sb 
         ON sb.bookid = b.bookid AND studentid = id 
   WHERE
      b.isdelete = false AND b.published = true
      AND sb.bookid is NULL;

replace your_function_name with your actual function name to refer the function's argument name.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.