trouble with functions in postgresql

Question

in SUPABASE functions which is based on Postgresql I want to write a function that gives the customer's name as input then in the body of the function I want to implement this logic that if the customer has not been created yet, insert it to to customer table then store his id and create order row and if the customer already exists store his id then create order

here's the function :

create or replace function FUNC_new_order(name text , qty int)
returns void as 
$$
begin
    declare CSID int ; 
    select customer_id into CSID from customer where customer_name = name ; 
    if NOT FOUND then 
    insert into customer(customer_name) values(name);
    select customer_id into CSID from customer where customer_name = name ; 
    --create order 
    insert into "order"(customer_id , order_qty)values(CSID, qty);

end;
$$
language plpgsql ; 

but in supabase it returns this error :

ERROR:  42601: syntax error at or near "into"
LINE 22:    select customer_id into CSID from customer where customer_name = name ; 
                               ^
CONTEXT:  invalid type name "customer_id into CSID from customer where customer_name "

Answer 1

With postgres, you can insert in both tables, whether or not the customer exists, in a single query.
Example with test customer and quantity = 500

WITH SelectedCustomer AS (
INSERT INTO Customer(customer_name) VALUES ('test customer') ON CONFLICT DO NOTHING RETURNING customer_id
)
INSERT INTO "order"
SELECT customer_id, 500 FROM SelectedCustomer;

As you can see, the query uses a combination of:

• ON CONFLICT DO NOTHING: assuming customer_name is unique, do not insert a new client if it breaks the unique constraint.
• RETURNING customer_id: whether or not a customer was inserted, give me the corresponding customer_id
• CTE to put everything together when inserting the record into the order table (which, as I am sure you already know based on how you were forced to reference it in your question, is a terrible name for a table; I urge you to at least rename it to customer_order instead).

I let you put that single query into your function. If you wish, you can add a RETURNING order_id clause to get the id of the created order.