PL/pgSQL 函数中的"ERROR: too few arguments for format()"

Question

我创建了一个函数，用于从使用不同时间段的几个表中提取信息:

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF tmpindicadores AS
$BODY$
DECLARE
r tmpindicadores%rowtype;
record tmpindicadores.relname%type;
tmpdata_inicio TIMESTAMP;
tmpdata_fim TIMESTAMP;
dia_inicio INTEGER := 01;
dia_fim INTEGER := 01;
mes INTEGER;
ano INTEGER;
BEGIN 
SELECT EXTRACT (MONTH FROM CURRENT_DATE) INTO mes;enter code here
SELECT EXTRACT (YEAR FROM CURRENT_DATE) INTO ano;
tmpdata_inicio := ano || '-' ||mes ||'-' ||dia_inicio;
tmpdata_fim := ano || '-' ||mes + 1 ||'-' ||dia_fim;
FOR r IN SELECT relname FROM tmpindicadores
LOOP
record = r.relname;
EXECUTE format($$INSERT INTO indicadores1 (SELECT * FROM %I WHERE time_date >= %tmpdata_inicio 
AND time_date <= %tmpdata_fim)$$,record);
RETURN NEXT r;
END LOOP;
COPY indicadores1 TO '/var/app_data/indicadores/arquivos/indicadores1.csv' DELIMITER ';' CSV 
HEADER;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
SELECT * FROM getAllFoo();

但是我得到:

ERROR: too few arguments for format.

这个错误发生在EXECUTE行。当我用'2022-04-01 00:00:00'和'2022-04-05 00:00:00'替换变量tmpdata_inicio和tmpdata_fim时，它可以完美地工作。

我做错了什么?

Answer 1

您使用的format()格式说明符不正确。

可以:

EXECUTE format($$INSERT INTO indicadores1 SELECT * FROM %I WHERE time_date >= %L AND time_date <= %L$$, record, tmpdata_inicio, tmpdata_fim);

而是使用USING子句传递值:

EXECUTE format($$INSERT INTO indicadores1 SELECT * FROM %I WHERE time_date >= $1 AND time_date <= $2$$, record)
USING tmpdata_inicio, tmpdata_fim;

也就是说，我将重写整个函数来净化和优化…