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
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?