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

Question

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.

• McNets: Please tag your postgres version.
• Supun Amarasinghe: Hi @McNets version is 10.1-1. I cannot add as a tag since it does not exist.
• Edgar Allan Bayron: is this relating to case sensitivity?
• Supun Amarasinghe: @EdgarAllanBayron Thanks for the suggestion. I tried that. No its not related.
• McNets: Shouldn't trigger functions RETURNS TRIGGER? postgresql.org/docs/9.1/static/plpgsql-trigger.html

Answer 1

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).