42883 ошибка postgresql

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

I searched for this without much luck… I hope you can help me…

This is my PL/pgSQL function:

CREATE OR REPLACE FUNCTION crearempresa(
    _id_empresa integer DEFAULT NULL::integer,
    _ci_rif character varying DEFAULT NULL::character varying,
    _nombre character varying DEFAULT NULL::character varying,
    _persona_contacto character varying DEFAULT NULL::character varying,
    _telefono_movil character varying DEFAULT NULL::character varying,
    _telefono_oficina character varying DEFAULT NULL::character varying,
    _fax character varying DEFAULT NULL::character varying,
    _email character varying DEFAULT NULL::character varying,
    _email_alterno character varying DEFAULT NULL::character varying,
    _direccion character varying DEFAULT NULL::character varying,
    _tipoempresa character varying DEFAULT NULL::character varying,
    _cod_usuario integer DEFAULT NULL::integer,
    _estado_registro integer DEFAULT NULL::integer
)
  RETURNS character varying AS
$BODY$
    DECLARE
        retorno character varying;
    BEGIN
        IF _id_empresa = 0 THEN
            _id_empresa = NULL;
        END IF;
        IF (select id_empresa from empresa where id_empresa = _id_empresa) is null THEN
            IF (Select MAX(id_empresa) from empresa) is null THEN
                _id_empresa = 1;
            ELSE
                _id_empresa = (Select MAX(id_empresa) + 1 from empresa);
            END IF;
            insert into empresa (
                id_empresa,ci_rif,nombre,persona_contacto,telefono_movil,telefono_oficina,fax,email,
                email_alterno,direccion,id_tipo_empresa,cod_usuario,fecha_creacion,fecha_actualizacion,estado_registro)
            values (
                _id_empresa,_ci_rif,_nombre,_persona_contacto,_telefono_movil,_telefono_oficina,_fax,_email,            
                _email_alterno,_direccion,_tipoempresa,_cod_usuario,CURRENT_DATE,CURRENT_DATE,_estado_registro);
            retorno = '1';      
        ELSE
            Update empresa
               set ci_rif = _ci_rif,            
                   nombre = _nombre,            
                   persona_contacto = _persona_contacto,            
                   telefono_movil = _telefono_movil,        
                   telefono_oficina = _telefono_oficina,            
                   fax = _fax,          
                   email = _email,          
                   email_alterno = _email_alterno,          
                   direccion = _direccion,      
                   id_tipo_empresa = _tipoempresa,  
                   cod_usuario = _cod_usuario,          
                   fecha_actualizacion = CURRENT_DATE,          
                   estado_registro = _estado_registro
             where id_empresa = _id_empresa;
            retorno = '2';
        END IF;                   
        RETURN retorno;
    END;
 $BODY$
 LANGUAGE plpgsql VOLATILE COST 100;

It was created fine, because I can see the function inside the folder ‘Functions’ in pgAdminIII:

I when I try to test the function using:

select crearempresa (1,'a','f','a','b','c','d','e','f','g',4,1,1);

I get the following error message:

ERROR:  no existe la función crearempresa(integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer, integer, integer)
LINE 1: select crearempresa (1,'a','f','a','b','c','d','e','f','g',4...
               ^
HINT:  Ninguna función coincide en el nombre y tipos de argumentos. Puede ser necesario agregar conversión explícita de tipos.
********** Error **********

ERROR: no existe la función crearempresa(integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer, integer, integer)
SQL state: 42883
Hint: Ninguna función coincide en el nombre y tipos de argumentos. Puede ser necesario agregar conversión explícita de tipos.
Character: 8

I just don’t know which one is the correct aproach to solve this issue. It looks like somehow PostgreSQL can’t locate the function even though it’s already created, as you can see in the picture. Or maybe, it has something to do with datatypes, but I just can’t understand why, because in the function definition the datatypes are equal to the parameters in the function call. Besides, I already have many functions similar to this and all worked fine…

Hope you can help me understand this…

Thanks in advance!

I’ve done some more digging. The issue isn’t with the parameter type, but with the fact that a name is specified. In PostgreSQL you can call a function with positional (SELECT * FROM foo(8) or named (SELECT * FROM foo(bar => 8)) argument style. Unfortunately it seems that like many other internal functions, pg_try_advisory_lock does not actually have parameter names, so you can only call it positionally. To do this, do not set the NpgsqlParameter’s name («key») — just leave it empty. This will make PostgreSQL generate the positional style, which should work.

For reference, the following reproduces your issue:

using var cmd = new NpgsqlCommand("pg_try_advisory_lock", conn);
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("key", NpgsqlDbType.Bigint, 8); // Note "key" parameter name
    cmd.ExecuteNonQuery();
}

While the following works:

using var cmd = new NpgsqlCommand("pg_try_advisory_lock", conn);
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue(NpgsqlDbType.Bigint, 8); // Note no parameter name
    cmd.ExecuteNonQuery();
}

The query that shows method along with their parameter names and types:

SELECT
    proname,
    CASE
        WHEN pg_proc.proargnames IS NULL THEN array_cat(array_fill(''::name,ARRAY[pg_proc.pronargs]),array_agg(pg_attribute.attname ORDER BY pg_attribute.attnum))
        ELSE pg_proc.proargnames
        END AS proargnames,
    pg_proc.proargtypes,
    CASE
        WHEN pg_proc.proallargtypes IS NULL AND (array_agg(pg_attribute.atttypid))[1] IS NOT NULL THEN array_cat(string_to_array(pg_proc.proargtypes::text,' ')::oid[],array_agg(pg_attribute.atttypid ORDER BY pg_attribute.attnum))
        ELSE pg_proc.proallargtypes
        END AS proallargtypes,
    CASE
        WHEN pg_proc.proargmodes IS NULL AND (array_agg(pg_attribute.atttypid))[1] IS NOT NULL THEN array_cat(array_fill('i'::"char",ARRAY[pg_proc.pronargs]),array_fill('o'::"char",ARRAY[array_length(array_agg(pg_attribute.atttypid), 1)]))
        ELSE pg_proc.proargmodes
        END AS proargmodes
FROM pg_proc
         LEFT JOIN pg_type ON pg_proc.prorettype = pg_type.oid
         LEFT JOIN pg_attribute ON pg_type.typrelid = pg_attribute.attrelid AND pg_attribute.attnum >= 1 AND NOT pg_attribute.attisdropped
GROUP BY proname, pg_proc.proargnames, pg_proc.proargtypes, pg_proc.proallargtypes, pg_proc.proargmodes, pg_proc.pronargs;

For proname=’pg_try_advisory_lock’, the proargnames column doesn’t contain names like for other functions.

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.