PostgreSQL: Boolean PL/pgSQL function that will be called from within a C++ program: Should I enforced a constraint myself, or leave it up to a unique constraint?
Posted on 2007-10-02
I've written a PL/PgSQL boolean function that inserts a new product into my database. My intention is to call this function from within my C++ frontend, and have it return false if it doesn't work because any one of various error conditions arise:
CREATE OR REPLACE FUNCTION add_product(description text, active boolean, soldby_u_w_v soldby_u_w_v, department_id integer, supplier integer, source integer, product_code integer, resale_item boolean, special_offer integer, ean_13_barcode_unit barcode, ean_13_barcode_between1 barcode, ean_13_barcode_between2 barcode, ean_13_barcode_case barcode, units_between1 smallint, units_between2 smallint, units_case smallint, price currency, costprice currency) RETURNS boolean AS
-- Make Main product record
-- Test that barcode doesn't already exist
IF (test_product_of_barcode_exists($10) OR test_product_of_barcode_exists($11) OR test_product_of_barcode_exists($12) OR test_product_of_barcode_exists($13)) THEN
RETURN ( FALSE );
INSERT INTO stock VALUES (nextval('seq_sku_product') ,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,nextval('seq_id_price'),TRUE);
--Make first price record (possibly of many)
INSERT INTO prices VALUES(CURRVAL('seq_id_price'), CURRVAL('seq_sku_product'), $17, $18, now());
RETURN ( TRUE );
LANGUAGE 'plpgsql' VOLATILE;
I draw your attention to the "-- Test that barcode doesn't already exist" part of the if - It calls a function that tests the existence of a barcode, and thus enforces a unique constraint. However, the various barcode types already have a unique constraint at the table level.
The questions are, is it more sensible to leave enforcing the unique constraint up to the unique constraint? Can I have this function return false if the unique constraint prevents the INSERT from working at all?