Postgresql Error 42883 When Executing Custom Function

Question

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!

Solution

The argument types don't match.

Your last three arguments in the function call are integers:

4,1,1);

but the function expects the 3rd-from-last argument to be varchar:

_tipoempresa character varying DEFAULT NULL::character varying,
_cod_usuario integer DEFAULT NULL::integer,
_estado_registro integer DEFAULT NULL::integer

Because PostgreSQL supports functions with the same name and different arguments, it can't tell if you meant to call this function with different arguments, or some other function of the same name that's missing from the current database. That's why it reports the error the way it does.

Comments

[alois.wirkes]

It was in fact this error! I change it to integer and it worked! Thank you Craig Ringer! When I did this was almost midnight and I couldn't see this error! xD