PostgreSQL has the best architecture for developing SQL codes. So, inside the function, you can not use transaction commit or start. Transactions work only inside the procedures. But, you can create your best ACID structure only by using functions. Because the function begin keyword is equivalent to start transaction command and the end keyword of the function is equivalent to commit transaction command. One function = One transaction. But if you need to use one transaction for inserting data into two tables, you can use your insert functions for the same tables inside the one function. In this variant, your main function will be your main transaction. Inside the main function, if one of the internal functions will have an exception so, other internal functions will be rollbacked. For example, we use sub-functions and sub-sub functions inside the main function. If one of the sub-sub functions will have exceptions then your main function will be rollbacked. Using this mechanism you will control your transactions fully, without any problems.
I wrote your query in PostgreSQL, Example:
begin transaction;
INSERT INTO Customers(cust_id,cust_name) VALUES('1000000010','Toys Emporium');
savepoint StartOrder;
commit;
INSERT INTO Orders(order_num,order_date,cust_id) VALUES(20100,'1999/12/1','1000000010');
commit;
exception when others then
rollback to StartOrder;
commit;
