Postgresql – Get inserted row count when master table isn’t modified

partitioningplpgsqlpostgresql

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables.

Now, I need to create a PL/pgSQL procedure that SELECTs some data from the (master) table and re-inserts it with some changed values.

Normally I'd use the ROW_COUNT diagnostics variable and be done with it.
Trouble is that ROW_COUNT is always 0, because nothing gets inserted into the master table (the trigger catches and redirects inserts en route).

My next idea was to do something like

WITH inserted AS (
  INSERT INTO master (...) SELECT ... FROM master WHERE ... RETURNING master.field
) SELECT count(*) FROM inserted INTO rowcount;

but, again, rowcount always ends up being 0, because nothing was ACTUALLY inserted into the master table.

If I do

WITH insertable AS (
  SELECT ... FROM master WHERE ...
), blah AS (
  SELECT count(*) INTO rowcount FROM insertable
) INSERT INTO master SELECT * FROM insertable;

then Postgresql complains that:

ERROR: INTO used with a command that cannot return data

SQL state: 42601

Etc…

In case of my last code extract, how would I get the row count of inserted into a rowcount variable in my procedure that I can then do stuff™ with? Or is there any other way of retrieving the number of rows that were inserted into the (tables inheriting from) master table?

Best Answer

If this is still desirable for you (although not THAT vital), this is a solution to it:

CREATE TABLE rows_affected_in_session (
  session_pid int NOT NULL,
  session_time timestamptz NOT NULL,
  table_name text NOT NULL,
  rows_affected int NOT NULL,
  PRIMARY KEY (session_pid, session_time, table_name));

CREATE OR REPLACE FUNCTION f_update_rows_affected_in_session (p_table regclass, p_rows_affected int) RETURNS void AS $BODY$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM rows_affected_in_session WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text) THEN
    INSERT INTO rows_affected_in_session (session_pid, session_time, table_name, rows_affected)
    VALUES (pg_backend_pid(), current_timestamp, p_table::text, p_rows_affected);
  ELSE
    UPDATE rows_affected_in_session SET rows_affected = rows_affected + p_rows_affected
    WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text; 
  END IF;
END;
$BODY$ LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE FUNCTION f_rows_affected_in_session() RETURNS TABLE (table_name text, rows_affected int) AS $BODY$
  SELECT table_name, rows_affected
  FROM rows_affected_in_session
  WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp
  ORDER BY 1
$BODY$ LANGUAGE SQL SECURITY DEFINER;

You use it by calling the function f_update_rows_affected_in_session(<table>, <rows_affected>) after every successful insert within triggers (which you can use ROW_COUNT diagnostics), and then use the function f_rows_affected_in_session() to retrieve the rows affected within the session (you can determine in it exactly which child tables were affected, or sum the results for a grand total). Example:

SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT * FROM f_rows_affected_in_session();
Related Question