Postgresql – SQL Error [42883] – function does not exist – Cannot call function from trigger

functionspostgresqlpostgresql-10trigger

I want to insert a new row to Table2 when Table 1 is inserted a row. I have created a function for that. However, when I try to create the trigger, it gives an error although function is created and I can see the function.

SQL Error [42883]: ERROR: function insertintoautoincrementexample() does not exist

Following are the details related to it. I tried to create the trigger with some text parameters but no luck. 

//Table 1
CREATE TABLE myOriginalTable(
    companyName VARCHAR(15) PRIMARY KEY NOT NULL,
    location VARCHAR(30)    
);    

//Table 2    
CREATE TABLE autoincrementexample (
    entryid INT4 NOT NULL DEFAULT nextval('autoincrementexample_entryid_seq'::regclass),
    companyname VARCHAR(15) NOT NULL,
    location VARCHAR(30) NOT NULL,
    PRIMARY KEY (companyname)
);

//Function    
CREATE OR REPLACE FUNCTION insertIntoAutoIncrementExample(companyname text,location text)

    returns void
    language plpgsql
    as $function$

    declare
    varaudseq bigint;

    begin
        varaudseq :=0;

    begin
        SELECT NEXTVAL(autoincrementexample) INTO varaudseq;
        INSERT INTO autoincrementexample(companyname,location)VALUES(companyName,location);
    COMMIT;
    END;
    END;
    $function$;

//Trigger
CREATE TRIGGER after_insert_original 
    AFTER INSERT ON myoriginaltable
    FOR EACH row
    EXECUTE PROCEDURE insertintoautoincrementexample();

Would appreciate any help regarding this.

Best Answer

As the documentation says:

The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)

The function you have declared is:

insertIntoAutoIncrementExample(companyname text,location text) ... returns void

so it does not fit both in the return type and in the argument types. A trigger function does not take arguments but can access the values inserted or changed in the row variables NEW and OLD. They get automatically defined in plpgsql, see Trigger procedures in the plpgsql chapter for details and examples.

Concerning the error message:

function insertintoautoincrementexample() does not exist

it means: this function name, with an empty list of arguments, does not exist. The presence of parentheses around nothing is relevant, because in postgresql, functions always go with their argument types: foobar(int) is not the same function than foobar(), or foobar(int,int) or foobar(text).

Related Question