Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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?

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
$BODY$BEGIN
-- 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 );
ELSE
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 );
END IF;
END;$BODY$
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?

Thanks,
Sternocera
0
sternocera
Asked:
sternocera
1 Solution
 
earth man2Commented:
You are in effect doing the check twice.

http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
...
declare
  flag boolean = TRUE;
begin
  begin
    INSERT INTO stock VALUES (nextva('seq_sku_product') ,$1,$2,$3,$4,$5,$6,$7,$8,$9,
            $10,$11,$12,$13,$14,$15,$16,nextval('seq_id_price'),TRUE);
  exception
     when UNIQUE VIOLATION then
        flag := FALSE;
  end;
  return flag;
end;
$$ language plpgsql;
0
 
sternoceraAuthor Commented:
Great, thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now