Auto Table Partitioning -PostgreSQL- ERROR:format() 的参数太少

Question

我正在尝试使用下面的函数和下面的触发器自动分区我的 oltpsales 表，但随后我尝试在表上插入执行，我得到下面的错误代码。我在下面引用了一些线程，欢迎提出建议。

在触发器函数中使用动态表名插入

使用数组格式化字符串的 Postgres

错误：format(( 的参数太少 上下文：PL/pgSQL 函数 testoltpsales_insert_function(( 行 17 在执行

CREATE TRIGGER testoltpsales_insert_trg
BEFORE INSERT ON myschema."testoltpsales"
FOR EACH ROW EXECUTE PROCEDURE testoltpsales_insert_function();

CREATE OR REPLACE FUNCTION testoltpsales_insert_function()
RETURNS TRIGGER AS $$
DECLARE
partition_date TEXT;
partition_name TEXT;
start_of_month TEXT;
end_of_next_month TEXT;
BEGIN
partition_date := to_char(NEW."CreateDateTime",'YYYY_MM');
partition_name := 'testoltpsaless_' || partition_date;
start_of_month := to_char((NEW."CreateDateTime"),'YYYY-MM') || '-01';
end_of_next_month := to_char((NEW."CreateDateTime" + interval '1 month'),'YYYY-MM') || '-01';
IF NOT EXISTS
(SELECT 1
FROM   information_schema.tables 
WHERE  table_name = partition_name) 
THEN
EXECUTE format(E'CREATE TABLE %I (CHECK ( date_trunc('day', %I.CreateDateTime) >= ''%s'' AND date_trunc('day', %I.CreateDateTime) < ''%s'')) INHERITS (myschema."Testoltpsaless")', 
VARIADIC ARRAY [partition_name, start_of_month,end_of_next_month]);
RAISE NOTICE 'A partition has been created %', partition_name;
-- EXECUTE format('GRANT SELECT ON TABLE %I TO readonly', partition_name); -- use this if you use role based permission
END IF;
EXECUTE format('INSERT INTO %I ("OwnerId","DaddyTable","SaleId","RunId","CreateDateTime","SalesetId","Result","Score","NumberOfMatches" ) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)', partition_name) 
USING NEW."OwnerId",NEW."DaddyTable",NEW."SaleId",NEW."RunId",NEW."CreateDateTime",NEW."SalesetId",NEW."Result",NEW."Score",NEW."NumberOfMatches";
RETURN NULL;
END
$$
LANGUAGE plpgsql;

Answer 1

EXECUTE format(E'CREATE TABLE %I (CHECK ( date_trunc('day', %I.CreateDateTime) >= ''%s'' AND date_trunc('day',
%I.CreateDateTime) < ''%s'')) INHERITS (myschema."Testoltpsaless")', 
VARIADIC ARRAY [partition_name, start_of_month,end_of_next_month]); ```

格式字符串中有五个格式说明符，但只传递三个参数。除非您使用位置格式，例如%1$I，您必须提供相同数量的参数，因为它们是按顺序使用的。

https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT