2

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]

4
  • 1
    What is this IF NEW.the_schema.test_values THEN supposed to test for? NEW references the inserted row . Commented Feb 3, 2021 at 23:58
  • Good question! I copied that from a tutorial, but now that you mention it, it does seem redundant -- let me try without the conditional in there - - EDIT: with the if statement removed, I get the following 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 Commented Feb 4, 2021 at 0:00
  • 1
    Remove the whole line - and the END iF obviously. Commented Feb 4, 2021 at 0:03
  • nice, that did the trick! Thanks for your help, I sure appreciate it! Commented Feb 4, 2021 at 0:18

1 Answer 1

2

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;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.