执行自定义函数时 Postgresql 错误 42883

Postgresql Error 42883 When Executing Custom Function

提问by alois.wirkes

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

我在没有多少运气的情况下搜索了这个......我希望你能帮助我......

This is my PL/pgSQL function:

这是我的 PL/pgSQL 函数：

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:

它创建得很好，因为我可以在 pgAdminIII 中看到文件夹“Functions”中的函数：

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

我只是不知道哪一种是解决这个问题的正确方法。看起来 PostgreSQL 以某种方式无法定位该函数，即使它已经创建，正如您在图片中看到的那样。或者，它与数据类型有关，但我不明白为什么，因为在函数定义中，数据类型等于函数调用中的参数。此外，我已经有很多与此类似的功能并且一切正常......

Hope you can help me understand this...

希望你能帮我理解这个...

Thanks in advance!

提前致谢！

回答by Craig Ringer

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:

但该函数期望倒数第三个参数是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.

因为 PostgreSQL 支持具有相同名称和不同参数的函数，所以它无法判断您是否打算使用不同的参数调用此函数，或者当前数据库中缺少的某些其他同名函数。这就是它报告错误的原因。