I'm trying to implement a many-to-many relationship in PostgreSQL. Here are the tables from my database:
CREATE TABLE products (
product_id serial PRIMARY KEY
, product_name varchar NOT NULL
);
CREATE TABLE orders (
order_id serial PRIMARY KEY
, order_name varchar NOT NULL
);
CREATE TABLE product_order (
product_id int REFERENCES products
, order_id int REFERENCES orders
, PRIMARY KEY (product_id, order_id)
);
There will not be any UPDATEs or DELETEs in the products and orders tables, so there is no need for ON DELETE and ON UPDATE statements.
I have also created two hash indexes so I can search for orders and products names and get their id stored in the table:
CREATE INDEX product_index ON products USING hash(product_name);
CREATE INDEX order_index ON orders USING hash(order_name);
Here's what I'm trying to do:
- I want to insert into the product table and return the id of the inserted row.
- I want to insert into the order table and return the id of the inserted row.
- I want to insert both the product_id and order_id into the product_order table.
There is an edge case:
If the product that I want to insert is already in the product table, then I don't want to create another row with a different id. In this case, I want to retrieve the product_id that is already in the table.
This edge case is the same for order.
To accomplish all these, I've created an SQL FUNCTION:
CREATE OR REPLACE FUNCTION add_product_order(myproduct varchar, myorder varchar)
RETURNS VOID
LANGUAGE sql
AS
$$
WITH pro AS (
WITH p as (
SELECT product_id FROM products WHERE product_name = myproduct -- check if product is already in the table
)
INSERT into products (product_name) -- insert into products and get the product_id only if myproduct was not found
SELECT (myproduct)
WHERE NOT EXISTS (
SELECT product_id FROM p
)
RETURNING product_id
),
ord AS (
WITH o as(
SELECT order_id FROM orders WHERE order_name = myorder -- check if order is already in the table
)
INSERT into orders (order_name) -- insert into orders and get the order_id only if myorder was not found
SELECT (myorder)
WHERE NOT EXISTS (
SELECT order_id FROM o
)
RETURNING order_id
)
INSERT INTO product_order (product_id, order_id) -- insert both FK ids into the product_order table
SELECT pro.product_id, ord.order_id FROM pro, ord;
$$;
After creating the function, I execute the following SQL query to run it:
select add_product_order('product1','order1');
Everything seems to be fine, but it only works when the product I'm trying to insert is not in the table.
If the product is already in the table, the first SELECT returns the product_id in the temporary p table. But I don't know how to get a hold of p.product_id in the last INSERT INTO product_order.
Seeing I can't get too far with this, I also tried with a plpgsql FUNCTION:
CREATE OR REPLACE FUNCTION add_product_order(myproduct varchar, myorder varchar)
RETURNS VOID
LANGUAGE plpgsql
AS
$$
DECLARE
id_product integer;
id_order integer;
BEGIN
SELECT product_id INTO id_product FROM products WHERE product_name = myproduct; -- check if product is already in the table
IF NOT FOUND THEN
RAISE INFO 'product % not found', myproduct;
INSERT INTO products (product_name) VALUES (myproduct) RETURNING product_id; -- product not found, so insert it and get the id
id_product := product_id; -- Tried also with SELECT product_id INTO id_product;
END IF;
SELECT order_id INTO id_order FROM orders WHERE order_name = myorder; -- check if order is already in the table
IF NOT FOUND THEN
RAISE INFO 'order % not found', myorder;
INSERT INTO orders (order_name) VALUES (myorder) RETURNING order_id; -- order not found, so insert it and get the id
id_order := order_id;
END IF;
INSERT INTO product_order (product_id, order_id) VALUES (id_product, id_order); -- insert both ids into the product_order table
END;
$$;
This plpgsql FUNCTION should solve the problem I had in the SQL function above.
But it gives me an error: query has no destination for result data
What is the correct way to accomplish this?
PS. I searched and read multiple answers before I posted this question:
