Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

@@ROWCOUNT in PostgreSQL 9.3

I want to returns the number of rows affected by the last statement.

Using Microsoft SQL Server 2008 R2 I do it this way:

SELECT * FROM Test_table;

SELECT @@ROWCOUNT AS [Number Of Rows Affected];

Will gives:

Number Of Rows Affected
-----------------------
10

How about in PostgreSQL 9.3?

like image 921
MAK Avatar asked Jan 23 '15 11:01

MAK


People also ask

What is SELECT @@ rowcount?

Usage. SQL Server @@ROWCOUNT is a system variable that is used to return the number of rows that are affected by the last executed statement in the batch.

What is the use of @@ rowcount?

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client.

How do you find the row count for all your tables in Postgres?

The basic SQL standard query to count the rows in a table is: SELECT count(*) FROM table_name; This can be rather slow because PostgreSQL has to check visibility for all rows, due to the MVCC model.

How do I count the number of rows returned by a query in PostgreSQL?

The COUNT(*) function returns the number of rows returned by a SELECT statement, including NULL and duplicates. When you apply the COUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially. If you use the COUNT(*) function on a big table, the query will be slow.


2 Answers

DO $$
DECLARE
     total_rows integer;
BEGIN
  UPDATE emp_salary
   SET salary = salary+1;
   IF NOT FOUND THEN
      RAISE NOTICE 'No rows found';
   ELSIF FOUND THEN
   GET DIAGNOSTICS total_rows := ROW_COUNT;
      -- the above line used to get row_count
      RAISE NOTICE 'Rows Found : total_rows: %', total_rows;
   END IF; 
END $$;
like image 164
Achilles Ram Nakirekanti Avatar answered Nov 05 '22 20:11

Achilles Ram Nakirekanti


AFAIK there is no such construct in postgresql however the number of rows is part of the result you get from postgresql.

CORRECTION: as a_horse_with_no_name states in his comment there is something similar which can be used within PL/pgSQL. Also see example in answer posted by Achilles Ram Nakirekanti

From within programs however my original suggestion is in most cases easier then having to resort to the usage of PL/pgSQL.

When using libpq: On the result of a select you can use PQntuples to determine the number of rows returned. For update, insert and delete you can use PQcmdTuples with the result to get the number of rows affected.

Other client libraries often have similar functionality.

For REF from referred article: GET DIAGNOSTICS integer_var = ROW_COUNT;

like image 40
Eelke Avatar answered Nov 05 '22 21:11

Eelke