Auto Table Partitioning -PostgreSQL- ERROR: too few arguments for format()

Question

I'm trying to auto partition my oltpsales table using a function below and a trigger below but then I try to perform in insert on the table I get error code below. I have referenced a few threads below and suggestions are welcomed.

INSERT with dynamic table name in trigger function

Postgres format string using array

ERROR: too few arguments for format() CONTEXT: PL/pgSQL function testoltpsales_insert_function() line 17 at EXECUTE

 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;

score 2 · Accepted Answer · answered Oct 03 '19 at 14:56

2

    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]); ```

There are five format specifiers in your format string but you're passing it only three arguments. Unless you're using positional formatting e.g. %1$I, you must supply the same number of args, as they are used sequentially.

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

answered Oct 03 '19 at 14:56

404

8,022
2
27
47

Thanks for your prompt response, I've had a look at the doc and will need a bit of help understanding the five format specifiers in the execute format command earlier. How do you break this down to 5 specifiers? – ribbit Oct 03 '19 at 16:19
Your string uses 3 `%I` and 2 `%s`, You need to supply an argument for every one of them, even if it's the same one. E.g. `format('%s%s%s%s%s', 'a', 'a', 'a', 'a', 'a')` to make `aaaaa`. You can't supply just one and have it used in every place, UNLESS you use positional formatting (well I'm calling it that anyway), e.g. `format('%1$s%1$s%1$s%1$s%1$s', 'a')` - that tells it to use arg 1 on each of those. – 404 Oct 04 '19 at 07:29
That’s brilliant, thank you for your help as the function now works as expected. One more lesson learnt today! – ribbit Oct 04 '19 at 16:44

Auto Table Partitioning -PostgreSQL- ERROR: too few arguments for format()

1 Answers1