https://supabase.com/ logo
Join DiscordCommunities
Powered by
Using postgres and plpgsql, I'm trying to create a...
# sql
l
Using postgres and plpgsql, I'm trying to create a function with takes in a parameter which is an array of objects.
Copy code
sql
CREATE FUNCTION test_function(products_object_array_input some_data_type) -- what should the data type be? array? jsonb[]?
An array of objects that I would pass into the function could look like this:
Copy code
json
[
  { "productID": 3, "quantity": 5 },
  { "productID": 1, "quantity": 2 }
]
First question: What should I set the products_object_array_input data type as? array? jsonb[]? Second question: I then want to loop over the array to insert the values of the object array. Again, using plpgsql. How?
Copy code
sql
-- I want to loop over cart_array_input and do this for every object in the array. Sorry for pseudo-code. i = iterator
insert into order_item(product_id, quantity)
values(products_object_array_input[i].productID, products_object_array_input[i].quantity);
s
If you're ever passing JSON into, or returning JSON from, a PG function, the data type would be JSON or JSONB. 
JSONB[]
would be different, and would look like this:
Copy code
{
  [
    { "productID": 3, "quantity": 5 },
    { "productID": 1, "quantity": 2 }
  ]
}
As you can see, not valid JSON but a valid PG array of JSON / JSONB (since 
{
and 
}
are the array markers in PG unless you tell PG that it's all JSON. JSONB would be my recommendation. For all intents and purposes, it appears as normal JSON to us, but it's possible for PG to optimise how it's stored, indexed and accessed, and some JSON operators only work on JSONB specifically. For working with the actual contents of the JSON, you would need to use the JSON-specific operators: https://www.postgresql.org/docs/9.3/functions-json.html dot.notation isn't used for traversing through data in PG
l
Thank you for you answer! Those JSON functions are probably what I'm looking for!
I need further help described inside the comments of the code, would be much appreciated:
Copy code
sql
create table incoming_order(...) -- id, and other columns

create table product(...) -- id, and other columns

create table order_item(
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_id int not null,
  CONSTRAINT fk_product FOREIGN KEY(product_id) REFERENCES product(id), 
  quantity int not null,
  incoming_order_id int not null,
  CONSTRAINT fk_order FOREIGN KEY(incoming_order_id) REFERENCES incoming_order(id)
); -- An incoming_order can have many order_items.

create function new_incoming_order(cart_input json)
-- cart_input will be an array with products (objects). Could look something like this: 
-- [{product_id: 2, quantity: 3}, {product_id: 1, quantity: 4}]
  returns setof incoming_order
  as $$
    declare
      var_incoming_order_id int;
    begin

      insert into incoming_order(some_column)
      values(some_value)
      returning id into var_incoming_order_id;

      create type some_type as (product_id int, quantity int);

      insert into order_item(
      select * from json_populate_recordset(null::some_type, cart_input)
      );

      -- What is left and that I need help with is, I need to insert var_incoming_order_id into order_item.incoming_order_id TOGETHER with every new row created from json_populate_recordset. Maybe I should use something other than json_populate_recordset? But what then? And how would it look like?

      return query select * from incoming_order where incoming_order.id = var_incoming_order_id;
    end;
  $$ language plpgsql
Actually I think I can fix this if I just get help with this: Using postgreSQL or pgplsql, how can I modify a JSON object array to append a new key:value pair to every object in the array (they key:value pair is same on all objects in the array)?
Copy code
json
[
  {product_id: 1, quantity: 4},
  {product_id: 2, quantity: 3}
]
->
Copy code
json
[
  {product_id: 1, quantity: 4, incoming_order_id: 10},
  {product_id: 2, quantity: 3, incoming_order_id: 10}
]
s
You'll probably want to use the 
jsonb_set
function. There's a good overview with some examples at https://aaronbos.dev/posts/update-json-postgresql e.g.:
Copy code
sql
UPDATE public.person
SET personb = jsonb_set(personb, '{interests, 2}', '"Teaching"', true)
WHERE personb ->> 'last_name' = 'Ellis';
l
Thank you! I do this
Copy code
sql
  UPDATE cart_input
  SET cart_input = jsonb_set(cart_input, '{incoming_order_id, 2}', var_incoming_order_id, true);

  insert into order_item(
    select * from jsonb_populate_recordset(null::order_item, cart_input)
  );
but get this error:
Copy code
{
  message: 'relation "cart_input" does not exist',
  code: '42P01',
  details: null,
  hint: null
}
If I try to do it by removing the 
UPDATE cart_input
line, I get 
syntax error at or near "("
PreviousNext
Powered by