PostgreSQL after-trigger for table constraint

In PostgreSQL, I am not sure I understand after-triggers:

Within an AFTER insert or update FOR EACH STATEMENT trigger (function), is it correct that (contrary to e.g. MSSQL), if I query the trigger's table, I see the data before any changes are applied to the table?
And, if that is correct, how can I enforce a constraint over a whole table (for example, that the sum over some column does not exceed a certain value, or that at most N records have some boolean column set to true, etc.)?

The only approach I have been able to concoct is querying the table joining it with the deleted and inserted records in order to recover what the data would be after the statement, but I am new to PG and this seems a bit too much work for such a common requirement.

I have tried reading the docs as well as searching the web but I could not find a clear answer to the above, in fact not even an example.

Thanks for any clarification/advice.

Solution

Within an AFTER [trigger], is it correct that, if I query the trigger's table, I see the data before any changes are applied to the table?

No, that rather depends on the volatility category of the trigger function: a VOLATILE function will see the table changes, a STABLE or IMMUTABLE function will not. -- Indeed, I had declared my function STABLE, so I was not seeing the table changes.

See 38.7. Function Volatility Categories for more details. Here is an extract:

For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the volatility category, namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

In fact, SQL Commands / CREATE FUNCTION also mentions it:

[STABLE] is inappropriate for AFTER triggers that wish to query rows modified by the current command.

Finally, here is a basic example with a comparison between a STABLE and a VOLATILE trigger function that not only confirms the above findings, but should also serve as a minimal example of how to write (and not to write) AFTER triggers for table constraints (it's quite minimal since RAISE NOTICE does not write anywhere on DB Fiddle, nor we can insert records in some logging table from STABLE functions):

CREATE DATABASE "TestDb";

CREATE TABLE "TestTable_stable"
(
    "Name" varchar(20) NOT NULL,
    "Flag" bool        NOT NULL,
    PRIMARY KEY ("Name")
);

CREATE TABLE "TestTable_volatile"
(
    "Name" varchar(20) NOT NULL,
    "Flag" bool        NOT NULL,
    PRIMARY KEY ("Name")
);

CREATE FUNCTION "fn_TestTable_stable_TC"()
    RETURNS trigger
    LANGUAGE plpgsql
    STABLE
AS '
DECLARE
    _flagCount integer;
BEGIN
    SELECT COUNT("Name")
    FROM "TestTable_stable"
    WHERE "Flag" = true
    INTO STRICT _flagCount;
    
    IF NOT (_flagCount <= 1) THEN
        RAISE EXCEPTION
            ''(stable) FlagCount = %'', _flagCount;
    END IF;
    
    RETURN NULL;
END;
';

CREATE FUNCTION "fn_TestTable_volatile_TC"()
    RETURNS trigger
    LANGUAGE plpgsql
    VOLATILE
AS '
DECLARE
    _flagCount integer;
BEGIN
    SELECT COUNT("Name")
    FROM "TestTable_volatile"
    WHERE "Flag" = true
    INTO STRICT _flagCount;
    
    IF NOT (_flagCount <= 1) THEN
        RAISE EXCEPTION
            ''(volatile) FlagCount = %'', _flagCount;
    END IF;
    
    RETURN NULL;
END;
';

CREATE TRIGGER "TestTable_stable_TC"
    AFTER INSERT OR UPDATE OF "Flag"
    ON "TestTable_stable"
    FOR EACH STATEMENT
    EXECUTE FUNCTION "fn_TestTable_stable_TC"()
;

CREATE TRIGGER "TestTable_volatile_TC"
    AFTER INSERT OR UPDATE OF "Flag"
    ON "TestTable_volatile"
    FOR EACH STATEMENT
    EXECUTE FUNCTION "fn_TestTable_volatile_TC"()
;

-- (1) Test with implicit (i.e. statement-level) transactions:

INSERT INTO "TestTable_stable"    -- OK
VALUES
    ('A', true),
    ('B', false)
;

INSERT INTO "TestTable_stable"    -- NO ERROR! (needs volatile)
VALUES
    ('C', false),
    ('D', true)
;

INSERT INTO "TestTable_volatile"  -- OK
VALUES
    ('A', true),
    ('B', false)
;

INSERT INTO "TestTable_volatile"  -- ERROR (as expected)
VALUES
    ('E', false),
    ('F', true)
;

-- (2) Same test in a SERIALIZABLE transaction, same result:

TRUNCATE TABLE "TestTable_stable";
TRUNCATE TABLE "TestTable_volatile";

BEGIN ISOLATION LEVEL SERIALIZABLE;

INSERT INTO "TestTable_stable"    -- OK
VALUES
    ('S1', true),
    ('S2', false)
;

INSERT INTO "TestTable_stable"    -- NO ERROR! (needs volatile)
VALUES
    ('S3', false),
    ('S4', true)
;

INSERT INTO "TestTable_volatile"  -- OK
VALUES
    ('V1', true),
    ('V2', false)
;

INSERT INTO "TestTable_volatile"  -- ERROR (as expected)
VALUES
    ('V3', false),
    ('V4', true)
;

COMMIT;

Link to the code on DB Fiddle.