Putting a IFTHEN condition into a Trigger Function

Question

I have two tables

Main table
Sub table

My intention is to use a trigger function to insert the new row into the Sub Table if it fulfils a condition of a column not being Null.

I have tried this method but my Sub Table will still insert the row with a null value

BEGIN
    INSERT INTO unsubscriber_list("ID","Name","Email","unsubdate")
    SELECT new."ID",new."Name",new."Email",new."date_of_unsub" FROM master_database
    WHERE date_of_unsub IS NOT NULL
    ORDER BY "account_created_on" DESC LIMIT 1;
    RETURN NULL;
END;

I have also tried an IFTHEN condition but it produces error

BEGIN
    IF master_database.date_of_unsub IS NOT NULL THEN
    INSERT INTO unsubscriber_list("ID","Name","Email","unsubdate")
    SELECT new."ID",new."Name",new."Email",new."date_of_unsub" FROM master_database;
    END IF;
    RETURN NULL;
END;

This is the error that they showed

ERROR:  missing FROM-clause entry for table "master_database"
LINE 1: SELECT master_database.date_of_unsub IS NOT NULL

My expected result is one whereby an Insert Trigger activates the Trigger Function whereby it validates if that specific column IS NOT NULL

If Null: ignore If not Null: Insert.

Hi Philip! can you explain more please? I'm a total newbie at this — Penn Han
– Penn Han, Commented May 7, 2019 at 2:19
Both of your queries would trigger errors because of the undefined table alias name "new". However according to your description the first one (partly) worked. I'm wondering if you modified it a little bit and missed some part. — Philip Tzou
– Philip Tzou, Commented May 7, 2019 at 2:22
Philip, thank you for taking time out to solve my problem. I've googled a little bit more about New and it seems that it is a special variable that is auto-created. New Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations. I played around with my code with this new knowledge and solved the problem. I will be posting the answer when i tidy up the code. Thanks again! — Penn Han
– Penn Han, Commented May 7, 2019 at 2:29
@PhilipTzou: the new record is automatically available in a trigger function. — user330315
– user330315, Commented May 7, 2019 at 5:47

Penn Han · Accepted Answer · 2019-05-07 02:34:05Z

1

After playing around with my code, it seems that I have solved the problem.

I used the IFTHEN function and added a NEW (which according to postgresql, is a special variable that is automatically created for a trigger function to hold the newly created row.)

this is my code

BEGIN
    IF NEW.date_of_unsub IS NOT NULL THEN
    INSERT INTO unsubscriber_list("ID","Name","Email","unsubdate")
    SELECT NEW."ID",NEW."Name",NEW."Email",NEW."date_of_unsub" FROM master_database;
    END IF;
    RETURN NULL;
END;

I have tested the function by inserting a script and it seems to create the appropriate row while ignoring inappropriate ones.

Then again, I'm a total newbie at Postgresql so I might not have the nicest code. Will be looking out for cleaner ways to produce such a function!

answered May 7, 2019 at 2:34

Penn Han

113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

The function looks alright, except you should RETURN NEW.

404 Over a year ago

Why are you selecting NEW values from master_database? The master_database table (I assume that's what it is) has nothing to do with the NEW row variable. If you want to select NEW values, just do INSERT INTO ... VALUES (NEW."ID", NEW."Name", ...). Also indent the code within the IF block for readability.

Collectives™ on Stack Overflow

Putting a IFTHEN condition into a Trigger Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related