调用PostgreSQL存储过程报错：无匹配名称与参数类型

创建名为inserir_funcionario的PostgreSQL存储过程后，执行调用语句时触发错误，提示不存在匹配名称和参数类型的存储过程，需添加显式类型转换。

存储过程代码 #

CREATE OR REPLACE PROCEDURE inserir_funcionario(
    funcionarios_nome VARCHAR(50),
    sexo_nome CHAR(1),
    data_admissao TIMESTAMP,
    matricula VARCHAR(10),
    setores_nome VARCHAR(50),
    cargos_nome VARCHAR(50),
    cargos_salario DECIMAL(10,2),
    in_telefones VARCHAR(30)[][],
    enderecos_cep VARCHAR(9),
    enderecos_estado VARCHAR(50),
    enderecos_cidade VARCHAR(50),
    enderecos_bairro VARCHAR(50),
    enderecos_rua VARCHAR(50),
    enderecos_numero VARCHAR(6),
    enderecos_complemento VARCHAR(50),
    acessos_usuario VARCHAR(20),
    acessos_senha VARCHAR(100)
)

LANGUAGE plpgsql
AS $$
DECLARE
fk_cargos RECORD;
fk_setores RECORD;
fk_funcionarios RECORD;
telef VARCHAR(30)[]; 
BEGIN
    RAISE NOTICE '1';
    EXECUTE "SELECT id_setores FROM setores WHERE nome = 'Produção'" INTO fk_setores;
    EXECUTE "SELECT id_cargos FROM cargos WHERE nome = 'Operador'" INTO fk_cargos;
RAISE NOTICE '2';
    INSERT INTO funcionarios(nome,sexo,data_admissao,matricula,fk_setores,fk_cargos)
    VALUES (funcionarios_nome,setores_nome,current_timestamp,matricula,fk_setores,fk_cargos);

RAISE NOTICE '3';
    EXECUTE "SELECT id_funcionarios FROM funcionarios WHERE matricula = 'matricula'" INTO fk_funcionarios;

RAISE NOTICE '4';
    INSERT INTO acessos(usuario, senha, fk_funcionarios)
    VALUES(acessos_usuario, acessos_senha, id_funcionarios);

RAISE NOTICE '5';
    INSERT INTO enderecos(cep,estado,cidade,bairro,rua,numero,complemento,fk_funcionarios)
    VALUES(enderecos_cep,enderecos_estado,enderecos_cidade,enderecos_bairro,enderecos_rua,enderecos_numero,enderecos_complemento,fk_funcionarios);
RAISE NOTICE '6';

    FOREACH telef SLICE 1 IN ARRAY in_telefones
    LOOP
        INSERT INTO telefones(tipo,numero,fk_funcionarios)
        VALUES(telef[1],telef[2],fk_funcionarios);
    END LOOP;

END;
$$;

调用语句 #

CALL inserir_funcionario('Andre','M',current_timestamp,'A123','Produção','Operador',1000.00,ARRAY[['cel','26516564'],['con','54132165']],'12345678','SP','São Paulo','Vila Mariana','Rua dos Bobos','0','Casa','andre','123456');

错误信息 #

