获取 PostgreSQL 中受 INSERT 或 UPDATE 影响的记录数

Question

我的 PostgreSQL 8/9 数据库驱动程序不会返回执行 INSERT 或 UPDATE 时受影响的记录计数。

PostgreSQL 提供非标准语法“RETURNING”，看起来像一个很好的解决方法。但语法可能是什么？该示例返回记录的 ID，但我需要一个计数。

插入分销商（did、dname）值（默认，“XYZ 小部件”） 返回了；

Answer 1

我知道这个问题是 oooolllllld 并且我的解决方案可以说过于复杂，但这是我最喜欢的解决方案！

无论如何，我必须做同样的事情并让它像这样工作：

-- Get count from INSERT
WITH rows AS (
    INSERT INTO distributors
        (did, dname)
    VALUES
        (DEFAULT, 'XYZ Widgets'),
        (DEFAULT, 'ABC Widgets')
    RETURNING 1
)
SELECT count(*) FROM rows;

-- Get count from UPDATE
WITH rows AS (
    UPDATE distributors
    SET dname = 'JKL Widgets'
    WHERE did <= 10
    RETURNING 1
)
SELECT count(*) FROM rows;

这些天我真的必须抽出时间为 PostgreSQL 的WITH子句写一首爱情十四行诗......

Answer 2

我同意 Milen，您的司机应该为您做这件事。您使用什么驱动程序以及什么语言？但如果您使用 plpgsql，则可以使用 GET DIAGNOSTICS my_var = ROW_COUNT;

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-语句-诊断

Answer 3

您可以在更新后获取 ROW_COUNT 或使用以下代码插入：

insert into distributors (did, dname) values (DEFAULT, 'XYZ Widgets');
get diagnostics v_cnt = row_count;

Answer 4

您可以将查询包装在事务中，它应该在您ROLLBACK或COMMIT之前显示计数。示例：

BEGIN TRANSACTION;

INSERT .... ;

ROLLBACK TRANSACTION;

如果您运行上面的前两行，它应该会给您计数。如果您发现受影响的行数不是您所期望的，则可以ROLLBACK（撤消）插入。如果您对 INSERT 的正确性感到满意，那么您可以运行相同的操作，但将第 3 行替换为 COMMIT TRANSACTION;。

重要说明：运行任何BEGIN TRANSACTION;后，您必须ROLLBACK;或COMMIT;< /code> 事务，否则事务将创建一个锁，如果您在生产环境中运行，该锁可能会减慢甚至瘫痪整个系统。

Answer 5

从你的问题中并不清楚你如何称呼该声明。假设您使用的是 JDBC 之类的东西，您可能会将其称为查询而不是更新。来自 JDBC 的 <代码>executeQuery：

执行给定的 SQL 语句，该语句返回单个 ResultSet
对象。

因此，当您执行返回某些查询结果的语句（例如 SELECT 或 INSERT ... RETURNING）时，这是合适的。如果您正在对数据库进行更新，然后想知道有多少元组受到影响，则需要使用 executeUpdate 返回：

(1) SQL 数据操作语言 (DML) 的行计数
语句或 (2) 0 表示不返回任何内容的 SQL 语句

Answer 6

我可能会很晚才重播问题，但这会对像我这样寻求完整答案的人有所帮助，希望

CREATE TABLE admin_layer.test(id integer, user_name varchar);

INSERT INTO admin_layer.test(id,user_name) values(1,'Ganesh'),(2, 'Nick');

-- Using Function
CREATE FUNCTION admin_layer.fn_insert_test(_id INTEGER, _user_name varchar)
RETURNS INTEGER AS 
$BODY$
DECLARE _row_count INTEGER;
BEGIN
EXECUTE format('INSERT INTO admin_layer.test(id,user_name) VALUES(%s,%L)',_id, _user_name);
GET DIAGNOSTICS _row = ROW_COUNT;
RETURN _row_count;
END;
$BODY$ LANGUAGE plpgsql;

-- Calling function
-- SELECT admin_layer.fn_insert_test(123, 'John Ray') AS inserted_rows;

-- using a 'DO' block to execute an anonymous code block.
DO $
DECLARE
    _inserted_rows INTEGER := 0;
BEGIN
    _inserted_rows := admin_layer.fn_insert_test(123, 'John Doe');
    
    -- Check if any rows were inserted
    IF _inserted_rows > 0 THEN
        RAISE NOTICE 'Rows inserted: %', _inserted_rows;
        -- Additional processing based on the number of rows inserted
    ELSE
        RAISE NOTICE 'No rows inserted';
        -- Handle the case where no rows were inserted
    END IF;
END;
$;


-- Using Procedure
CREATE PROCEDURE admin_layer.proc_insert_test(_id INTEGER, _user_name VARCHAR, INOUT _row_count INTEGER)
LANGUAGE plpgsql
AS $
BEGIN
    EXECUTE format('INSERT INTO admin_layer.test(id, user_name) VALUES (%s, %L)', _id, _user_name);
    GET DIAGNOSTICS _row_count = ROW_COUNT;
END;
$;

--  using a 'DO' block to execute an anonymous code block.
DO $BODY$
DECLARE
    _inserted_rows INTEGER:=0; -- Initialize to 0
BEGIN
    CALL admin_layer.proc_insert_test(123, 'John Sins', _inserted_rows);    
    -- Check if any rows were inserted
    IF inserted_rows > 0 THEN
        RAISE NOTICE 'Rows inserted: %', _inserted_rows;
        -- Additional processing based on the number of rows inserted
    ELSE
        RAISE NOTICE 'No rows inserted';
        -- Handle the case where no rows were inserted
    END IF;
END;
$BODY$

-- To get updated rows count using procedure
CREATE PROCEDURE admin_layer.proc_update_test(_id INTEGER, _new_user_name VARCHAR, INOUT _updated_rows INTEGER)
LANGUAGE plpgsql
AS $
BEGIN
    EXECUTE format('UPDATE admin_layer.test SET user_name = %L WHERE id = %s;',$2, $1); 
    -- or instead using position we can use the arguments (_new_user_name, _id)
    GET DIAGNOSTICS _updated_rows = ROW_COUNT;
END;
$;

-- 
DO $
DECLARE
    _updated_rows INTEGER := 0;
BEGIN
    CALL admin_layer.proc_update_test(123, 'Max', _updated_rows);
    RAISE NOTICE 'Rows updated: %', _updated_rows;
END;
$;

-- We can achieve this without using function or procedure
DO $
DECLARE
    _updated_rows INTEGER := 0;
BEGIN
    UPDATE admin_layer.test SET user_name = 'Jony Sins' WHERE id = 123;    
    GET DIAGNOSTICS _updated_rows = ROW_COUNT;
    -- Check if any rows were updated
    IF _updated_rows > 0 THEN
        RAISE NOTICE 'Rows updated: %', _updated_rows;
        -- Additional processing based on the number of rows updated
    ELSE
        RAISE NOTICE 'No rows updated';
        -- Handle the case where no rows were updated
    END IF;
END;
$;

select * from admin_layer.test