How can I create a Postgres 11 trigger function which inserts a new row in table 'b' upon insert or update to table 'a'?

Question

I'm running Postgres 11 on RDS.
I'm trying to create a simple trigger function to insert records into table 'test_alias' whenever a row is inserted into table 'test_values'.

I have the following tables:

CREATE TABLE the_schema.test_values (
    id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), 
    value_1 TEXT NOT NULL, 
    value_2 TEXT NOT NULL,
    value_quantity INTEGER NOT NULL
);

CREATE TABLE the_schema.test_alias (
    id INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), 
    value_1_copy TEXT NOT NULL, 
    value_2_copy TEXT NOT NULL,
    value_quantity_copy INTEGER NOT NULL
);

My trigger function is like so:

CREATE OR REPLACE FUNCTION the_schema.populate_test_alias()
RETURNS TRIGGER AS
$BODY$
BEGIN
IF NEW.the_schema.test_values THEN 
INSERT INTO the_schema.test_alias (value_1_copy, value_2_copy, value_quantity_copy)
VALUES (NEW.value_1, NEW.value_2, NEW.value_quantity);
END IF;
return null;
END; 
$BODY$ LANGUAGE plpgsql;

And here is the trigger:

CREATE TRIGGER TRG_TEST_ALIAS 
AFTER INSERT OR UPDATE ON the_schema.test_values
FOR EACH ROW 
execute procedure the_schema.populate_test_alias();

Upon INSERT like so:

INSERT INTO the_schema.test_values (value_1, value_2, value_quantity)
VALUES ('abc', 'xyz', 1);

I get this error:

ERROR:  missing FROM-clause entry for table "the_schema"
LINE 1: SELECT NEW.the_schema.test_values

I've also tried an equivalent setup with the default schema, and it still errors (though with a different error):

ERROR:  record "new" has no field "test_values"
CONTEXT:  SQL statement "SELECT NEW.test_values"
PL/pgSQL function populate_test_alias() line 3 at IF

It seems to me that there must be an error in the way I'm using the NEW keyword, but as far as I can tell, the way I've used it in the function is the same as several examples I've referred to (online/SO and hard copy), and I can't figure it out.

All guidance is much appreciated!

---

example of similar question for reference, includes links to official docs (which I've also read but clearly don't understand as I should): [https://stackoverflow.com/questions/11001118/postgres-trigger-after-insert-accessing-new]

Answer 1

NEW references the inserted or updated row. Therefore NEW. only makes sense with a field identifier.

Also value_1, value_2 and value_quantity have a NOT NULL constraint, which means that you need not test for them.

So you can just drop the whole conditional:

CREATE OR REPLACE FUNCTION the_schema.populate_test_alias()
RETURNS TRIGGER AS
$BODY$
BEGIN
--IF NEW.the_schema.test_values THEN 
INSERT INTO the_schema.test_alias (value_1_copy, value_2_copy, value_quantity_copy)
VALUES (NEW.value_1, NEW.value_2, NEW.value_quantity);
--END IF;
return null;
END; 
$BODY$ LANGUAGE plpgsql;