Home>

error occurred in postgresql trigger function and stored procedure

I'm currently studying postgreSQL triggers ...! Has an error and the cause and response are unknown, so please let me know.

What i did

I made the following stored procedure. After creating new user data, y is automatically added to test_flg.

Stored procedure creation 
CREATE OR REPLACE FUNCTION update_user () RETURNS TRIGGER AS $$
    BEGIN
        UPDATE User SET test_flg ='Y' WHERE test_flg ='N' and No like'No-M%';
    END;
$$LANGUAGE plpgsql;
Registered as a trigger function 
CREATE TRIGGER check_update
    AFTER INSERT ON User
    FOR EACH ROW
    EXECUTE PROCEDURE update_user ();
The error that is occurring 
DBExecuteError: ERROR: control reached end of trigger procedure without RETURN --Where: PL/pgSQL function

I don't need the return value this time, but I get a RETURN error.
What should I do here ...

postgresql
  • Answer # 1

    CREATE OR REPLACE FUNCTION update_user () RETURNS TRIGGER AS $$

    Does FUNCTION require RETURN?

    So

    trigger procedure without RETURN --Where: PL/pgSQL function
    I'm angry.

    EXECUTE PROCEDURE update_user ();

    It should be FUNCTION, but it is called by PROCEDURE.

    If test_flg ='N' and No like'No-M%', why not just set the BEFORE INSERT trigger to code that just sets test_flg to'Y'?

  • Answer # 2

    For triggers in the first placeFunction(RETURNS TRIGGER is required), so be surereturnIs required.
    * Return is not necessary for Function (returns void) that has no return value regardless of the trigger.

    The return value is not particularly necessary this time

    return Null;


    Please add.

Related articles