CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Elton
Elton

Reputation: 3

"ERROR: too few arguments for format()" in a PL/pgSQL function

I created a function to extract information from several tables using different time period:

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();

But I get:

ERROR: too few arguments for format.

This error occurs in the EXECUTE line. When I replace the variables tmpdata_inicio and tmpdata_fim with '2022-04-01 00:00:00' and '2022-04-05 00:00:00' it works perfectly.

What am I doing wrong?

Upvotes: 0

Views: 704

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

You are using incorrect format specifiers for format().

This would work:

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

But rather, pass values with the USING clause:

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

That said, I would rewrite the whole function to sanitize and optimize ...

Upvotes: 1

Related Questions