I use this code to verify the DELETE
sentence, but I am sure you know a better way:
CREATE OR REPLACE FUNCTION my_schema.sp_delete_row_table(table_name character varying
, id_column character varying
, id_value integer)
RETURNS integer AS
$BODY$
DECLARE
BEFORE_ROWS integer;
AFTER_ROWS integer;
BEGIN
EXECUTE 'SELECT count(*) FROM ' || TABLE_NAME INTO BEFORE_ROWS;
EXECUTE 'DELETE FROM ' || TABLE_NAME || ' WHERE ' || ID_COLUMN || ' = ' || (ID_VALUE)::varchar;
EXECUTE 'SELECT count(*) FROM ' || TABLE_NAME INTO AFTER_ROWS;
IF BEFORE_ROWS - AFTER_ROWS = 1 THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
How to improve this code? I need it to work in Postgres 8.4, 9.1 and 9.2.
Look into the variables called found
and row_count
:
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
found
is true if any rows were affected. row_count
gives you the number of affected rows.
IF FOUND THEN
GET DIAGNOSTICS integer_var = ROW_COUNT;
END IF;