获取受PostgreSQL中INSERT或UPDATE影响的记录数 [英] Get count of records affected by INSERT or UPDATE in PostgreSQL

问题描述

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

My database driver for PostgreSQL 8/9 does not return a count of records affected when executing INSERT or UPDATE.

PostgreSQL提供了非标准语法 返回，这似乎是一个不错的解决方法。但是语法可能是什么？该示例返回一条记录的ID，但我需要一个计数。

PostgreSQL offers the non-standard syntax "RETURNING" which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count.

插入分配器（did，dname）中的值（默认值，' XYZ窗口小部件'）
返回确实；

推荐答案

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

I know this question is oooolllllld and my solution is arguably overly complex, but that's my favorite kind of solution!

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

Anyway, I had to do the same thing and got it working like this:

-- 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子句写一个爱情十四行诗。

One of these days I really have to get around to writing a love sonnet to PostgreSQL's WITH clause ...

这篇关于获取受PostgreSQL中INSERT或UPDATE影响的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！