从代码点火器中的postgres更新查询获取'返回'值

Question

So I have a query like the following (actually more complicated but not important to the question, so I deleted some) where I 'return' a value.

The query works fine in sql:

$sql = "UPDATE user_authentication
            SET failed_login_attempts = failed_login_attempts + 1,
            RETURNING failed_login_attempts";

But in codeigniter it returns a boolean instead of an integer. This is what should happen with codeigniter's result() function, so it's not a bug or anything, but I am just wondering if there is a work-around to get the return value from an update statement. I know I can do a second query...

when I user $query->result();

Answer 1

You could chain it with a CTE :

WITH upd AS (UPDATE user_authentication
            SET failed_login_attempts = failed_login_attempts + 1
            RETURNING failed_login_attempts
            )
SELECT * FROM upd;