PostgreSQL: Get ROW_COUNT of Last Executed Query Like MS SQL @@ROWCOUNT

In Microsoft SQL Server, @@ROWCOUNT returns the number of rows affected by the most recent SQL statement. PostgreSQL doesn’t use @@ROWCOUNT, but provides a similar way to get the affected row count using the GET DIAGNOSTICS statement. :contentReference[oaicite:0]{index=0}

What is ROW_COUNT in PostgreSQL?

In PostgreSQL, the equivalent of MSSQL’s @@ROWCOUNT is the ROW_COUNT value retrieved via GET DIAGNOSTICS. This captures the number of rows impacted by the last INSERT, UPDATE, or DELETE statement executed in a PL/pgSQL block. :contentReference[oaicite:1]{index=1}

Example: Using GET DIAGNOSTICS

The following example shows how to update records and capture the number of rows affected:

DO $$
DECLARE
    v_RowCountInt INTEGER;
BEGIN
    UPDATE usermst
    SET emailid = 'abc@gmail.com';

    GET DIAGNOSTICS v_RowCountInt = ROW_COUNT;
    RAISE NOTICE 'Rows affected: %', v_RowCountInt;
END;
$$;

In this example:

• UPDATE usermst modifies rows.
• GET DIAGNOSTICS v_RowCountInt = ROW_COUNT retrieves how many rows were changed.
• RAISE NOTICE prints out the count. :contentReference[oaicite:2]{index=2}

When to Use ROW_COUNT

You typically use ROW_COUNT when you need logic based on whether a statement impacted data — for example:

• Check if an update modified any rows before continuing.
• Log how many records were affected.
• Trigger further actions if rows were modified. :contentReference[oaicite:3]{index=3}

Important Notes

PostgreSQL’s GET DIAGNOSTICS … = ROW_COUNT works only inside PL/pgSQL or anonymous blocks (like the DO block above). It doesn’t directly mimic the global variable style of MSSQL’s @@ROWCOUNT, but it achieves the same result.

If you simply want to know how many rows are currently in a table without affecting data, you can use:

SELECT COUNT(*) FROM table_name;

This returns the total number of rows in the table but is not related to the last statement executed. :contentReference[oaicite:4]{index=4}

Summary

While PostgreSQL doesn’t have an exact equivalent to MSSQL’s @@ROWCOUNT, you can use GET DIAGNOSTICS within PL/pgSQL to capture how many rows were affected by the most recent INSERT, UPDATE, or DELETE statement. This is useful for flow control and reporting in stored procedures and scripts. :contentReference[oaicite:5]{index=5}