This is the first in a series of “How do I do X in Greenplum database” and today’s first topic is How do I get the number of rows Inserted (or Updated/Deleted).
Knowing the number of rows Inserted, Updated, or Deleted is pretty common in database programming. You may want to key off of this to determine the next step in a procedure or just capture the number of rows in an ETL process.
Sybase and Microsoft SQL Server use a Global Variable to do this which is @@ROWCOUNT. It is dynamically populated after each statement execution. In Oracle, you can use SQL%ROWCOUNT to do the same thing.
In Greenplum and in a PL/pgSQL function, you can get the rowcount by using GET DIAGNOSTICS. Here is an example.
CREATE OR REPLACE FUNCTION fn_update_person() RETURNS void AS $$ DECLARE v_rowcount int; BEGIN UPDATE person SET state_code = UPPER(state_code); GET DIAGNOSTICS v_rowcount = ROW_COUNT; RAISE INFO '% Rows Updated.', v_rowcount; END; $$ language plpgsql;
There you go. Pretty simple to get the ROW_COUNT in Greenplum.