Hot answers tagged

3

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


2

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 trigger invocation. ...


2

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: SELECT NEW.id AS point_id, schema....


2

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.


2

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


1

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() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO schema2."table2" ("uid, "did") VALUES(new.uid::text, new.did); return new; END; $function$ ; BEGIN does not require ...


1

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


1

You can catch the update of column10 in the trigger function IF OLD.column10 = NEW.column10 THEN ... do the trigger stuff ... ELSE ... do nothing, because column10 has been changed END IF;


Only top voted, non community-wiki answers of a minimum length are eligible