Correct usage of Postgres JSON conversion functions

Question

I am working on a Postgres update transaction.

Let's say I have two tables: events and ticket_books with event booking types. The ticket_books table has a foreign key pointing to the events.

I need to update an event stored in the database, including booking type records from the ticket_books table.

To deal with cascading update and delete, I decided to build a transaction, in a "pseudo-code" it looks like:

    BEGIN;
      DELETE FROM
        ticket_books
      WHERE
        event_id = ${req.params.id} AND
        id NOT IN (${bookingIds})
      FOR booking IN json_to_recordset('${JSON.stringify(book)}') as book(id int, title varchar(200), price int, ...) LOOP
        IF bookind.id THEN
          UPDATE
            ticket_books
          SET
            title = booking.title, price = booking.price
          WHERE
            event_id = ${req.params.id};
        ELSE
          INSERT INTO
            ticket_books (title, price, qty_available, qty_per_sale)
          VALUES
            (booking.title, booking.price, booking.qty_available, booking.qty_per_sale)
          RETURNING
            id
        END IF;
      END LOOP;
   UPDATE
     event
   SET
    ...
   WHERE
     id = ...
   RETURNING
     id;
   COMMIT;

I currently get the error: syntax error at or near "json_to_recordset". I never used json_to_recordset or friends before, just saw from the document that from 9.3 and later those are available. Unsure how to get Postgres to understand what I need, though.

I am embedding a JSON array so the final line looks like:

FOR booking IN json_to_record('[{"id":13,"description":"Three day access to the festival","title":"Three Day General Admission","price":260,"qty_available":5000,"qty_per_sale":10},{"id":14,"description":"Single day access to the festival","title":"Single Day General Admission","price":"90.90","qty_available":2000,"qty_per_sale":2},{"title":"Free Admission","price":"0.00","qty_available":0,"qty_per_sale":0}]')

I believe that my JSON array is valid. Apparently, this is not how I should be passing it to the Postgres. What should I be doing instead? My goal is to iterate over the array entries. If there is an integer value for booking.id, I want to update the record, else insert a new one.

jjanes · Accepted Answer · 2020-03-22 23:26:07Z

1

You need a query, and a standalone function call usually does not count as a query:

FOR booking IN select * from json_to_recordset(...

Also, you can't use BEGIN to start a transaction in plpgsql. It is only used to start a block. If you are using a procedure rather than a function, then you can COMMIT but then a new transaction starts immediately with no BEGIN token being used.

You are also missing a semicolon between the DELETE and the FOR, but from the error message that seems to be missing from only your post, and not from your actual code.

answered Mar 22, 2020 at 23:26

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correct usage of Postgres JSON conversion functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related