计算受DELETE影响的行 [英] Count rows affected by DELETE

问题描述

我使用此代码来验证DELETE句子，但我确定您知道更好的方法:

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;

如何改进此代码?我需要它在Postgres 8.4、9.1和9.2中工作.

How to improve this code? I need it to work in Postgres 8.4, 9.1 and 9.2.

推荐答案

查看名为found和row_count的变量:

http://www.postgresql. org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

found为true. row_count为您提供了受影响的行数.

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;

这篇关于计算受DELETE影响的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！