I think you will want something like the following - see the fiddle here. It's based on Common Table Expressions (CTEs) and that fact that with PostgreSQL, you can perform not only SELECTs, but also INSERTs, UPDATEs and DELETEs (see here also).
First, your service table:
CREATE TABLE service
name VARCHAR (10) NOT NULL PRIMARY KEY,
id INTEGER NOT ...
You can use INFORMATION_SCHEMA views and special variables: TG_TABLE_SCHEMA, TG_TABLE_NAME
Quoted from docs:
When a PL/pgSQL function is called as a trigger, several special
variables are created automatically in the top-level block. They are:
TG_TABLE_NAME: Data type name; the name of the table that caused the
In a BEFORE INSERT trigger, the new row has not yet been inserted into the table. So when the query searches for points.id with the new value, it will not find it.
And there is no need to read the points table because you want to access only the new row, and all those values are available in NEW:
NEW.id AS point_id,
Obviously I can hard code in the columns myself into the trigger and audit tables, but I'd rather have a solution which doesn't need updating every time there is a schema change on the table.
That is nearly the correct approach. Write a stored procedure to generate your triggers and run it any time you change the schema.
This is called an aggregate table. This is a valid strategy.
You could also defer the calculation of user reputation if you are not bothered with real-time updates. Some sites do DB maintenance during the night, rebuild indexes etc. Since we are talking about user reputation, it's no big deal if the current stats are only reflected the day after. I don't ...
A trigger requires a trigger function. And those can only be written in PL/pgSQL.
So you need something like this:
CREATE OR REPLACE FUNCTION schema1.clone_to_table2()
INSERT INTO schema2."table2" ("uid, "did")
BEGIN does not require ...
I'm putting this here since it's a bit long, but I don't think it should qualify as an answer. There are no answers here, just observations and advice.
Short version, there isn't anything that can be done to make the query go faster and achieve the same results. You need to change the process that is feeding data into the main table if you want batching ...