ERROR:  procedure inserir_funcionario(unknown, unknown, timestamp with time zone, unknown, unknown, unknown, numeric, text[], unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist
LINE 1: CALL inserir_funcionario('Andre','M',current_timestamp,'A123...
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.

问题分析 #

从错误信息可以看出，调用时传入的参数类型与存储过程定义的参数类型不匹配，核心问题包括：

1. 数组类型不匹配：存储过程定义in_telefones为VARCHAR(30)[][]，但调用时传入的数组默认是text[][]类型，PostgreSQL无法自动隐式转换二维数组的类型。
2. 部分参数隐式转换失败：如sexo_nome CHAR(1)、cargos_salario DECIMAL(10,2)等参数，传入的字面量被识别为unknown或numeric类型，与定义的类型不完全匹配。
3. 存储过程内部还存在其他语法/逻辑错误（虽不直接导致当前调用错误，但执行时会触发新问题）：
   • EXECUTE语句使用双引号包裹SQL，双引号在PostgreSQL中用于标识符，应改为单引号或美元符包裹SQL字符串。
   • INSERT语句中错误使用setores_nome作为sexo字段的值，应该用sexo_nome。
   • INSERT INTO acessos中使用未定义的id_funcionarios，应改为fk_funcionarios.id_funcionarios。
   • EXECUTE查询中硬编码了'matricula'字符串，无法匹配传入的matricula参数。

修复方案 #

1. 修改调用语句，显式转换参数类型 #

将数组和不匹配的参数显式转换为存储过程定义的类型：

CALL inserir_funcionario(
    'Andre'::VARCHAR(50),
    'M'::CHAR(1),
    current_timestamp::TIMESTAMP,
    'A123'::VARCHAR(10),
    'Produção'::VARCHAR(50),
    'Operador'::VARCHAR(50),
    1000.00::DECIMAL(10,2),
    ARRAY[['cel','26516564'],['con','54132165']]::VARCHAR(30)[][],
    '12345678'::VARCHAR(9),
    'SP'::VARCHAR(50),
    'São Paulo'::VARCHAR(50),
    'Vila Mariana'::VARCHAR(50),
    'Rua dos Bobos'::VARCHAR(50),
    '0'::VARCHAR(6),
    'Casa'::VARCHAR(50),
    'andre'::VARCHAR(20),
    '123456'::VARCHAR(100)
);

2. 修复存储过程内部的语法和逻辑错误 #

CREATE OR REPLACE PROCEDURE inserir_funcionario(
    funcionarios_nome VARCHAR(50),
    sexo_nome CHAR(1),
    data_admissao TIMESTAMP,
    matricula VARCHAR(10),
    setores_nome VARCHAR(50),
    cargos_nome VARCHAR(50),
    cargos_salario DECIMAL(10,2),
    in_telefones VARCHAR(30)[][],
    enderecos_cep VARCHAR(9),
    enderecos_estado VARCHAR(50),
    enderecos_cidade VARCHAR(50),
    enderecos_bairro VARCHAR(50),
    enderecos_rua VARCHAR(50),
    enderecos_numero VARCHAR(6),
    enderecos_complemento VARCHAR(50),
    acessos_usuario VARCHAR(20),
    acessos_senha VARCHAR(100)
)

LANGUAGE plpgsql
AS $$
DECLARE
fk_cargos RECORD;
fk_setores RECORD;
fk_funcionarios RECORD;
telef VARCHAR(30)[]; 
BEGIN
    RAISE NOTICE '1';
    -- 修复EXECUTE的引号问题，改用单引号并使用参数传递
    EXECUTE 'SELECT id_setores FROM setores WHERE nome = $1' INTO fk_setores USING setores_nome;
    EXECUTE 'SELECT id_cargos FROM cargos WHERE nome = $1' INTO fk_cargos USING cargos_nome;
    RAISE NOTICE '2';
    -- 修复sexo字段值错误，使用传入的data_admissao而非current_timestamp
    INSERT INTO funcionarios(nome,sexo,data_admissao,matricula,fk_setores,fk_cargos)
    VALUES (funcionarios_nome,sexo_nome,data_admissao,matricula,fk_setores.id_setores,fk_cargos.id_cargos);

    RAISE NOTICE '3';
    -- 修复matricula硬编码问题，使用参数传递
    EXECUTE 'SELECT id_funcionarios FROM funcionarios WHERE matricula = $1' INTO fk_funcionarios USING matricula;

    RAISE NOTICE '4';
    -- 修复未定义的id_funcionarios，改用具体字段引用
    INSERT INTO acessos(usuario, senha, fk_funcionarios)
    VALUES(acessos_usuario, acessos_senha, fk_funcionarios.id_funcionarios);

    RAISE NOTICE '5';
    -- 修复fk_funcionarios的引用方式
    INSERT INTO enderecos(cep,estado,cidade,bairro,rua,numero,complemento,fk_funcionarios)
    VALUES(enderecos_cep,enderecos_estado,enderecos_cidade,enderecos_bairro,enderecos_rua,enderecos_numero,enderecos_complemento,fk_funcionarios.id_funcionarios);
    RAISE NOTICE '6';

    FOREACH telef SLICE 1 IN ARRAY in_telefones
    LOOP
        -- 修复fk_funcionarios的引用方式
        INSERT INTO telefones(tipo,numero,fk_funcionarios)
        VALUES(telef[1],telef[2],fk_funcionarios.id_funcionarios);
    END LOOP;

END;
$$;

内容的提问来源于stack exchange，提问作者FRSantosDev