Clever Geek Handbook

PostgreSQL update function

I have a question regarding an update function that 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;

Ok, so if I enter an entry that doesn't exist, like 9, it returns success even though I know it hasn't updated anything!

Does SQL throw errors if it updates a non-existent row?

thank

+1


source to share


2 answers


It depends on the DBMS - but no, it shouldn't cause 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. (When using Informix, if you use ANSI MODE database, you get SQLNOTFOUND; if you use non-ANSI database, you get 0 (no error) as a condition. The reasons for this predate the original SQL-86 standard.)



Please note that SQL is a set-based language. What you requested was that the statement updates a set of (matched) rows - and it is quite correct to update a set containing null (matched) rows.

0


source


You can use "FOUND" to determine if the last statement affected one or more lines, see "Guide" .

Example:



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


source






More articles:


All Articles