Update function in postgres help

Question

Hi there...

I have a question regarding an update function I created...

CREATE OR REPLACE FUNCTION rm_category_update(icompany bpchar, iraw_mat_cat_code bpchar, iraw_mat_cat_desc bpchar)

RETURNS character AS

$BODY$

DECLARE
   loc_result    CHAR(50); 

BEGIN

UPDATE rm_category

 SET
    raw_mat_cat_code    = iraw_mat_cat_code,
    raw_mat_cat_desc    = iraw_mat_cat_desc

WHERE company = icompany;

loc_result = 'success';

RETURN loc_result ;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION rm_category_update(icompany bpchar, iraw_mat_cat_code bpchar, iraw_mat_cat_desc bpchar) OWNER TO postgres;

Okay, so if I input a record that doesn't exist, for example 9, it returns success even though I know it has updated nothing!

Does SQL not throw errors if it is updating a non-existent row??

Thanks

Answer 1

It depends on the DBMS - but no, it should not throw an error. According to the SQL standard (ISO/IEC 9075:2008 these days), it should set the SQLNOTFOUND condition (+100), which is separate from any error condition. (With Informix, if you use a MODE ANSI database, you get SQLNOTFOUND; if you use a non-ANSI database, you get 0 (no error) as the condition. The reasons for that pre-date the original SQL-86 standard.)

Note that SQL is a set-based language. What you requested was that the statement update a set of (matching) rows -- and it is perfectly valid to update a set containing zero (matching) rows.

Answer 2

You could use "FOUND" to detect if the last statement affected one or more rows, see Manual.

Example:

-- snippet
IF FOUND THEN
  loc_result = 'success';
ELSE
  loc_result = 'failed'; -- or something similiar...
END IF;

Answer 3

Cool, Thats actually a really smart idea!

Thanks a million...